Database COLD backup & Restore from TAPE

Database COLD backup & Restore from TAPE

Here I want to duplicate the PROD database to QA.
Here my PROD db is not a live Prod database instead it is a reporting database.

There are some limitations and restrictions like REPORTING DB is in no archivelog mode and no disk space to take the backup and has given permission to take down the DB for a cold backup to TAPE.


DB Backup on the source host:

PROD_HOST:/
#!/bin/ksh
export ORACLE_HOME=/fin04/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=PRODDB
export TODAY=`date '+%Y%m%d'`
export TIME=`date '+%H%M%S'`
$ORACLE_HOME/bin/rman msglog Backup_PRODDB_COLD.${TODAY}_${TIME}.log << EOF
connect target /
run {
shutdown immediate;
startup mount;
allocate channel ch01 type sbt_tape;
allocate channel ch02 type sbt_tape;
allocate channel ch03 type sbt_tape;
allocate channel ch04 type sbt_tape;
allocate channel ch05 type sbt_tape;
allocate channel ch06 type sbt_tape;
allocate channel ch07 type sbt_tape;
allocate channel ch08 type sbt_tape;
allocate channel ch09 type sbt_tape;
allocate channel ch10 type sbt_tape;
send 'NB_ORA_POLICY=PRODDB_ORA_CDC_PROD,NB_ORA_CLIENT=PROD_HOST,NB_ORA_SCHED=full';
backup
INCREMENTAL LEVEL=0
database ;
backup current controlfile;
sql 'alter database open';
release channel ch01;
release channel ch02;
release channel ch03;
release channel ch04;
release channel ch05;
release channel ch06;
release channel ch07;
release channel ch08;
release channel ch09;
release channel ch10;
}
list incarnation of database;
exit
EOF


:- Backup completed:

RMAN>
piece handle=6pn19kt1_1_1 tag=TAG20120121T024033 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch01: backup set complete, elapsed time: 00:00:58
Finished backup at 21-JAN-12

Starting Control File and SPFILE Autobackup at 21-JAN-12
piece handle=c-3861913925-20120121-00 comment=API Version 2.0,MMS Version 5.0.0.0
Finished Control File and SPFILE Autobackup at 21-JAN-12


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 PRODDB 3861913925 CURRENT 6574375077 29-OCT-04






DB Restore on the destination/new host:-
==========================================

unixhost(PRODDB) /export/home/oracle
>rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jan 21 17:15:54 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: PRODDB (not mounted)

RMAN> set dbid=3861913925 ;

executing command: SET DBID

RMAN> run{
2> allocate channel ch01 type sbt_tape;
3> send 'NB_ORA_POLICY=PRODDB_ORA_CDC_PROD,NB_ORA_CLIENT=PROD_HOST-bn';
4> restore controlfile from autobackup; <---- We have to use autobackup for TAPE
5> }

allocated channel: ch01
channel ch01: sid=544 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2010042405)

sent command to channel: ch01

Starting restore at 21-JAN-12

channel ch01: looking for autobackup on day: 20120121

channel ch01: autobackup found: c-3861913925-20120121-00
channel ch01: control file restore from autobackup complete
output filename=+QADB_REDO_01/PRODDB/controlfile/current.264.773170015
output filename=+QADB_REDO_02/PRODDB/controlfile/current.266.773170019
Finished restore at 21-JAN-12
released channel: ch01

RMAN>
RMAN>

=====mount the database with the restore controlfile=======

RMAN> sql 'alter database mount';

using target database control file instead of recovery catalog
sql statement: alter database mount

===Start the restore:

#!/bin/ksh
export ORACLE_HOME=/fin03/u0001/oracle/product/10.2.0/db_1
export ORACLE_SID=MYDB
NLS_DATE_FORMAT="DD-MON-RRRR HH24:MI:SS"
export NLS_DATE_FORMAT
/fin03/u0001/oracle/product/10.2.0/db_1/bin/rman debug trace db_restore_trace1.log msglog db_restore.log append< { run
allocate channel ch01 type disk;
crosscheck backup;
delete noprompt expired backup;
}

