ROAD-2-ORACLE : RMAN backup scenario, How to Create New database using RMAN

This Documents also provide by Mr. Uzzal Basak :-)

How to Create New database using RMAN backup scenario:

I have two database servers, 1 is my production db server and other one is backup testing db server. in backup testing server i have installed only oracle binaries.(server) haven't configured any databases using dbca. ;)
connect to rman of production db and take the backup of controlfile/datafile/archivelogs.

bash-3.2$ mkdir rman_backup

bash-3.2$ cd rman_backup/

bash-3.2$ pwd

/export/home/oracle/rman_backup

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2211608 bytes

Variable Size 314573032 bytes

Database Buffers 96468992 bytes

Redo Buffers 4292608 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 3

Current log sequence 3

SQL> show parameter db_reco

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /export/home/oracle/flash_reco

very_area

db_recovery_file_dest_size big integer 3882M

SQL> alter system set db_recovery_file_dest='/export/home/oracle/rman_backup' sc ope=both;

System altered.

SQL> alter system set db_recovery_file_dest_size=30G scope=both;

System altered.

SQL> show parameter db_reco

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /export/home/oracle/rman_backu

db_recovery_file_dest_size big integer 30G

SQL>

oracle$ rman target /

take control file backup
rman> configure controlfile autobackup on;
now check it
rman > show all
rman > backup as copy current controlfile

RMAN> connect target/

connected to target database: ORCL (DBID=1381674113)

RMAN> backup as copy current controlfile;

Starting backup at 29-JUL-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/export/home/oracle/rman_backup/ORCL/controlfile/o1_mf_TAG20140729T235520_9xhr09cs_.ctl tag=TAG20140729T235520 RECID=1 STAMP=854236523

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 29-JUL-14

Starting Control File and SPFILE Autobackup at 29-JUL-14

piece handle=/export/home/oracle/rman_backup/ORCL/autobackup/2014_07_29/o1_mf_s_854236524_9xhr0gkv_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 29-JUL-14

now backup database full
rman> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup
format '/export/home/oracle/rman_backup/%U.bkp'
database plus archivelog;
release channel d1;
release channel d2;
}

--------------------------------------------------------------------
now get the paths of backup files taken

oracle$ sqlplus / as sysdba

sql>select name from v$datafile;

sql>select member from v$logfile;

sql>select name from v$controlfile;

sql>show parameter dump_dest

copy them to a textpad bcz we want to create same directories in
other server.

----------------------------------------------------------------------

now connect to the backup testing server

create directories for files like backupset and dumpset

oracle$ cd $oracle_home
oracle$ mkdir oradata
oracle$ cd oradata
oracle$ mkdir orcl

--------------------------------------------------------------------
now get the paths of backup files taken
oracle$ sqlplus / as sysdba
sql>select name from v$datafile;
sql>select member from v$logfile;
sql>select name from v$controlfile;
sql>show parameter dump_dest
copy them to a textpad bcz we want to create same directories in
other server.
----------------------------------------------------------------------
now connect to the backup testing server
create directories for files like backupset and dumpset

oracle$ cd $oracle_home
oracle$ mkdir oradata
oracle$ cd oradata
oracle$ mkdir orcl

oracle$ /export/home/oracle/admin/orcl
oracle$ mkdir orcl
oracle$ cd orcl
oracle$ mkdir adump bdump cdump udump
oracle$ mkdir rman_backup
----------------------------------------------
now connect to the prod db and copy all the backup files to
relevant location.
archivelogs,controlfile,datafile to rman_backup using scp command

----------------------------

now connect to backup server

first step is to restore spfile

oracle$ export oracle_sid=orcl

----------------------------
now connect to backup server
first step is to restore spfile
oracle$ export oracle_sid=orcl
oracle$ rman target /
start the database in nomount mode using dumy parameter file
rman> startup nomount
RMAN> restore controlfile from '/export/home/oracle/rman_backup/ORCL/autobackup/2014_07_29/o1_mf_s_854236524_9xhr0gkv_.bkp';
RMAN> restore controlfile from '/export/home/oracle/rman_backup/ORCL/autobackup/2014_07_29/o1_mf_s_854236524_9xhr0gkv_.bkp';

now login to rman and restore the database
rman target /
restore database
rman> restore database;
if this fails catalog datafiles
rman> catalog start with '/u01/app/oracle/flash_recovery_area/backupset/2011-02-02';
rman> restore database;
recover database using logfiles
rman> recover database
it fails, so issue below

RMAN> restore database;

Starting restore at 30-JUL-14

Starting implicit crosscheck backup at 30-JUL-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

Finished implicit crosscheck backup at 30-JUL-14

Starting implicit crosscheck copy at 30-JUL-14

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 30-JUL-14

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_29/o1_mf_1_5_9xhr55wh_.arc

File Name: /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_6_9xhrc45v_.arc

File Name: /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_7_9xhrwklp_.arc

File Name: /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_8_9xhs070w_.arc

File Name: /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_9_9xhs5wf5_.arc

