RMAN cancel based recovery !!!

source :
 http://dba-oracle.com/bk_recover_database_until_cancel_tips.htm
 http://www.dba-oracle.com/t_oracle_open_resetlogs_tips.htm


Problem:  How do I recover until cancel with Oracle when doing a roll forward?

Can someone explain why it is necessary to perform "recover database until cancel" before using "alter database open resetlogs" when you are restoring a cold backup without a backup of the redo log files ?

I perform the following steps, which I thought would be very simple, however I get an error on opening the database.

SHUTDOWN IMMEDIATE

Restore all backup datafiles and controlfiles (OS copy)

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS

ORA-01139: RESETLOGS option only valid after an incomplete database recovery
So I tried using: 

RECOVER DATABASE UNTIL CANCEL

ALTER DATABASE OPEN RESETLOGS

And this works, the database is opened and the new redo logs are created.

I got a trace of my controlfile and found the following..

CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVEMODE .....
I think it is no NORESETLOGS that is causing the problem as if I re-create the control file with RESETLOGS specified it works fine.

Solution:  The Oracle documentation notes:

"In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specifyCANCEL instead of a filename or when all redo has been applied to the datafiles.

Cancel-based recovery is better than change-based or time-based recovery if you want to control which archived log terminates recovery. For example, you may know that you have lost all logs past sequence 1234, so you want to cancel recovery after log 1233 is applied.

You should perform cancel-based media recovery in these stages:

1. Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".
2. Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.
3. Perform media recovery on the restored database backup as described in the following procedure.

To perform cancel-based recovery:

1. Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:% sqlplus '/ AS SYSDBA'
2. Start a new instance and mount the database:
   STARTUP MOUNT
3. Begin cancel-based recovery by issuing the following command:RECOVER DATABASE UNTIL CANCEL
   
   If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.
   
   RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

   Note:If you fail to specify the UNTIL clause on the RECOVERcommand, then you will not be able to open the database until a complete recovery is done.

4. Oracle applies the necessary redo log files to reconstruct the restored datafiles. Oracle supplies the name it expects to find fromLOG_ARCHIVE_DEST_1 and requests you to stop or proceed with applying the log file. Note that if the control file is a backup, then you must supply the names of the online logs if you want to apply the changes in these logs.

Note:If you use an Oracle Real Application Clusters configuration, and you are performing incomplete recovery or using a backup control file, then Oracle can only compute the name of the first archived redo log file from the first thread. The first redo log file from the other threads must be supplied by the user. After the first log file in a given thread has been supplied, Oracle can suggest the names of the subsequent log files in this thread.

5. Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:
   CANCEL
   
   Oracle returns a message indicating whether recovery is successful. Note that if you cancel recovery before all the datafiles have been recovered to a consistent SCN and then try to open the database, you will get an ORA-1113error if more recovery is necessary for the file. You can queryV$RECOVER_FILE to determine whether more recovery is needed, or if a backup of a datafile was not restored prior to starting incomplete recovery.

6. Open the database in RESETLOGS mode. You must always reset the online logs after incomplete recovery or recovery with a backup control file. For example, enter:

   ALTER DATABASE OPEN RESETLOGS;"

Oracle RMAN Simplified Recovery Through OPEN RESETLOGS

Following an incomplete point-in-time recovery or recovery without a backup control file, it will be necessary to open the database with the OPEN RESETLOGS option as follows:

SQL> alter database open resetlogs;

The OPEN RESETLOGS operation creates a new incarnation of the database, resets the log sequence to 1 and online redo logs are given a new time stamp and SCN.  Prior to Oracle 10g, the newly generated redo log files could not be used with the backups taken in the past.  Therefore, it was historically important to take an immediate backup as all previous backups became invalid.  Using OPEN RESETLOGS can negate the need for that backup.

Without the use of OPEN RESETLOGS, when using the RMAN catalog for future backups, the following command had to be issued to make the RMAN catalog aware of the new incarnation of the database.

RMAN> reset database;

As of Oracle 10g, it is no longer necessary to back up the database following an incomplete recovery and OPEN RESETLOGS operations. 