Now:
===

Started the restore again with trace on just in case:-
========================================================


RMAN-08511: piece handle=6on195dc_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch09: restore complete, elapsed time: 06:19:06
RMAN-08023: channel ch05: restored backup piece 1
RMAN-08511: piece handle=6kn195da_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch05: restore complete, elapsed time: 06:28:23
RMAN-08023: channel ch04: restored backup piece 1
RMAN-08511: piece handle=6ln195db_1_1 tag=TAG20120120T221549
RMAN-08180: channel ch04: restore complete, elapsed time: 06:32:18
RMAN-03091: Finished restore at 23-JAN-2012 02:05:25

RMAN-08031: released channel: ch01
RMAN-08031: released channel: ch03


After the crosscheck and cleanup. RMAN restored 2TB database in less than 7hrs.

In my Restore script I forgot to include "switch datafile all" at the end.
Since we are using "set newname for datafile 1 to NEW" I should have added "switch datafile all"

After restore:-
==================================

RMAN> run {
2> allocate channel ch01 type disk;
3> switch datafile all;
4> }


This didn't work since the channels used for restore has been released and my restore session has been ended.



But after the restore we can do this by going to RMAN>

While database in mount state and all the files has been restored:

RMAN>
RMAN> catalog start with '+QADB_DATA_01/QADB/datafile';
switch database to copy;

RMAN>

This will only work is ALL the datafiles are restored, otherwise the SWITCH DATABASE TO COPY will fail.


Before you open the database:
disable any change tracking


SQL>ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;


Now:-

SQL>ALTER DATABASE OPEN;

Since this is arestore from COLD backup there is no media recovery and hence no "resetlogs option"






At this point database is UP and running:



Stage-2:-

Now I want to rename this Database to something else. I am using NID to change the DBNAME and DBID of the restored database.


unixhost(PRODDB) /export/home/oracle
>tnsping PRODDB1

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 24-JAN-2012 07:55:08

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:
/fin03/u0001/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = unixhost-vip)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PRODDB.TWCABLE.COM) (INSTANCE_NAME = PRODDB1)))
OK (10 msec)




unixhost(PRODDB) /export/home/oracle
>nid TARGET=SYS/s09s20y7@PRODDB1 DBNAME=QADB

DBNEWID: Release 10.2.0.4.0 - Production on Tue Jan 24 07:56:39 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to database PRODDB (DBID=3861913925)

Connected to server version 10.2.0

Control Files in database:
+QADB_REDO_01/PRODDB/controlfile/current.264.773170015
+QADB_REDO_02/PRODDB/controlfile/current.266.773170019

Change database ID and database name PRODDB to QADB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 3861913925 to 340462486
Changing database name from PRODDB to QADB
Control File +QADB_REDO_01/PRODDB/controlfile/current.264.773170015 - modified
Control File +QADB_REDO_02/PRODDB/controlfile/current.266.773170019 - modified
Datafile +QADB_DATA_01/PRODDB/datafile/system.416.773331423 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/ps_cm_acctg_line_idx1.417.773315313 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/aart_data_lg.373.773319401 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amlarge.363.773335877 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amapp.496.773334279 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/amlarge.340.773315185 - dbid changed, wrote new name

Datafile +QADB_DATA_01/PRODDB/datafile/pcapp.508.773335933 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/pclarge.401.773329153 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/perfstat.468.773331615 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/poapp.456.773315299 - dbid changed, wrote new name
Datafile +QADB_DATA_01/PRODDB/datafile/polarge.267.773327515 - dbid changed, wrote new name

Control File +QADB_REDO_01/PRODDB/controlfile/current.264.773170015 - dbid changed, wrote new name
Control File +QADB_REDO_02/PRODDB/controlfile/current.266.773170019 - dbid changed, wrote new name
Instance shut down

Database name changed to QADB.
Modify parameter file and generate a new password file before restarting.
Database ID for database QADB changed to 340462486.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.


***
Change the DBNAME and other log/dump directories that has the old name from the parameter file.


Here I have the parameter file already setup for the clone so I am just changing the control_file location in my clone parameter file.