Resetlog always somewhat risky option, while opening database after incomplete recovery with resetlog option or point in time recovery and open db in resetlog. So its good to check db can open in ready only and check data is correct as per requirement and resetlog and then attempt it open in reset log.
level 0 backup:
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=0 database plus archivelog tag='Full_backup_level0';
}
level 1 backup:
sql "alter session set nls_date_format=''dd-Mon-yy hh24:mi:ss''";
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=1 database plus archivelog tag='%tag%';
}
backup as compressed backupset incremental level=1 database include current controlfile filesperset=50 tag='db_level_0';
sql "alter session set nls_date_format=''dd-Mon-yy hh24:mi:ss''";
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=0 database include current controlfile filesperset=50 tag='db_level_0';
backup archivelog all delete all input filesperset=4 tag='arch_level_0';
}
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=1 database include current controlfile filesperset=50 tag='db_level_1';
backup archivelog all delete all input filesperset=4 tag='arch_level_1';
}
RMAN> recover database until time "to_date('20-AUG-2015 22:28:11','dd-mon-yyyy hh24:mi:ss')";
Starting recover at 20-AUG-15
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_27QF3U0M_1_1
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_27QF3U0M_1_1 tag=ARCH_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=C:\APP\DILIP\PRODUCT\11.1.0\DB_1\RDBMS\ARC00009_0888271172.001 thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-AUG-15
RMAN> exit
SYS@clone> /
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME
--- ------- ----------------------------------------------------------------- --- ------------------ ----------------------- ---
NO ONLINE 298348 20-AUG-2015 10:28:11 pm
NO ONLINE 298349 20-AUG-2015 10:28:11 pm
2 rows selected.
SYS@clone> alter database open read only;
Database altered.
SYS@clone> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
1 row selected.
SYS@clone> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\app\Dilip\product\11.1.0\db_1\RDBMS
Oldest online log sequence 9
Next log sequence to archive 10
Current log sequence 10
SYS@clone> desc level1
ERROR:
ORA-04043: object level1 does not exist
SYS@clone> desc level10
ERROR:
ORA-04043: object level10 does not exist
SYS@clone>
SYS@clone>
SYS@clone> desc level10
ERROR:
ORA-04043: object level10 does not exist
SYS@clone> desc tab
Name
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
TNAME
TABTYPE
CLUSTERID
SYS@clone> select * from tab where tname like 'LEVEL%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- -----------------
LEVEL_0 TABLE
LEVEL_1 TABLE
2 rows selected.
RMAN> recover database until sequence 11;
Starting recover at 20-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\APP\DILIP\ORADATA\CLONE_TEST\SYSTEM01.DBF
destination for restore of datafile 00005: C:\APP\DILIP\ORADATA\CLONE_TEST\TT1.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_28QF3
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_28QF3U4A_1_1 tag=D
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: C:\APP\DILIP\ORADATA\CLONE_TEST\SYSAUX01.DBF
destination for restore of datafile 00003: C:\APP\DILIP\ORADATA\CLONE_TEST\UNDOTBS01.DBF
destination for restore of datafile 00004: C:\APP\DILIP\ORADATA\CLONE_TEST\TT.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_29QF3
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_29QF3U4A_1_1 tag=D
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
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 08/20/2015 22:56:59
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 29
RMAN> exit
Recovery Manager complete.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Aug 20 22:57:47 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@clone> alter database open read only;
Database altered.
SYS@clone> desc level10
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
SEGMENT_SUBTYPE VARCHAR2(10)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
RETENTION VARCHAR2(7)
MINRETENTION NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
SYS@clone>
### Bingo :)
Or
again try for something
level 0 backup:
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=0 database plus archivelog tag='Full_backup_level0';
}
level 1 backup:
sql "alter session set nls_date_format=''dd-Mon-yy hh24:mi:ss''";
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=1 database plus archivelog tag='%tag%';
}
backup as compressed backupset incremental level=1 database include current controlfile filesperset=50 tag='db_level_0';
sql "alter session set nls_date_format=''dd-Mon-yy hh24:mi:ss''";
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=0 database include current controlfile filesperset=50 tag='db_level_0';
backup archivelog all delete all input filesperset=4 tag='arch_level_0';
}
run{
allocate channel c1 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
allocate channel c2 type disk format 'C:\app\Dilip\oradata\backup_clone\backup_clone\_%D_%T_%U' maxpiecesize 8G;
backup as compressed backupset incremental level=1 database include current controlfile filesperset=50 tag='db_level_1';
backup archivelog all delete all input filesperset=4 tag='arch_level_1';
}
RMAN> recover database until time "to_date('20-AUG-2015 22:28:11','dd-mon-yyyy hh24:mi:ss')";
Starting recover at 20-AUG-15
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_27QF3U0M_1_1
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_27QF3U0M_1_1 tag=ARCH_LEVEL_0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=C:\APP\DILIP\PRODUCT\11.1.0\DB_1\RDBMS\ARC00009_0888271172.001 thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-AUG-15
RMAN> exit
SYS@clone> /
FUZ STATUS ERROR REC CHECKPOINT_CHANGE# CHECKPOINT_TIME
--- ------- ----------------------------------------------------------------- --- ------------------ ----------------------- ---
NO ONLINE 298348 20-AUG-2015 10:28:11 pm
NO ONLINE 298349 20-AUG-2015 10:28:11 pm
2 rows selected.
SYS@clone> alter database open read only;
Database altered.
SYS@clone> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY
1 row selected.
SYS@clone> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\app\Dilip\product\11.1.0\db_1\RDBMS
Oldest online log sequence 9
Next log sequence to archive 10
Current log sequence 10
SYS@clone> desc level1
ERROR:
ORA-04043: object level1 does not exist
SYS@clone> desc level10
ERROR:
ORA-04043: object level10 does not exist
SYS@clone>
SYS@clone>
SYS@clone> desc level10
ERROR:
ORA-04043: object level10 does not exist
SYS@clone> desc tab
Name
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
TNAME
TABTYPE
CLUSTERID
SYS@clone> select * from tab where tname like 'LEVEL%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- -----------------
LEVEL_0 TABLE
LEVEL_1 TABLE
2 rows selected.
RMAN> recover database until sequence 11;
Starting recover at 20-AUG-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\APP\DILIP\ORADATA\CLONE_TEST\SYSTEM01.DBF
destination for restore of datafile 00005: C:\APP\DILIP\ORADATA\CLONE_TEST\TT1.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_28QF3
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_28QF3U4A_1_1 tag=D
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: C:\APP\DILIP\ORADATA\CLONE_TEST\SYSAUX01.DBF
destination for restore of datafile 00003: C:\APP\DILIP\ORADATA\CLONE_TEST\UNDOTBS01.DBF
destination for restore of datafile 00004: C:\APP\DILIP\ORADATA\CLONE_TEST\TT.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_29QF3
channel ORA_DISK_1: piece handle=C:\APP\DILIP\ORADATA\BACKUP_CLONE\BACKUP_CLONE\_20_20150820_29QF3U4A_1_1 tag=D
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
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 08/20/2015 22:56:59
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 29
RMAN> exit
Recovery Manager complete.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Aug 20 22:57:47 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@clone> alter database open read only;
Database altered.
SYS@clone> desc level10
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
SEGMENT_SUBTYPE VARCHAR2(10)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
RETENTION VARCHAR2(7)
MINRETENTION NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
SYS@clone>
### Bingo :)
Or
again try for something
No comments:
Post a Comment