The OPEN RESETLOGS feature is also applicable for the following two scenarios:

• Performing a recovery using a backup control file and opening the database with the RESETLOGS operation. 
• Reinstantiation of  the old primary database following a failover

When the new database incarnation is created following the use of OPEN RESETLOGS and the log sequence reset, orphaned backups are created.  These orphaned backups can be used by RMAN to restore the database to a point in time not in the current incarnation path.
It is possible for RMAN to restore backups from direct ancestor incarnations and recover up to the current time as long as a continuous path of archived logs exists from the earliest backups to the desired point of recovery.  This holds true even across OPEN RESETLOGS operations.
 Under certain circumstances RMAN can also perform restore and recovery with orphaned backups via the restoration of a control file from an appropriate incarnation.

source

Ways of generating EXECUTION PLAN in Oracle Database

Generating EXECUTION PLAN from explain plan is the best method of reading the plan that optimizer defined for executing a SQL statement in Oracle Database. There are multiple ways of generating such execution plans, one is by running EXPLAIN PLAN and other from V$SQL_PLAN. In this blog, I am going to cover in detail about ways of generating EXECUTION PLAN that might help Oracle DBA in his day to day performance tuning activities.

Running EXPLAIN PLAN is detailed in my earlier blog EXPLAIN PLAN for multiple SQL statements.

Activities:

1. Generate EXECUTION PLAN from PLAN_TABLE.
2. Generate EXECUTION PLAN using DBMS_XPLAN.

2.1 Using function DISPLAY().

2.2 Using function DISPLAY_CURSOR().

1. Generate EXECUTION PLAN from PLAN_TABLE.

Explain plan records the execution plan of optimizer into the default table PLAN_TABLE. We can fetch the execution plan from PLAN_TABLE by running a conventional SELECT statement with proper formatting which can look alike the output of DBMS_XPLAN package.

Every user session will have PLAN_TABLE accessible to allow user generated execution plan since Oracle 10g.

Let us now run explain plan on one SQL statement and then write SELECT statement to generate reasonable execution plan from PLAN_TABLE.

SQL> set lines 100

SQL> col EXECUTION_PLAN for a50

SQL> show user

USER is “SCOTT”

SQL> explain plan for

  select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

Explained.

SQL> select rtrim(lpad(‘ ‘,2*LEVEL) || rtrim(operation) || ‘ ‘ || rtrim(options) || ‘ ‘ || object_name) EXECUTION_PLAN, cost, cardinality from plan_table connect by prior id=parent_id start with id=0;

EXECUTION_PLAN                                           COST CARDINALITY

————————————————–      ———- ———–

  SELECT STATEMENT                                         298       31225

    TABLE ACCESS FULL T1                                  298       31225

Consider modifying this SELECT statement to include/exclude the columns you wish to be in the execution plan.

2. Generate EXECUTION PLAN using DBMS_XPLAN.

DBMS_XPLAN is the most widely used package to generate execution plan of SQL statement. There are two functions DISPLAY() and DISPLAY_CURSOR() mostly used with this package.

DISPLAY() function will extract the execution plan of a specific SQL statement(s) from PLAN_TABLE, this means running EXPLAIN PLAN of that statement is pre-requisite.

DISPLAY_CURSOR() function is used to extract execution plan of SQL statement from V$SQL_PLAN  if query is available in shared pool.

Let us generate execution plan with these two functions.

2.1 Using function DISPLAY().

SQL> explain plan for

  2  select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

—————————————————————————————————-

Plan hash value: 3617692013

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      | 31225 |  2988K|   298   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T1   | 31225 |  2988K|   298   (1)| 00:00:04 |

————————————————————————–

Predicate Information (identified by operation id):

—————————————————

PLAN_TABLE_OUTPUT

—————————————————————————————————-

   1 – filter(“OWNER”=’DBSNMP’ OR “OWNER”=’SYS’ OR “OWNER”=’SYSTEM’)

13 rows selected.

Compare this execution plan with the output of SELECT statement we ran on PLAN_TABLE in the above discussion.

