Steps for Migrating database from Normal file system to ASM

Steps Migration of database from Normal filesystem to ASM:
Set the environment variable to proper database : export ORACLE_SID=testdb;
Run the /home/oracle/db_data_files.sql to get the list of files used by database which are on NAS filesystems and save the output
Make sure enable archivelog of the database
SQL> archive log list;
SQL>shutdown immediate;
Edit the init.ora parameter with the following
*.log_archive_dest_1='+FRA_01'
*.log_archive_format='testdb_%t_%r_%s.arc' (make sure change to corresponding db name)
SQL> startup mount
SQL> alter database archivelog;
Edit the init.ora parameter with the following
*.db_create_file_dest = '+DATA_01'
*.db_recovery_file_dest = '+FRA_01'
*.db_recovery_file_dest_size = 8G
*.control_files = '+DATA_01', '+FRA_01'
( make sure backup old parameter file and comment old controlfile parameter)

Shudown the database

SQL> Shutdown immediate

Startup database with nomount
SQL> STARTUP NOMOUNT

1. From an RMAN session, copy one of your controlfiles from the local file system to its new location in ASM. The new controlfile will be copied to the value specified in the initialization parameter control_files:
RMAN > connect target /
RMAN> restore controlfile from '/data/8ps/test/data/testdb/control_01.dbf';
2. From an RMAN or SQL*Plus session, mount the database. This will mount the database using the controlfile stored in ASM:
RMAN> ALTER DATABASE MOUNT;
3. From an RMAN session, copy the database files from the local file system to ASM:
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA1_01';

NOTE: Since this step takes longtime run the following script in nohup mode

cat /home/oracle/copy_to_asm.ksh

#!/bin/ksh
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

rman << EOF
connect target /
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
backup as copy database format '+DATA_01';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
}

exit;
EOF

nohup /home/oracle/copy_to_asm.ksh &

4. From an RMAN session, update the control file / data dictionary so that all database files point to the RMAN copy made in ASM:
rman target /
Rman > SWITCH DATABASE TO COPY;


5. From a SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

NOTE: here you may not have anything to recover if changes are not many

SQL> ALTER DATABASE OPEN RESETLOGS;

6. From a SQL*Plus session, re-create any tempfiles that are still currently on the local file system to ASM. This is done by simply dropping the tempfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameter db_create_file_dest=+DATA_01:
SQL> select tablespace_name, file_name, bytes from dba_temp_files;

SQL> alter database tempfile '/data/8ps/test/data/testdb/ts_temp_01.dbf' drop including datafiles;
SQL> alter tablespace temp add tempfile size 512M;



7. From a SQL*Plus session, re-create any online redo logfiles that are still currently on the local file system to ASM. This is done by simply dropping the logfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameters db_create_file_dest=+DATA_01 and db_recovery_file_dest=+FRA_01:

a. Determine the current online redo logfiles to move to ASM by examining the file names (and sizes) from V$LOGFILE:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;
b. Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:
SQL> select group#, status from v$log;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> select group#, status from v$log;

c. After making the last online redo log file the CURRENT one, drop the first online redo log:
SQL> alter database drop logfile group 1;

d. Re-create the dropped redo log group in ASM (and a different size if desired):
SQL> alter database add logfile group 1 size 250m;

e. After re-creating the first online redo log group, loop back to drop / re-create the next online redo logfile until all logs are rebuilt in ASM.
Verify all online redo logfiles have been created in ASM:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;

8. Verify all the database files have been migrated to ASM

SQL> @/home/oracle/db_data_files_all.sql




9. Shutdown the database and edit the init.ora file to change controlfiles parameter to add both files

SQL> shutdown immediate

*.control_files = '+DATA_01/testdb/controlfile/current.257.754062863','+FRA_01/TESTDB/CONTROLFILE/current.259.754067365'




10. Startup the database

SQL> startup

what is channel and its functions and its characteristics in RMAN?

What is a channel?

An RMAN channel represents one stream of data to a device type, and corresponds to one server session. Most backup and recovery commands in RMAN are executed by server sessions. As illustrated above figure.Each channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance. The server session performs the backup, restore, and recovery.



There are two ways of Channel allocation.

i)Automatic Channel allocation
ii)Manual Channel allocation.

i)Automatic channel Allocation:
This can be allocated through following command.
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK RATE 1900K;

ii)Manual Channel Allocation:
This can be allocated through following command.
In the Run block.. Allocated the channel..
Allocate channel c1 device type disk format '/oracle/g01/backup/';
Allocate channel for maintenance;

You can override automatic channel allocation settings by manually allocating channels within a RUN block. Manual channels always override automatic channels. For example, you override automatic channel allocation when you issue a command as follows:

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE PLUS ARCHIVELOG;
}


You can change a parallelism setting by issuing another CONFIGURE DEVICE TYPE ... PARALLELISM command. This example configures PARALLELISM 2 and then changes it to 3:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;


You can view the default setting for parallelism by running the SHOW DEVICE TYPE command. For example:

RMAN> SHOW DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #default

how to place backup dumps to different mount points(disks) through Rman.

Hi All,

This Article will be useful if you have space issue in single disk(mount point).

How to configure RMAN to backup to different disks(mount points).


You need to configure two channels for two mountpoints


Below are the steps.


step i)RMAN> configure channel 1 device type disk format '/d01/stage1/%U.bak';

step ii)RMAN>configure channel 2 device type disk format '/d01/stage2/%U.bak';

step iii)RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

step iv)RMAN> configure default device type to disk;

step v)RMAN> backup database;

Then you can verify the backup dumps. It will placed in both stage1 and stage2 mountpoints

ls -l /d01/stage?

or you can write a programme and run the below script.

run { allocate channel c1 type disk maxpiecesize=60m format =
'/d01/stage1/%U.bak';
allocate channel c2 type disk maxpiecesize=60m format =
'/d01/stage2/%U.bak';
backup tablespace example;
}




I hope this helps.. :)

HOW TO CREATE WALLET FOR TDE(ASO)

STEP 1:CREATE THE WALLET DIRECTORY:
===================================
mkdir -p /oracle/dbtools/TDE/10.2.0/wallet

STEP 2:CHANGE THE WALLET PERMISSIONS:
====================================
chmod 0700 wallet

STEP 3:EDIT THE SQLNET.ORA FILE:
===============================
SQLNET.EXPIRE_TIME =5

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /oracle/dbtools/TDE/10.2.0/wallet/)))

STEP 4:STOP & START THE LISTENER:
=========================
LSNRCTL STOP LISTENERNAME & LSNRCTL START LISTENER NAME

STEP 5:CREATE THE ENCRYPTION KEY:
=================================
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "********";

STEP 6:CONFIRM WHETHER ENCRYPTION KEY IS CREATED IN WALLET DIRECTORY:
====================================================================

STEP 7:OPEN THE WALLET IF ITS NOT OPEN:

alter system set wallet open identified by "********";