Cold backup with RMAN
Scenario 1:
Most of the time Interviewers ask candidates, I want to take cold backup with RMAN, can I?
And 90% candidates give answer as "No sir, we cant take cold backup with RMAN".
Scenario 2:
Though 10% of people give above answer correct, they fail by commenting "For RMAN backup DB always must be in archivelog mode"
In fact, we can take cold backup with RMAN no matter whether your DB is in archivelog mode or not.
Quick steps:
Backup
1) Shutdown DB
2) Startup mount
3) RMAN>backup database (make sure, controlfile autoback is on / or use COPY CURRENT CONTROLFILE TO 'location';)
4) Alter database open;
5) Create object 2305 in scott schema.
6)shut immediate;
Recovery steps
7) startup nomount;
8) restore controlfile from '';
9) alter database mount;
10) restore database;
11) sql> recover database using backup controlfile until cancel;
12) alter database open resetlogs;
transactions after step 3: wont be recover, if we dont have archivelog.
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:52:29 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL>
SQL>
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exiit
SP2-0042: unknown command "exiit" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:53:28 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 83886424 bytes
Database Buffers 260046848 bytes
Redo Buffers 2945024 bytes
RMAN> backup database;
Starting backup at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NCSNF_TAG20140523T125343_9QXX
WV57_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-14
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:54:37 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> create table scott.test2305 as select * from scott.emp;
Table created.
SQL> select count(*) from scott.test2305;
COUNT(*)
----------
0
SQL>
SQL>
SQL>
SQL> select count(*) from scott.emp;
COUNT(*)
----------
0
SQL>
SQL>
SQL> select * from dba_objects where object_name='TEST2305';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SCOTT
TEST2305
56376 56376 TABLE
23-MAY-14 23-MAY-14 2014-05-23:12:55:03 VALID N N N
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:56:41 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 83886424 bytes
Database Buffers 260046848 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:56:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL1 (DBID=1227596891, not open)
RMAN> restore database;
Starting restore at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1
_MF_NNNDF_TAG20140523T125343_9QXXW0CJ_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAY-14
RMAN> restore database using backup controlfile until cancel;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, channel, check, controlfile, clone, database, da
tafile, device, from, force, high, (, preview, ;, skip, spfile, standby, tablespace, until, validate"
RMAN-01007: at line 1 column 18 file: standard input
RMAN> exit
Recovery Manager complete.
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:57:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3042883 generated at 05/23/2014 12:52:58 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_05_23\O1_MF_1
_1_%U_.ARC
ORA-00280: change 3042883 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
0
SQL> select count(*) from scott.test2305;
select count(*) from scott.test2305
*
ERROR at line 1:
ORA-00942: table or view does not exist
Scenario 1:
Most of the time Interviewers ask candidates, I want to take cold backup with RMAN, can I?
And 90% candidates give answer as "No sir, we cant take cold backup with RMAN".
Scenario 2:
Though 10% of people give above answer correct, they fail by commenting "For RMAN backup DB always must be in archivelog mode"
In fact, we can take cold backup with RMAN no matter whether your DB is in archivelog mode or not.
Quick steps:
Backup
1) Shutdown DB
2) Startup mount
3) RMAN>backup database (make sure, controlfile autoback is on / or use COPY CURRENT CONTROLFILE TO 'location';)
4) Alter database open;
5) Create object 2305 in scott schema.
6)shut immediate;
Recovery steps
7) startup nomount;
8) restore controlfile from '';
9) alter database mount;
10) restore database;
11) sql> recover database using backup controlfile until cancel;
12) alter database open resetlogs;
transactions after step 3: wont be recover, if we dont have archivelog.
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:52:29 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Current log sequence 1
SQL>
SQL>
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exiit
SP2-0042: unknown command "exiit" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:53:28 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 83886424 bytes
Database Buffers 260046848 bytes
Redo Buffers 2945024 bytes
RMAN> backup database;
Starting backup at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-MAY-14
channel ORA_DISK_1: finished piece 1 at 23-MAY-14
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NCSNF_TAG20140523T125343_9QXX
WV57_.BKP tag=TAG20140523T125343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 23-MAY-14
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:54:37 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> create table scott.test2305 as select * from scott.emp;
Table created.
SQL> select count(*) from scott.test2305;
COUNT(*)
----------
0
SQL>
SQL>
SQL>
SQL> select count(*) from scott.emp;
COUNT(*)
----------
0
SQL>
SQL>
SQL> select * from dba_objects where object_name='TEST2305';
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SCOTT
TEST2305
56376 56376 TABLE
23-MAY-14 23-MAY-14 2014-05-23:12:55:03 VALID N N N
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:56:41 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 83886424 bytes
Database Buffers 260046848 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 23 12:56:56 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL1 (DBID=1227596891, not open)
RMAN> restore database;
Starting restore at 23-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1
_MF_NNNDF_TAG20140523T125343_9QXXW0CJ_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2014_05_23\O1_MF_NNNDF_TAG20140523T125343_9QXX
W0CJ_.BKP tag=TAG20140523T125343
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 23-MAY-14
RMAN> restore database using backup controlfile until cancel;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "using": expecting one of: "archivelog, channel, check, controlfile, clone, database, da
tafile, device, from, force, high, (, preview, ;, skip, spfile, standby, tablespace, until, validate"
RMAN-01007: at line 1 column 18 file: standard input
RMAN> exit
Recovery Manager complete.
C:\Users\oradb>sqlplus sys/orcl1@orcl1 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 23 12:57:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 3042883 generated at 05/23/2014 12:52:58 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_05_23\O1_MF_1
_1_%U_.ARC
ORA-00280: change 3042883 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select count(*) from scott.emp;
COUNT(*)
----------
0
SQL> select count(*) from scott.test2305;
select count(*) from scott.test2305
*
ERROR at line 1:
ORA-00942: table or view does not exist
No comments:
Post a Comment