2.2 Using function DISPLAY_CURSOR().

To generate execution plan of a query using DISPLAY_CURSOR, you will require SQL ID and cursor child number of that query.

I have run the below SQL from SCOTT schema and captured SQL_ID and latest cursor child number from V$SQL, you can consider using V$SQL_SHARED_CURSOR for much details on cursor.

SQL>conn scott/password

Connected.

SQL>select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’);

SQL>conn / as sysdba

SQL> select sql_id,child_number from v$sql where sql_text like ‘select * from t1 where owner in(%';

SQL_ID        CHILD_NUMBER

————- ————

54skb3zk6qux7 0

SQL> select * from table(dbms_xplan.display_cursor(’54skb3zk6qux7′,0,’TYPICAL’));

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID  54skb3zk6qux7, child number 0

————————————-

select * from t1 where owner in(‘SYS’,’SYSTEM’,’DBSNMP’)

Plan hash value: 3617692013

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   298 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   | 31225 |  2988K|   298   (1)| 00:00:04 |

PLAN_TABLE_OUTPUT

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

   1 – filter((“OWNER”=’DBSNMP’ OR “OWNER”=’SYS’ OR “OWNER”=’SYSTEM’))

18 rows selected.

Conclusion:

• We can use explain plan if the statement is not found in the shared_pool.
• If the SQL statement is already in shared_pool use DISPLAY_CURSOR function from DBMS_XPLAN package.
• It is always advisable to generate EXECUTION PLAN using DBMS_XPLAN.DISPLAY_CURSOR as it gives us the accurate plan of execution.
• EXPLAIN PLAN will generate execution plan without considering the actual values of bind variables. Optimizer is intelligent enough to change the execution plan before executing the SQL statement by considering bind variable values which can be only found using DISPLAY_CURSOR function.

                       youtube : https://youtu.be/s0Lyh-hQo-g

How ASM disk header block repair works !!

source : http://laurent-leturgez.com/2012/11/12/how-asm-disk-header-block-repair-works/

How ASM disk header block repair works

10 CommentsPosted by Laurent on November 12, 2012

In this post, I will explain one of my last works about the ASM disk header block.

First, I will create a TEST tablespace in my orcl database. The TEST’s datafile will be managed by ASM.

[oracle@oel ~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel ~]$ sqlplus / as sysdba
SQL> create tablespace test datafile '+data' size 5M autoextend on maxsize unlimited;
Tablespace created.

SQL>  select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl

SQL> select file_id,tablespace_name,file_name,status,online_status from dba_data_files
  2  /

   FILE_ID TABLESPACE_NAME                FILE_NAME                                          STATUS    ONLINE_
---------- ------------------------------ -------------------------------------------------- --------- -------
         4 USERS                          /u02/oradata/orcl/users01.dbf                      AVAILABLE ONLINE
         3 UNDOTBS1                       /u02/oradata/orcl/undotbs01.dbf                    AVAILABLE ONLINE
         2 SYSAUX                         /u02/oradata/orcl/sysaux01.dbf                     AVAILABLE ONLINE
         1 SYSTEM                         /u02/oradata/orcl/system01.dbf                     AVAILABLE SYSTEM
         5 EXAMPLE                        /u02/oradata/orcl/example01.dbf                    AVAILABLE ONLINE
         6 TEST                           +DATA/orcl/datafile/test.258.798578613             AVAILABLE ONLINE

6 rows selected.

My ASM instance manages two diskgroups DATA (external redundancy) and RL (normal redundancy).

DATA is a 2 disks diskgroup managed by asmlib.

SQL> select group_number,name,type,state from v$asm_diskgroup
  2  /

GROUP_NUMBER NAME TYPE   STATE
------------ ---- ------ -----------
           1 DATA EXTERN MOUNTED
           2 RL   NORMAL MOUNTED
SQL> select path from v$asm_disk where group_number=1;

PATH
---------------------------------
ORCL:ASM1
ORCL:ASM2

And now, we erase the header block of each disk. Header block is the first one of an ASM disk device, and its default size is 4096 bytes (This size is available in _asm_blksize undocumented parameter).

[oracle@oel ~]$ dd if=/dev/zero of=/dev/oracleasm/disks/ASM1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 2.2e-05 seconds, 186 MB/s
[oracle@oel ~]$ dd if=/dev/zero of=/dev/oracleasm/disks/ASM2 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000336 seconds, 12.2 MB/s

Even my block headers destroyed, I can still write in my diskgroup and allocate extents in it.

SQL> alter database datafile 6 resize 10M;

Database altered.

SQL> create table t tablespace TEST as select * from dba_source;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select file#,checkpoint_time from v$datafile_header where file#=6;

     FILE# CHECKPOINT_TIME
---------- -------------------
         6 05/11/2012 19:27:43

Well, let’s try to restart the rdbms instance:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2235208 bytes
Variable Size             377488568 bytes
Database Buffers          683671552 bytes
Redo Buffers                5541888 bytes
Database mounted.
Database opened.
SQL> select count(*) from t;

  COUNT(*)
----------
    702070

Damned ! I can still restart it and read all file extents.

There’s something strange in this demo, my ASM disk header is invalid and I still can read files. I will verify with kfed my headers state (only one ASM disk is shown below):

oracle@oel ~]$ kfed read /dev/oracleasm/disks/ASM1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
2B469E002400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