File Name: /export/home/oracle/rman_backup/ORCL/autobackup/2014_07_29/o1_mf_s_854236524_9xhr0gkv_.bkp

File Name: /export/home/oracle/rman_backup/ORCL/autobackup/2014_07_30/o1_mf_s_854236870_9xhrc7n8_.bkp

File Name: /export/home/oracle/rman_backup/ORCL/autobackup/2014_07_30/o1_mf_s_854237726_9xhs5zkj_.bkp

File Name: /export/home/oracle/rman_backup/ORCL/backupset/2014_07_29/o1_mf_annnn_TAG20140729T235758_9xhr56y4_.bkp

File Name: /export/home/oracle/rman_backup/ORCL/backupset/2014_07_29/o1_mf_nnndf_TAG20140729T235801_9xhr5c3p_.bkp

File Name: /export/home/oracle/rman_backup/ORCL/backupset/2014_07_30/o1_mf_annnn_TAG20140730T000108_9xhrc52p_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /export/home/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /export/home/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /export/home/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /export/home/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /export/home/oracle/oradata/orcl/DEVB_mds.dbf

channel ORA_DISK_1: restoring datafile 00006 to /export/home/oracle/oradata/orcl/DEVB_orabam.dbf

channel ORA_DISK_1: restoring datafile 00007 to /export/home/oracle/oradata/orcl/DEVB_soainfra.dbf

channel ORA_DISK_1: restoring datafile 00008 to /export/home/oracle/oradata/orcl/DEVB_iassdpm.dbf

channel ORA_DISK_1: reading from backup piece /export/home/oracle/rman_backup/ORCL/backupset/2014_07_29/o1_mf_nnndf_TAG20140729T235801_9xhr5c3p_.bkp

channel ORA_DISK_1: piece handle=/export/home/oracle/rman_backup/ORCL/backupset/2014_07_29/o1_mf_nnndf_TAG20140729T235801_9xhr5c3p_.bkp tag=TAG20140729T235801

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:50

Finished restore at 30-JUL-14

RMAN> recover database;

Starting recover at 30-JUL-14

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_29/o1_mf_1_5_9xhr55wh_.arc

archived log for thread 1 with sequence 6 is already on disk as file /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_6_9xhrc45v_.arc

archived log for thread 1 with sequence 7 is already on disk as file /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_7_9xhrwklp_.arc

archived log for thread 1 with sequence 8 is already on disk as file /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_8_9xhs070w_.arc

archived log for thread 1 with sequence 9 is already on disk as file /export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_9_9xhs5wf5_.arc

archived log file name=/export/home/oracle/rman_backup/ORCL/archivelog/2014_07_29/o1_mf_1_5_9xhr55wh_.arc thread=1 sequence=5

archived log file name=/export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_6_9xhrc45v_.arc thread=1 sequence=6

archived log file name=/export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_7_9xhrwklp_.arc thread=1 sequence=7

archived log file name=/export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_8_9xhs070w_.arc thread=1 sequence=8

archived log file name=/export/home/oracle/rman_backup/ORCL/archivelog/2014_07_30/o1_mf_1_9_9xhs5wf5_.arc thread=1 sequence=9

unable to find archived log

archived log thread=1 sequence=10

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/30/2014 03:56:39

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1063704

Issue above same command if you can't open the database

now create a spfile from pfile
oracle$sqlplus / as sysdba
sql>create spfile from pfile='/u01/myinit.ora';
sql> now down the db serve and start it again...
sql> check table whatever u want ;)

Convert production DB in archivelog and few parameter changes.

output below box:

dump destination

copy spfile from production to testing server

SQL> startup nomount

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2211608 bytes

Variable Size 314573032 bytes

Database Buffers 96468992 bytes

Redo Buffers 4292608 bytes

SQL> !

$ bash

bash-3.2$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jul 30 03:40:44 2014

RMAN> connect target/

connected to target database: ORCL (not mounted)

Starting restore at 30-JUL-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/30/2014 03:46:42

ORA-19504: failed to create file "/export/home/oracle/flash_recovery_area/orcl/control02.ctl"

ORA-27040: file create error, unable to create file

Solaris-AMD64 Error: 2: No such file or directory

ORA-19600: input file is control file (/export/home/oracle/oradata/orcl/control01.ctl)

ORA-19601: output file is control file (/export/home/oracle/flash_recovery_area/orcl/control02.ctl)

Create this /export/home/oracle/flash_recovery_area/orcl/

Then try

Starting restore at 30-JUL-14

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/export/home/oracle/oradata/orcl/control01.ctl

output file name=/export/home/oracle/flash_recovery_area/orcl/control02.ctl

Finished restore at 30-JUL-14

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 417546240 bytes

Fixed Size 2211608 bytes

Variable Size 314573032 bytes

Database Buffers 96468992 bytes

Redo Buffers 4292608 bytes

SQL> create pfile from spfile;

File created.

SQL> alter database mount;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

ROAD-2-ORACLE

Wednesday 13 August 2014

RMAN backup scenario, How to Create New database using RMAN

No comments: