ORACLE 11G R2: RESTORE A OLD DATABASE SPFILE FROM RMAN (USING BACKUP PIECE)

Database: ORCL
ORCL DBID: 3343811387
RMAN Database: RMANDB

If you need restore a old spfile from rman backup

1.- First startup ORCL database with pfile, if any, and enter in RMAN to see that backup piece can be used


Rman target / catalog=rman/temporal@rmandb

RMAN> Set dbid 3343811387;

RMAN> list backup of spfile;

List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7730769 Full 17.75M SBT_TAPE 00:00:31 09-AUG-11
BP Key: 7730771 Status: AVAILABLE Compressed: NO Tag: TAG20110809T122115
Handle: 03mjicct_1_1 Media: 0186L4
SPFILE Included: Modification time: 09-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7730787 Full 256.00K SBT_TAPE 00:00:30 09-AUG-11
BP Key: 7730790 Status: AVAILABLE Compressed: NO Tag: TAG20110809T122330
Handle: 04mjice4_1_1 Media: 0186L4
SPFILE Included: Modification time: 09-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7741855 Full 17.75M SBT_TAPE 00:01:11 10-AUG-11
BP Key: 7741858 Status: AVAILABLE Compressed: NO Tag: TAG20110810T101732
Handle: 06mjkpgu_1_1 Media: 0186L4
SPFILE Included: Modification time: 10-AUG-11
SPFILE db_unique_name: ORCL

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7741878 Full 256.00K SBT_TAPE 00:00:36 11-AUG-11
BP Key: 7741881 Status: AVAILABLE Compressed: NO Tag: TAG20110810T102027
Handle: 07mjkpjd_1_1 Media: 0186L4
SPFILE Included: Modification time: 11-AUG-11
SPFILE db_unique_name: ORCL
RMAN> exit

2.- Then shutdown ORCL database and realize the restore from specific bakup piece.

Rman target / catalog=rman/temporal@rmandb
RMAN> Set dbid 3343811387;
RMAN> Startup force nomount;
RMAN> run {
2> allocate channel c1 device type 'SBT_TAPE';
3> RESTORE SPFILE to '+DATA' FROM '04mjice4_1_1';
}
RMAN> shutdown immediate;
RMAN> exit

Then you can startup the ORCL database normally with the restored spfile.

ORACLE 11G R2: HOW DO YOU CREATE CONNECTIVITY WITHIN THE CLUSTER (KEYGEN DSA,RSA)

CREATE CONNECTIVITY WITHIN THE CLUSTER

We have a cluster with two members (Host1, Host2).
The same steps for DSA and RSA.
In ORACLE_HOME using oracle user.

1.- Generate the keys.

First we generate in Host1

[oracle@Host1]$ ssh-keygen -t dsa
(all by default)

      Generating public/private dsa key pair.
      Enter file in which to save the key (/home/oracle/.ssh/id_dsa):
      Created directory '/home/oracle/.ssh'.
      Enter passphrase (empty for no passphrase):
     Enter same passphrase again:
     Your identification has been saved in /home/oracle/.ssh/id_dsa.
     Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
     The key fingerprint is:
     5d:8c:42:97:eb:42:ae:52:52:e9:59:20:2a:d3:6f:59 oracle@Host1.dbsconsult.com

2.- Transfer the keys


The public key on each node is copied to both nodes. Execute the following on each node.

In Host2

[oracle@Host2]$ ssh Host1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys


       The authenticity of host 'Host1 (192.168.0.184)' can't be established.

       RSA key fingerprint is 00:d9:70:08:bc:fd:b5:e4:e3:df:a3:c7:d8:46:1e:a5.

      Are you sure you want to continue connecting (yes/no)? yes

      Warning: Permanently added 'Host1,192.168.0.184' (RSA) to the list of known hosts.

      oracle@Host1's password:


In Host1