[oracle@oel ~]$ sudo /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/kfed read /dev/sdb1
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
2B413559C400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: file not found; arguments: [kfbtTraverseBlock] [Invalid OSM block type] [] [0]

So KFED has confirmed my header blocks are invalid. Now, I will try to unmount and remount the diskgroup to see if there’s any effect on diskgroup mount operation.

[oracle@oel ~]$ sqlplus / as sysasm

SQL> alter diskgroup DATA dismount;

Diskgroup altered.

SQL> alter diskgroup DATA mount;
alter diskgroup DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

So ASM disk header seems to be important for mounting the diskgroup but without effect on asm extent allocation etc.

Now I will repair asm disks with kfed and repair operation to restore my asm header blocks:

[oracle@oel ~]$ kfed repair /dev/oracleasm/disks/ASM1
[oracle@oel ~]$ kfed repair /dev/oracleasm/disks/ASM2

[oracle@oel ~]$ sqlplus / as sysasm

SQL> alter diskgroup DATA mount;

Diskgroup altered.

So KFED is able to restore the header (with the repair operation).

[oracle@oel ~]$ . oraenv
ORACLE_SID = [+ASM] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel ~]$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup
...
Database opened.
SQL> select count(*) from T;

  COUNT(*)
----------
    702070

And datas in the T table are still there … no problem !

Well, let’s summarize … I erased the header block of both asm disks in my diskgroup without any impact on file extent allocation. The only impact I had was on the mount capability. Next, I have restored the header block of each disk with a kfed repair operation … yes, but without any backup of the disk or the disk header. So I wonder … where was the backup of my header block?

First, I will try a very simple method. I will have a look at all metadata blocks in my asm disk device. I hope I will find another header block. For this purpose, i will use a basic shell script to analyze each block. If the script finds a block with the type KFBTYP_DISKHEAD, it will keep the block position, and print them at the end of script execution:

The basic script:

#!/bin/bash
#set -x
export ORAENV_ASK=NO
export ORACLE_SID=+ASM
. oraenv

i=0
ret=0
tab_cnt=0;
blk_typ='';

while [ "$blk_typ" != "KFBTYP_INVALID" ];
do
  blk_typ=`kfed read $1 blkn=$i | grep kfbh.type | awk '{print $5;}' | sed 's/^ *//g' | sed 's/ *$//g' `
  ret=$?
  if [ "$blk_typ" = "KFBTYP_DISKHEAD" ]; then
    t[$i]=$i
  fi
  let i=$i+1
done
echo "list of header block with KFBTYP_DISKHEAD type"
echo ${t[@]}

Execution results:

