Thursday, 20 August 2015

RMAN read only open scenario before resetlogs

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

No comments:

Post a Comment