Hi,
If we have the rman cold backup we can recover the Database upto that point we have the backup.Below is our experiment to prove this point.
Recovery of Database from complete loss of all files:
---------------------------------------------------------
Our Database is in noarchive log mode as verified below:
SQL> ARCHIVE LOG LIST
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination D:\app\RafiAlvi\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence 1
Current log sequence 2
k
I'm having rman cold backup of all database files and image copy of controlfile.
Below are our scripts which I used for taking cold backup:
run_ORCL.txt:
-----------------
run{
shutdown immediate;
startup mount;
allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
backup database TAG='ORCL_BACKUP_WEEKLY';
allocate channel c1 type disk;
copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
Release channel c1;
configure retention policy to recovery window of 7 days;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
CROSSCHECK BACKUP;
release channel ch1;
release channel ch2;
alter database open;
}
rman_coldb_backup.bat:
------------------------
-- orcl DB RMAN COLD BACKUP
set ORACLE_SID=orcl
set ORACLE_HOME=D:\app\RafiAlvi\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\app\RafiAlvi\
rman target sys/orcldba @D:\RMAN_BACKUP_SCRIPTS\run_ORCL.txt log=D:\RMAN_BACKUP_SCRIPTS\LOGS\rman_cold_orcl_25july_cold.log
rman_cold_orcl_25july_cold.log:
--------------------------------
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 25 09:04:41 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1280115002, not open)
RMAN> run{
2> shutdown immediate;
3> startup mount;
4> allocate channel ch1 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET1_%U_%T' maxpiecesize 5G;
5> allocate channel ch2 device type disk format 'D:\RMAN_BACKUP\orcl_BK_SET2_%U_%T';
6> backup database TAG='ORCL_BACKUP_WEEKLY';
7> allocate channel c1 type disk;
8> copy current controlfile to 'D:\RMAN_BACKUP\orcl_ctrl_%U_%T';
9> Release channel c1;
10> configure retention policy to recovery window of 7 days;
11> CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
12> CROSSCHECK BACKUP;
13> release channel ch1;
14> release channel ch2;
15> alter database open;
16> }
17>
using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
allocated channel: ch1
channel ch1: SID=134 device type=DISK
allocated channel: ch2
channel ch2: SID=5 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ch1: starting piece 1 at 25-JUL-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ch2: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:11
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 25-JUL-11
channel ch1: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:08
channel ch2: finished piece 1 at 25-JUL-11
piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 tag=ORCL_BACKUP_WEEKLY comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:21
Finished backup at 25-JUL-11
allocated channel: c1
channel c1: SID=48 device type=DISK
Starting backup at 25-JUL-11
channel ch1: starting datafile copy
copying current control file
output file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_24MIAF8O_20110725 tag=TAG20110725T090632 RECID=32 STAMP=757415193
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUL-11
released channel: c1
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\SNAPCF_ORCL.F';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_BACKUP\snapcf_orcl.f';
new RMAN configuration parameters are successfully stored
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_04\O1_MF_NCSNF_TAG20110604T133433_6YO5PVD8_.BKP RECID=1 STAMP=752938475
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNNDF_TAG20110621T011243_700NMVS9_.BKP RECID=8 STAMP=754362763
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSNF_TAG20110621T011243_700NO02P_.BKP RECID=9 STAMP=754362800
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND0_TAG20110621T013343_700OV861_.BKP RECID=10 STAMP=754364024
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN0_TAG20110621T013343_700OYC20_.BKP RECID=11 STAMP=754364123
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NNND1_TAG20110621T013544_700OZ19R_.BKP RECID=12 STAMP=754364145
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\APP\RAFIALVI\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2011_06_21\O1_MF_NCSN1_TAG20110621T013544_700P3CKP_.BKP RECID=13 STAMP=754364283
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1HMI7EPO_1_1 RECID=20 STAMP=757316409
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1IMI7EPP_1_1 RECID=21 STAMP=757316410
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1JMI7ES5_1_1 RECID=22 STAMP=757316486
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1NMI7F9M_1_1 RECID=23 STAMP=757316919
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1OMI7FCC_1_1 RECID=24 STAMP=757317004
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110724_1PMI7FDF_1_1 RECID=25 STAMP=757317040
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 RECID=26 STAMP=757318341
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1TMI7GMD_1_1_20110724 RECID=27 STAMP=757318368
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1UMI7GN7_1_1_20110724 RECID=28 STAMP=757318377
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 RECID=29 STAMP=757318347
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_21MIAF62_1_1_20110725 RECID=30 STAMP=757415106
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_23MIAF6E_1_1_20110725 RECID=31 STAMP=757415136
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_22MIAF62_1_1_20110725 RECID=32 STAMP=757415110
Crosschecked 20 objects
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_04MF7PSK_1_1 RECID=2 STAMP=754182037
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_20110618_05MF7PVA_1_1 RECID=3 STAMP=754182123
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_07MF7TM1_1_1_20110619 RECID=4 STAMP=754185922
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_09MF7TM4_1_1_20110619 RECID=5 STAMP=754185933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_0AMF7TML_1_1_20110619 RECID=6 STAMP=754185943
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_08MF7TM2_1_1_20110619 RECID=7 STAMP=754185924
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0LMI4OJ5_1_1 RECID=14 STAMP=757228134
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0MMI4OM5_1_1 RECID=15 STAMP=757228232
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0OMI4PES_1_1 RECID=16 STAMP=757229020
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0PMI4PJ3_1_1 RECID=17 STAMP=757229156
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0RMI4QET_1_1 RECID=18 STAMP=757230046
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\RMAN_BACKUP\ORCL_DATA_BACKUP2_20110723_0SMI4QJ5_1_1 RECID=19 STAMP=757230182
Crosschecked 12 objects
released channel: ch1
released channel: ch2
database opened
Recovery Manager complete.
We are dropping our Database and assuming that all our Database files are lost
or corrupted including the controlfile.I'm including that step also.
Dropping Database:
---------------------
We have to drop Database in mount restrict mode as given below:
To make sure we are dropping the correct Database,please make sure we do the below steps:
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:14:41 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount restrict
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
Database mounted.
SQL> drop database;
Database dropped.
Now I will try to connect the Database,
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 25 09:18:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
Now,Restore and recovery of Database can be done by following below steps:
Step 1: Startup Database with pfile:
Create one pfile in location where previous pfile was existing:
INITORCL.ora:
db_name=ORCL
Step 2:Restore the controlfile with 'RMAN':
RMAN> restore controlfile;
Starting restore at 24-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: copied control file copy
input file name=D:\RMAN_BACKUP\ORCL_CTRL_CF_D-ORCL_ID-1280115002_1VMI7GP2_20110724
output file name=D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24-JUL-11
Step 3: Mount the Database.
C:\Users\RafiAlvi>set ORACLE_SID=ORCL
C:\Users\RafiAlvi>sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 24 08:31:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='D:\APP\RAFIALVI\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITORCL.ORA'
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2174320 bytes
Variable Size 159384208 bytes
Database Buffers 50331648 bytes
Redo Buffers 5267456 bytes
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
Step 4: Restore ,recover and open the Database.
RMAN> restore Database;
Starting restore at 24-JUL-11
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
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 00004 to D:\APP\RAFIALVI\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\RAFIALVI\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET1_1RMI7GM4_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
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 D:\APP\RAFIALVI\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\RAFIALVI\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\RAFIALVI\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724
channel ORA_DISK_1: piece handle=D:\RMAN_BACKUP\ORCL_BK_SET2_1SMI7GM5_1_1_20110724 tag=ORCL_BACKUP_WEEKLY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 24-JUL-11
RMAN> recover database;
Starting recover at 24-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/24/2011 08:55:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database
Step 5:0RA-38760 is because flashback Database is turned ON.
We have to turn OFF and try now,It will work.
CASE 1:IN ARCHIVE LOG MODE:
------------------------------
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
SQL> alter database flashback off;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
CASE 2:IN NOARCHIVELOG MODE
-------------------------------
RMAN> recover database;
Starting recover at 25-JUL-11
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/25/2011 10:29:40
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
(OR)
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00275: media recovery has already been started
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Oracle is amazing :) BECAUSE my Database was in NOARCHIVELOG mode after recovery as I resetlogs it is converted to ARCHIVELOG MODE.
Hope we enjoyed recovery.
Best regards,
Rafi.
No comments:
Post a Comment