[oracle@oel ~]$ ./asm_surgery.sh /dev/oracleasm/disks/ASM1
The Oracle base remains unchanged with value /u01/app/oracle
list of header block with KFBTYP_DISKHEAD type
0 510
[oracle@oel ~]$ ./asm_surgery.sh /dev/oracleasm/disks/ASM2
The Oracle base remains unchanged with value /u01/app/oracle
list of header block with KFBTYP_DISKHEAD type
0 510

Ok, so there is a copy of the header block in the 510th block in my disk. Indeed, as Bane Radulović mentioned it in its blog, a backup copy of ASM disk header is in the second last block of allocation unit 1. So, if my AU is 1Mb and my block size 4096 bytes, a copy of my header block will be available in ((1048576 / 4096) * 2 – 1  = 511), as the block# starts at 0, it is located in the 510th block.

To double check this, I will erase the header block of my first ASM disk, and use kfed repair and strace to see what really happens:

[oracle@oel ~]$ dd if=/dev/zero of=/dev/oracleasm/disks/ASM1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 2.3e-05 seconds, 178 MB/s

[oracle@oel ~]$ strace kfed repair /dev/oracleasm/disks/ASM1
.../...

stat("/dev/oracleasm/disks/ASM1", {st_mode=S_IFBLK|0660, st_rdev=makedev(8, 17), ...}) = 0
access("/dev/oracleasm/disks/ASM1", F_OK) = 0
statfs("/dev/oracleasm/disks/ASM1", {f_type=0x958459f6, f_bsize=4096, f_blocks=0, f_bfree=0, f_bavail=0, f_files=0, f_ffree=0, f_fsid={0, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/dev/oracleasm/disks/ASM1", O_RDWR) = 7
lseek(7, 2088960, SEEK_SET)             = 2088960
read(7, "\1\202\1\1\376\200<\206\371\7"..., 4096) = 4096
lseek(7, 0, SEEK_SET)                   = 0
read(7, ""..., 4096) = 4096
lseek(7, 0, SEEK_SET)                   = 0
write(7, "\1\202\1\1\200\302\206\371\7"..., 4096) = 4096
close(7)                                = 0

This is very interesting.

First operation, it opens the device and read the block after 2088960 bytes. But 2088960/4096 = 510, so it reads the 510th block of the disk. Next it reads the block at position 0 (header block), and then writes in it the content of the 510th block.

Well, now I know there’s a copy of each header block in a secret position !!! (block #510) and kfed uses this block to repair the main header block.

During my tests, I noticed that sometimes, asm disks lost their asm label (I don’t know why). As a consequence, repaired disks won’t be recreated by the oracleasm scandisks (or after a reboot).

[root@oel ~]# ls /dev/oracleasm/disks/*
/dev/oracleasm/disks/ASM1  /dev/oracleasm/disks/ASM2  /dev/oracleasm/disks/ASM3  /dev/oracleasm/disks/ASM4
[root@oel ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Cleaning disk "ASM1"
Scanning system for ASM disks...
[root@oel ~]# ls /dev/oracleasm/disks/*
/dev/oracleasm/disks/ASM2  /dev/oracleasm/disks/ASM3  /dev/oracleasm/disks/ASM4

According the fact that you know which device is corresponding to asmlib disk device, you can relabel this disk with oracleasm renamedisk command (be very careful with this command):

[root@oel ~]# oracleasm renamedisk -f /dev/sdb1 ASM1
Writing disk header: done
Instantiating disk "ASM1": done

[root@oel ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...

[root@oel ~]# ls /dev/oracleasm/disks/*
/dev/oracleasm/disks/ASM1  /dev/oracleasm/disks/ASM2  /dev/oracleasm/disks/ASM3  /dev/oracleasm/disks/ASM4

About these ads

Share this:

• Facebook2
• Twitter
• LinkedIn
• Email
• Google
• Reddit

Related

Oracle Database 12c released ... some featuresIn "12c"

Managing database audit Trail in Oracle 11gR2In "Administration"

Oracle 11g md_restore and compatible.rdbmsIn "Administration"

11g, ASM / ADVM, Internals, Oracle

← How I prepared my 11g OCM examEM 12c agent diagnostic with emctl →