Restoring Database to Different ASM Disk Group Layout

Create a cold backup of source database, when database is in mount stage

run {
allocate channel oem_backup_disk1 type disk format ‘/%U’;
allocate channel oem_backup_disk2 type disk format ‘/%U’;
allocate channel oem_backup_disk3 type disk format ‘/%U’;
allocate channel oem_backup_disk4 type disk format ‘/%U’;
backup as COMPRESSED BACKUPSET tag ‘FULL_COLD_BACKUP’ database include current controlfile;
release channel oem_backup_disk1;
release channel oem_backup_disk2;
release channel oem_backup_disk3;
release channel oem_backup_disk4;
}

Create a pfile for target database

*.core_dump_dest=’/u01/app/oracle/admin/TESTDB/cdump’
*.user_dump_dest=’/u01/app/oracle/admin/TESTDB/udump’
*.audit_file_dest=’/u01/app/oracle/admin/TESTDB/adump’
*.background_dump_dest=’/u01/app/oracle/admin/TESTDB/bdump’
*.compatible=’10.2.0.3.0′
*.db_block_size=8192
*.db_create_file_dest=’+DATA_DG’
*.db_file_multiblock_read_count=16
*.db_name=’TESTDB’
*.job_queue_processes=10
*.open_cursors=300
*.processes=400
*.remote_login_passwordfile=’exclusive’
*.sessions=445
*.sga_max_size=6442450944
*.sga_target=5133828096
*.undo_management=’AUTO’

On target server

sqlplus / as sysdba

SQL> create spfile from pfile=’/u01/app/oracle/product/10.2.0/dbs/initTESTDB.ora’;

echo “SPFILE=’+DATA_DG/TESTDB/spfileTESTDB.ora'” > initTESTDB.ora

SQL> startup nomount

ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size 2039744 bytes
Variable Size 2231369792 bytes
Database Buffers 4194304000 bytes
Redo Buffers 14737408 bytes

Duplicate database using RMAN

rman target sys/password@ auxiliary / log=rman.log

RUN
{
ALLOCATE AUXILIARY CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL C2 DEVICE TYPE DISK;
SET UNTIL SCN ;
DUPLICATE TARGET DATABASE TO TESTDB;
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
}

Here target is the source database (i.e. original database). Auxiliary is the database to create.

If target database is RAC then, add the public thread

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 (‘+DATA_DG’,’+DATA_DG’) SIZE 50M, GROUP 5 (‘+DATA_DG’,’+DATA_DG’) SIZE 50M;

SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;
SQL> ALTER DATABASE ENABLE PUBLIC THREAD 3;

Add the entries in $ORACLE_HOME/network/admin/tnsnames.ora

TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)

LISTENERS_TESTDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))
)

TESTDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
(INSTANCE_NAME = TESTDB1)
)
)

TESTDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
(INSTANCE_NAME = TESTDB2)
)
)

TESTDB3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node3-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
(INSTANCE_NAME = TESTDB3)
)
)

copy the tnsnames.ora to all nodes

$ scp tnsnames.ora oracle@node2:/u01/app/oracle/product/10.2.0/network/admin
$ scp tnsnames.ora oracle@node3:/u01/app/oracle/product/10.2.0/network/admin

Once the database is created, add entry in /var/opt/oracle/oratab

TESTDB:/u01/app/oracle/product/10.2.0:N

To add the database to cluster stack

$ srvctl add database -d TESTDB -o /u01/app/oracle/product/10.2.0
$ srvctl add instance -d TESTDB -i TESTDB1 -n node1
$ srvctl add instance -d TESTDB -i TESTDB2 -n node2
$ srvctl add instance -d TESTDB -i TESTDB3 -n node3

Advertisements
This entry was posted in Oracle Automatic Storage Management, Oracle Database Backup, Oracle Recovery Manager and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s