[oracle@Host1]$ ssh Host2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

     The authenticity of host 'Host2 (192.168.0.185)' can't be established.

     RSA key fingerprint is 00:d9:70:08:bc:fd:b5:e4:e3:df:a3:c7:d8:46:1e:a5.

    Are you sure you want to continue connecting (yes/no)? yes

    Warning: Permanently added 'Host2,192.168.0.185' (RSA) to the list of known hosts.

    oracle@Host2's password:

TESTING THE CONECTIVITY

Execute the command in both nodes:

./cluvfy comp nodecon -n Host1,Host2

Verifying node connectivity

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Node connectivity passed for subnet "172.21.54.0" with node(s) Host2,Host1

TCP connectivity check passed for subnet "172.21.54.0"

Node connectivity passed for subnet "10.10.10.0" with node(s) Host2,Host1

TCP connectivity check passed for subnet "10.10.10.0"

Interfaces found on subnet "172.21.54.0" that are likely candidates for VIP are:

Host2 eth0:172.21.54.31

Host1 eth0:172.21.54.30

Interfaces found on subnet "10.10.10.0" that are likely candidates for a private interconnect are:

Host2 eth1:10.10.10.31

Host1 eth1:10.10.10.30

Node connectivity check passed

Verification of node connectivity was successful.

ORA-01578: HOW TO KNOW WHICH OBJECT (TABLE, INDEX....) IS CORRUPTED

        Sometimes, in alert log file it's appears this message (ORA-01578: ORACLE data block corrupted). You can know which object is corrputed whit these steps and to decide if fix it deleting/recreating object (index, table.. ) or restore object using RMAN BLOCKRECOVER.

ORA-01578: ORACLE data block corrupted (file # 15, block # 608562)
ORA-01110: data file 15: '/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'

1.- DBVERIFY

      Using oracle dbverify tool in system prompt to confirm corruption

dbv file=/software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'


DBVERIFY: Release 11.2.0.2.0 - Production on Tue Oct 16 09:45:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /software/oracle/wind11/oradata/ORCL/dbfiles/ORCLusers03.dbf'

DBV-00200: Block, DBA 63523122, already marked corrupt
csc(0x0000.3de058f1) higher than block scn(0x0000.00000000)
Page 608562 failed with check code 6054


DBVERIFY - Verification complete

Total Pages Examined         : 2621440
Total Pages Processed (Data) : 2268458
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 45544
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 8958
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 298480
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 1109032205 (0.1109032205)

2.-  Script SQLPLus to confirm the block marked as corrupted with dbverify is the same it's appears in alert log file

select dbms_utility.data_block_address_file(&&rdba) RFN,
  2  dbms_utility.data_block_address_block(&&rdba) BL
   3  from dual;
 Enter value for rdba: 63523122
old   1: select dbms_utility.data_block_address_file(&&rdba) RFN,
new   1: select dbms_utility.data_block_address_file( 63523122) RFN,
old   2: dbms_utility.data_block_address_block(&&rdba) BL
new   2: dbms_utility.data_block_address_block( 63523122) BL

       RFN         BL
---------- ----------
        15     608562

3.- Script SQLPlus to confirm the tablespace

select file_id AFN, relative_fno, tablespace_name
 from dba_data_files
 where relative_fno=&RFN;
Enter value for rfn: 15
old   3:  where relative_fno=&RFN
new   3:  where relative_fno=15

       AFN RELATIVE_FNO TABLESPACE_NAME
---------- ------------ ------------------------------
        15           15 USERS

4.- Script SQLPlus to know the object corrupted

select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1;
Enter value for afn: 15
Enter value for bl: 608562
old   1: select * from dba_extents where file_id = &AFN and &BL between block_id AND block_id + blocks - 1
new   1: select * from dba_extents where file_id = 15 and 608562 between block_id AND block_id + blocks - 1

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
ORCLUSER
PAGERESULTS
                               TABLE              USERS
         0         15     607872    8388608       1024           15

 In this case it's a table, now you must to decide if restore the block using RMAN, or if the table is used as repository temporary table in operations and if it can be deleted and recreated or if the data contained can be restore with oracle utility data pump.