restore and recover a database from complete loss(from disk backup)

-- move older pfiles in dbs directory

[oracle@subhen-db02 dbs]$ ls -ltr|grep -i ORCLDB
-rw-rwxr--+ 1 oracle oinstall        46 Dec  5 17:20 initORCLDB_2.ora
-rw-rwx---+ 1 oracle oinstall      2560 Dec  5 17:21 orapwORCLDB
-rw-rwxr--+ 1 oracle oinstall        69 Dec  7 16:14 initORCLDB_3.ora
-rw-rwx---+ 1 oracle oinstall      1544 Jan 17 18:42 hc_ORCLDB_2.dat
-rw-rwxr--+ 1 oracle oinstall        69 Jan 17 23:02 initORCLDB_1.ora
-rw-rwx---+ 1 oracle oinstall      1544 Mar 25 11:14 hc_ORCLDB_1.dat
-rw-rwx---+ 1 oracle oinstall      1544 Apr 16 16:32 hc_ORCLDB_3.dat
[oracle@subhen-db02 dbs]$ mv initORCLDB_3.ora initORCLDB_3.ora.old
[oracle@subhen-db02 dbs]$ mv initORCLDB_1.ora initORCLDB_1.ora.old
[oracle@subhen-db02 dbs]$ mv initORCLDB_2.ora initORCLDB_2.ora.old


-- make dummy pfile to prepare the database for startup force nomount option

[oracle@subhen-db02 dbs]$ cat initORCLDB_1.ora
sga_target=4G
db_name=ORCLDB

-- set dbid and startup force nomount from RMAN prompt and restore spfile from autobackup or any valid backup location

[oracle@subhen-db02 dbs]$ . oraenv
ORACLE_SID = [oracle] ? ORCLDB_1
The Oracle base has been set to /oracle/app
[oracle@subhen-db02 dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 17 14:59:41 2013

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

connected to target database (not started)

RMAN> set dbid 783526276

executing command: SET DBID

RMAN> startup force nomount

Oracle instance started

Total System Global Area    4275781632 bytes

Fixed Size                     2235208 bytes
Variable Size                822084792 bytes
Database Buffers            3439329280 bytes
Redo Buffers                  12132352 bytes

RMAN> restore spfile from '+FRA/ORCLDB/backupset/2013_04_10/ncsnf0_backup_ORCLDB_di_0410130300_0.9694.812386853';

Starting restore at 17-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/ORCLDB/backupset/2013_04_10/ncsnf0_backup_ORCLDB_di_0410130300_0.9694.812386853
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 17-APR-13


-- shutdown and startup again for the spfile to take effect and restore control from autobackup or any valid backup location and mount database

[oracle@subhen-db02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 15:02:53 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@subhen-db02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 15:05:31 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2240344 bytes
Variable Size            4143972520 bytes
Database Buffers         2231369728 bytes
Redo Buffers               36098048 bytes
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/database/11.2.0.3/
                                                 dbhome_1/dbs/spfileORCLDB_1.
                                                 ora
SQL> exit

[oracle@subhen-db02 dbs]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 17 15:06:02 2013

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

connected to target database: ORCLDB (not mounted)

RMAN> set dbid 783526276

executing command: SET DBID

RMAN> restore controlfile from '+FRA/ORCLDB/backupset/2013_04_10/ncsnf0_backup_ORCLDB_di_0410130300_0.9694.812386853';

Starting restore at 17-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=835 instance=ORCLDB_1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DATA/ORCLDB/controlfile/current.819.812991983
output file name=+FRA/ORCLDB/controlfile/current.15606.801246979
Finished restore at 17-APR-13

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

-- restore and reover database from backup

RMAN> restore database
2> ;

Starting restore at 17-APR-13
Starting implicit crosscheck backup at 17-APR-13
allocated channel: ORA_DISK_1
Crosschecked 28 objects
Finished implicit crosscheck backup at 17-APR-13

Starting implicit crosscheck copy at 17-APR-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 17-APR-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_10/ncsnf0_BACKUP_ORCLDB_DI_0410130300_0.9694.812386853
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_10/annnf0_BACKUP_ORCLDB_DI_0410130300_0.9209.812386865
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_10/ncnnf0_BACKUP_ORCLDB_DI_0410130300_0.15191.812386887
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_16/annnf0_BACKUP_ORCLDB_DI_0416130216_0.15334.812902583
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_15/annnf0_BACKUP_ORCLDB_DI_0415130216_0.9957.812816187
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_14/annnf0_BACKUP_ORCLDB_DI_0414130216_0.8798.812729787
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_12/annnf0_BACKUP_ORCLDB_DI_0412130216_0.4151.812557003
File Name: +FRA/ORCLDB/BACKUPSET/2013_04_11/annnf0_BACKUP_ORCLDB_DI_0411130216_0.23705.812470583
File Name: +FRA/ORCLDB/snapcf_dir/ORCLDB.f

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/ORCLDB/datafile/system.3924.801247003
channel ORA_DISK_1: restoring datafile 00002 to +DATA/ORCLDB/datafile/sysaux.3923.801247003
channel ORA_DISK_1: restoring datafile 00003 to +DATA/ORCLDB/datafile/undotbs1.3926.801247003
channel ORA_DISK_1: restoring datafile 00004 to +DATA/ORCLDB/datafile/users.3929.801247003
channel ORA_DISK_1: restoring datafile 00005 to +DATA/ORCLDB/datafile/undotbs2.3928.801247003
channel ORA_DISK_1: restoring datafile 00006 to +DATA/ORCLDB/datafile/undotbs3.3927.801247003
channel ORA_DISK_1: restoring datafile 00007 to +DATA/ORCLDB/datafile/ggate_data.3925.801247003
channel ORA_DISK_1: restoring datafile 00008 to +DATA/ORCLDB/datafile/nwhads_plum.341.801756351
channel ORA_DISK_1: restoring datafile 00009 to +DATA/ORCLDB/datafile/nwhads_ddm1.406.801756537
channel ORA_DISK_1: restoring datafile 00010 to +DATA/ORCLDB/datafile/nwhads_ddm2.347.801756577
channel ORA_DISK_1: restoring datafile 00011 to +DATA/ORCLDB/datafile/nwhads_pah.3157.801756615
channel ORA_DISK_1: restoring datafile 00012 to +DATA/ORCLDB/datafile/superstream.3830.809895841
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_10/nnndf0_backup_ORCLDB_di_0410130300_0.11438.812386815
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_10/nnndf0_backup_ORCLDB_di_0410130300_0.11438.812386815 tag=BACKUP_ORCLDB_DI_041013030001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 17-APR-13

RMAN> recover database;

Starting recover at 17-APR-13
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 355 is already on disk as file +FRA/ORCLDB/onlinelog/group_7.15602.801247063
archived log for thread 1 with sequence 356 is already on disk as file +FRA/ORCLDB/onlinelog/group_8.6650.801247065
archived log for thread 1 with sequence 357 is already on disk as file +FRA/ORCLDB/onlinelog/group_1.15605.801247059
archived log for thread 1 with sequence 358 is already on disk as file +FRA/ORCLDB/onlinelog/group_2.15467.801247061
archived log for thread 2 with sequence 328 is already on disk as file +FRA/ORCLDB/onlinelog/group_10.7498.801331015
archived log for thread 2 with sequence 329 is already on disk as file +FRA/ORCLDB/onlinelog/group_3.15603.801247047
archived log for thread 2 with sequence 330 is already on disk as file +FRA/ORCLDB/onlinelog/group_4.7512.801247051
archived log for thread 2 with sequence 331 is already on disk as file +FRA/ORCLDB/onlinelog/group_9.15809.801330803
archived log for thread 3 with sequence 298 is already on disk as file +FRA/ORCLDB/onlinelog/group_6.15604.801247055
archived log for thread 3 with sequence 299 is already on disk as file +FRA/ORCLDB/onlinelog/group_11.572.801331045
archived log for thread 3 with sequence 300 is already on disk as file +FRA/ORCLDB/onlinelog/group_12.7522.801331055
archived log for thread 3 with sequence 301 is already on disk as file +FRA/ORCLDB/onlinelog/group_5.15608.801247053
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=287
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=340
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=315
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_10/annnf0_backup_ORCLDB_di_0410130300_0.9209.812386865
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_10/annnf0_backup_ORCLDB_di_0410130300_0.9209.812386865 tag=BACKUP_ORCLDB_DI_041013030001
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_315.19551.812992121 thread=2 sequence=315
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_340.13806.812992121 thread=1 sequence=340
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_287.5203.812992123 thread=3 sequence=287
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_315.19551.812992121 RECID=952 STAMP=812992122
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=316
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=288
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=341
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=289
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=342
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=317
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_11/annnf0_backup_ORCLDB_di_0411130216_0.23705.812470583
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_11/annnf0_backup_ORCLDB_di_0411130216_0.23705.812470583 tag=BACKUP_ORCLDB_DI_041113021603
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_316.1665.812992123 thread=2 sequence=316
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_287.5203.812992123 RECID=954 STAMP=812992122
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_288.11356.812992123 thread=3 sequence=288
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_340.13806.812992121 RECID=953 STAMP=812992122
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_341.17196.812992123 thread=1 sequence=341
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_288.11356.812992123 RECID=957 STAMP=812992128
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_289.17321.812992123 thread=3 sequence=289
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_341.17196.812992123 RECID=959 STAMP=812992130
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_342.19551.812992123 thread=1 sequence=342
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_316.1665.812992123 RECID=958 STAMP=812992128
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_317.13977.812992123 thread=2 sequence=317
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_317.13977.812992123 RECID=956 STAMP=812992127
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=318
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=343
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=290
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=344
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=319
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=345
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=346
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=320
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=291
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_12/annnf0_backup_ORCLDB_di_0412130216_0.4151.812557003
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_12/annnf0_backup_ORCLDB_di_0412130216_0.4151.812557003 tag=BACKUP_ORCLDB_DI_041213021604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_318.13806.812992145 thread=2 sequence=318
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_342.19551.812992123 RECID=960 STAMP=812992131
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_343.5203.812992145 thread=1 sequence=343
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_289.17321.812992123 RECID=955 STAMP=812992127
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_290.1665.812992145 thread=3 sequence=290
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_343.5203.812992145 RECID=964 STAMP=812992148
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_344.13671.812992147 thread=1 sequence=344
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_319.21419.812992145 thread=2 sequence=319
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_345.18453.812992145 thread=1 sequence=345
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_344.13671.812992147 RECID=962 STAMP=812992147
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_345.18453.812992145 RECID=961 STAMP=812992146
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_318.13806.812992145 RECID=965 STAMP=812992148
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_346.13977.812992143 thread=1 sequence=346
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_319.21419.812992145 RECID=963 STAMP=812992147
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_320.17196.812992145 thread=2 sequence=320
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_290.1665.812992145 RECID=968 STAMP=812992149
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_291.11356.812992145 thread=3 sequence=291
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_346.13977.812992143 RECID=969 STAMP=812992150
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=347
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=292
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=321
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=322
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=348
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=293
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=323
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=324
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=349
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=350
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=294
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=325
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_14/annnf0_backup_ORCLDB_di_0414130216_0.8798.812729787
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_14/annnf0_backup_ORCLDB_di_0414130216_0.8798.812729787 tag=BACKUP_ORCLDB_DI_041413021601
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_347.21419.812992157 thread=1 sequence=347
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_291.11356.812992145 RECID=966 STAMP=812992148
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_292.17321.812992157 thread=3 sequence=292
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_320.17196.812992145 RECID=967 STAMP=812992149
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_321.13671.812992157 thread=2 sequence=321
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_321.13671.812992157 RECID=972 STAMP=812992163
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_322.14387.812992163 thread=2 sequence=322
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_347.21419.812992157 RECID=979 STAMP=812992170
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_348.13977.812992155 thread=1 sequence=348
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_292.17321.812992157 RECID=971 STAMP=812992163
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_293.18453.812992157 thread=3 sequence=293
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_322.14387.812992163 RECID=975 STAMP=812992169
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_323.14434.812992167 thread=2 sequence=323
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_324.3028.812992163 thread=2 sequence=324
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_348.13977.812992155 RECID=981 STAMP=812992173
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_323.14434.812992167 RECID=974 STAMP=812992167
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_349.5203.812992157 thread=1 sequence=349
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_349.5203.812992157 RECID=970 STAMP=812992162
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_350.1665.812992157 thread=1 sequence=350
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_293.18453.812992157 RECID=973 STAMP=812992166
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_294.19551.812992163 thread=3 sequence=294
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_324.3028.812992163 RECID=977 STAMP=812992169
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_325.13806.812992157 thread=2 sequence=325
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_325.13806.812992157 RECID=976 STAMP=812992169
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=326
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=295
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=351
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=327
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=296
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=352
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=353
channel ORA_DISK_1: restoring archived log
archived log thread=3 sequence=297
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=354
channel ORA_DISK_1: reading from backup piece +FRA/ORCLDB/backupset/2013_04_15/annnf0_backup_ORCLDB_di_0415130216_0.9957.812816187
channel ORA_DISK_1: piece handle=+FRA/ORCLDB/backupset/2013_04_15/annnf0_backup_ORCLDB_di_0415130216_0.9957.812816187 tag=BACKUP_ORCLDB_DI_041513021602
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_326.13806.812992195 thread=2 sequence=326
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_294.19551.812992163 RECID=978 STAMP=812992170
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_295.3028.812992195 thread=3 sequence=295
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_350.1665.812992157 RECID=980 STAMP=812992172
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_351.18453.812992195 thread=1 sequence=351
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_326.13806.812992195 RECID=990 STAMP=812992199
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_327.14434.812992195 thread=2 sequence=327
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_295.3028.812992195 RECID=989 STAMP=812992198
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_296.13977.812992195 thread=3 sequence=296
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_351.18453.812992195 RECID=988 STAMP=812992198
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_352.14387.812992195 thread=1 sequence=352
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_352.14387.812992195 RECID=987 STAMP=812992196
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_353.17321.812992195 thread=1 sequence=353
archived log file name=+FRA/ORCLDB/onlinelog/group_10.7498.801331015 thread=2 sequence=328
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_353.17321.812992195 RECID=985 STAMP=812992196
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_2_seq_327.14434.812992195 RECID=982 STAMP=812992195
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_296.13977.812992195 RECID=983 STAMP=812992195
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_297.21419.812992197 thread=3 sequence=297
archived log file name=+FRA/ORCLDB/onlinelog/group_6.15604.801247055 thread=3 sequence=298
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_354.5203.812992195 thread=1 sequence=354
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_1_seq_354.5203.812992195 RECID=984 STAMP=812992195
channel default: deleting archived log(s)
archived log file name=+FRA/ORCLDB/archivelog/2013_04_17/thread_3_seq_297.21419.812992197 RECID=986 STAMP=812992196
archived log file name=+FRA/ORCLDB/onlinelog/group_7.15602.801247063 thread=1 sequence=355
archived log file name=+FRA/ORCLDB/onlinelog/group_3.15603.801247047 thread=2 sequence=329
archived log file name=+FRA/ORCLDB/onlinelog/group_11.572.801331045 thread=3 sequence=299
archived log file name=+FRA/ORCLDB/onlinelog/group_8.6650.801247065 thread=1 sequence=356
archived log file name=+FRA/ORCLDB/onlinelog/group_4.7512.801247051 thread=2 sequence=330
archived log file name=+FRA/ORCLDB/onlinelog/group_12.7522.801331055 thread=3 sequence=300
archived log file name=+FRA/ORCLDB/onlinelog/group_1.15605.801247059 thread=1 sequence=357
archived log file name=+FRA/ORCLDB/onlinelog/group_5.15608.801247053 thread=3 sequence=301
archived log file name=+FRA/ORCLDB/onlinelog/group_2.15467.801247061 thread=1 sequence=358
archived log file name=+FRA/ORCLDB/onlinelog/group_9.15809.801330803 thread=2 sequence=331
media recovery complete, elapsed time: 00:00:14
Finished recover at 17-APR-13

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN> exit

-- login database again and change the spfile location from local to ASM if required as below

[oracle@subhen-db02 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 17 15:11:57 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/database/11.2.0.3/
                                                 dbhome_1/dbs/spfileORCLDB_1.
                                                 ora
SQL> create pfile='/tmp/initORCLDB.ora' from spfile='/oracle/app/database/11.2.0.3/dbhome_1/dbs/spfileORCLDB_1.ora';

File created.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Manually Install XBRL to your XML Database (XDB)

--reference

--How to Manually Install and Uninstall XBRL to your XML Database (XDB) [ID 1297384.1]

-- the below has been done in a non-rac database , below this post the steps of RAC follows

-- check for opatch for any applied patch details

[oracle@subhendb-01 logs]$ cd /oracle/app/database/11.2.0.3/dbhome_2/OPatch/

[oracle@subhendb-01 OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_14-35-59PM.log

Lsinventory Output file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_14-35-59PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

-- scp the downloaded XBRL patch to database server [p13347017 for XBRL unzip the patch to the designated folder it would create a folder named XBRLRelease112022]

[oracle@subhendb-01 XBRL]$ ls -ltr
total 8
drwxr-xr-x 2 oracle oinstall 4096 Mar 15 16:24 RDBMS_patch
drwxr-xr-x 2 oracle oinstall 4096 Mar 15 16:24 XBRL_patch
[oracle@subhendb-01 XBRL]$ cd XBRL_patch
[oracle@subhendb-01 XBRL_patch]$ ls -ltr
total 2672
-rw-r--r-- 1 oracle oinstall 2730588 Mar 15 16:06 p13347017_112030_Generic.zip
[oracle@subhendb-01 XBRL_patch]$ unzip -d /oracle/app/PATCH/XBRL/XBRL_patch p13347017_112030_Generic.zip

[oracle@subhendb-01 XBRL_patch]$ ls -ltr
total 2676
drwxr-xr-x 5 oracle oinstall    4096 Feb 11  2012 XBRLRelease112022
-rw-r--r-- 1 oracle oinstall 2730588 Mar 15 16:06 p13347017_112030_Generic.zip
[oracle@subhendb-01 XBRL_patch]$ cd XBRLRelease112022
[oracle@subhendb-01 XBRLRelease112022]$ ls -ltr
total 28
drwxr-xr-x 6 oracle oinstall  4096 Dec  9  2011 xbrl_xdb
-rwxr-xr-x 1 oracle oinstall 12440 Feb 11  2012 README.TXT
drwxr-xr-x 3 oracle oinstall  4096 Feb 11  2012 files
drwxr-xr-x 4 oracle oinstall  4096 Feb 11  2012 etc


-- download the downloaded RDBMs patch to database server [p11883969 for RDBMS and unzip it]

[oracle@subhendb-01 XBRL]$ cd RDBMS_patch/
[oracle@subhendb-01 RDBMS_patch]$ ls -ltr
total 604
-rw-r--r-- 1 oracle oinstall 612229 Mar 15 16:00 p11883969_112030_Linux-x86-64.zip
[oracle@subhendb-01 RDBMS_patch]$ pwd
/oracle/app/PATCH/XBRL/RDBMS_patch
[oracle@subhendb-01 RDBMS_patch]$ unzip -d /oracle/app/PATCH/XBRL/RDBMS_patch p11883969_112030_Linux-x86-64.zip

[oracle@subhendb-01 RDBMS_patch]$ ls -ltr
total 608
drwxrwxr-x 4 oracle oinstall   4096 Dec 17  2011 11883969
-rw-r--r-- 1 oracle oinstall 612229 Mar 15 16:00 p11883969_112030_Linux-x86-64.zip
[oracle@subhendb-01 RDBMS_patch]$ cd 11883969/
[oracle@subhendb-01 11883969]$ ls -ltr
total 16
-rw-rw-r-- 1 oracle oinstall 4433 Dec 17  2011 README.txt
drwxr-xr-x 3 oracle oinstall 4096 Dec 17  2011 files
drwxr-xr-x 4 oracle oinstall 4096 Dec 17  2011 etc

-- proceed for RDBMS patch apply stop all services(databases and listeners) runnning out of the ORACLE_HOME alternatively stop ORACLE_HOME

[oracle@subhendb-01 11883969]$ export PATH=$PATH:/oracle/app/database/11.2.0.3/dbhome_2/OPatch
[oracle@subhendb-01 11883969]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/oracle/app/database/11.2.0.3/dbhome_2/bin:/oracle/app/database/11.2.0.3/dbhome_2/OPatch

[oracle@subhendb-01 11883969]$ pwd
/oracle/app/PATCH/XBRL/RDBMS_patch/11883969
[oracle@subhendb-01 11883969]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-25-31PM.log

Applying interim patch '11883969' to OH '/oracle/app/database/11.2.0.3/dbhome_2'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/database/11.2.0.3/dbhome_2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 11883969 successfully applied
Log file location: /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-25-31PM.log

OPatch succeeded.


-- check if successfully applied

[oracle@subhendb-01 11883969]$ opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-27-09PM.log

Lsinventory Output file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_17-27-09PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (1) :

Patch  11883969     : applied on Fri Mar 15 17:26:34 EST 2013
Unique Patch ID:  14352013
   Created on 16 Dec 2011, 12:53:41 hrs PST8PDT
   Bugs fixed:
     11883969



--------------------------------------------------------------------------------

OPatch succeeded.


-- proceed with XBRL patch apply

[oracle@subhendb-01 ~]$ echo $PATH
/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/oracle/app/database/11.2.0.3/dbhome_2/bin

[oracle@subhendb-01 ~]$ export PATH=$PATH:/oracle/app/database/11.2.0.3/dbhome_2/OPatch/

-- go to the location of the XBRL patch set and apply it

[oracle@subhendb-01 XBRL_patch]$ cd XBRLRelease112022/
[oracle@subhendb-01 XBRLRelease112022]$ ls -ltr
total 44
drwxrwxr-x+ 6 oracle oinstall  4096 Dec  9  2011 xbrl_xdb
-rwxrwxr-x+ 1 oracle oinstall 12440 Feb 11  2012 README.TXT
drwxrwxr-x+ 3 oracle oinstall  4096 Feb 11  2012 files
drwxrwxr-x+ 4 oracle oinstall  4096 Feb 11  2012 etc
[oracle@subhendb-01 XBRLRelease112022]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-47-50PM.log

Applying interim patch '13347017' to OH '/oracle/app/database/11.2.0.3/dbhome_2'
Verifying environment and performing prerequisite checks...
Patch 13347017: Optional component(s) missing : [ oracle.rdbms, 11.2.0.2.0, higher version 11.2.0.3.0 found. ]

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...
Patch 13347017 successfully applied
Log file location: /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-47-50PM.log

OPatch succeeded.

-- check it for successful apply

[oracle@subhendb-01 XBRLRelease112022]$ opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_17-49-30PM.log

Lsinventory Output file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_17-49-30PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  13347017     : applied on Fri Mar 15 17:48:06 EST 2013
Unique Patch ID:  14615728
   Created on 10 Feb 2012, 13:10:13 hrs PST8PDT
   Bugs fixed:
     12388668, 13347017, 10411201

Patch  11883969     : applied on Fri Mar 15 17:26:34 EST 2013
Unique Patch ID:  14352013
   Created on 16 Dec 2011, 12:53:41 hrs PST8PDT
   Bugs fixed:
     11883969



--------------------------------------------------------------------------------

OPatch succeeded.

-- proceed for runnning scripts for XBRL and start the services for the ORACLE_HOME


[oracle@subhendb-01 XBRLRelease112022]$ cd /oracle/app/database/11.2.0.3/dbhome_2/rdbms/xbrl_xdb/XBRLScripts/
[oracle@subhendb-01 XBRLScripts]$ ls -ltr
total 1240
-rw-rwxr--+ 1 oracle oinstall   2093 Feb 11  2012 xbrlurep112021.sql
-rwxrwxr-x+ 1 oracle oinstall   3188 Feb 11  2012 xbrluninstall.sql
-rwxrwxr-x+ 1 oracle oinstall   2031 Feb 11  2012 xbrludpurge.sql
-rw-rwxr--+ 1 oracle oinstall   8664 Feb 11  2012 xbrluddl112021.sql
-rw-rwxr--+ 1 oracle oinstall   4069 Feb 11  2012 xbrlu112021.sql
-rwxrwxr-x+ 1 oracle oinstall  30127 Feb 11  2012 xbrltiview.sql
-rwxrwxr-x+ 1 oracle oinstall   4098 Feb 11  2012 xbrlregschema.sql
-r--rwxr--+ 1 oracle oinstall   1331 Feb 11  2012 xbrlrecidx.sql
-rw-rwxr-x+ 1 oracle oinstall   2569 Feb 11  2012 xbrlrecidxdrv.sql
-rwxrwxr-x+ 1 oracle oinstall   3105 Feb 11  2012 xbrlpurgefile.sql
-rwxrwxr-x+ 1 oracle oinstall   3364 Feb 11  2012 xbrlloaderrmsg.sql
-rwxrwxr-x+ 1 oracle oinstall   7299 Feb 11  2012 xbrlinstall.sql
-rwxrwxr-x+ 1 oracle oinstall   7875 Feb 11  2012 xbrleventhdl.plb
-rwxrwxr-x+ 1 oracle oinstall    922 Feb 11  2012 xbrlerrmsg.sql
-rwxrwxr-x+ 1 oracle oinstall   3802 Feb 11  2012 xbrldrop.sql
-rwxrwxr-x+ 1 oracle oinstall  10287 Feb 11  2012 xbrlddl.sql
-r--rwxr--+ 1 oracle oinstall  28738 Feb 11  2012 xbrlddlindex.sql
-rwxrwxr-x+ 1 oracle oinstall   3950 Feb 11  2012 xbrlcrt.sql
-rwxrwxr-x+ 1 oracle oinstall 922045 Feb 11  2012 xbrlcompins.sql
-rwxrwxr-x+ 1 oracle oinstall  47878 Feb 11  2012 xbrlapi.plb
-rwxrwxr-x+ 1 oracle oinstall  11746 Feb 11  2012 xbrlapi0.plb
-rwxrwxr-x+ 1 oracle oinstall  10934 Feb 11  2012 License.txt
-rwxrwxr-x+ 1 oracle oinstall   2769 Feb 11  2012 InstallXBRLDemo.sql
drwxrwxr-x+ 2 oracle oinstall   4096 Mar 15 17:48 mesg

-- login to database and execute below

SQL> @xbrlinstall.sql Oracle321 xbrlsys sysaux temp true

-- verification

SQL> SELECT OBJECT_NAME FROM USER_OBJECTS
WHERE STATUS = 'VALID' AND OBJECT_TYPE = 'PACKAGE';  2

OBJECT_NAME
--------------------------------------------------------------------------------
DBMS_ORAXBRL_INTERNAL
DBMS_ORAXBRLV
DBMS_ORAXBRLD
DBMS_ORAXBRL_UBM
DBMS_ORAXBRLI
DBMS_ORAXBRLT
DBMS_ORAXBRL

7 rows selected.

SQL> SELECT ANY_PATH FROM RESOURCE_VIEW WHERE ANY_PATH = '/xbrl';

ANY_PATH
--------------------------------------------------------------------------------
/xbrl

1 row selected.

SQL> SELECT INDEX_NAME FROM USER_INDEXES
WHERE INDEX_TYPE = 'FUNCTION-BASED DOMAIN' AND STATUS = 'VALID';  2

INDEX_NAME
------------------------------
XBRL$SCHEMAIDX
XBRL$INSTANCEIDX
ORA$XBRLCACHEIDX

3 rows selected.

SQL> select object_name, object_type from user_objects
where status!='VALID';  2

no rows selected


===========================
FOR RAC database
===========================

-- Stop all services running out of the ORACLE_HOME

[oracle@subhen_db01 ~]$ srvctl stop home -o /oracle/app/database/11.2.0.3/dbhome_1 -s /home/oracle/state_file_1 -n subhen_db01 -t IMMEDIATE

-- RDBMS PATCH apply

[oracle@subhen_db01 ~]$ cd /home/oracle/PATCH/XBRL/RDBMS_patch/11883969
[oracle@subhen_db01 11883969]$ ls -ltr
total 16
-rw-rw-r-- 1 oracle oinstall 4433 Dec 17  2011 README.txt
drwxr-xr-x 3 oracle oinstall 4096 Dec 17  2011 files
drwxr-xr-x 4 oracle oinstall 4096 Dec 17  2011 etc
[oracle@subhen_db01 11883969]$ which opatch
/oracle/app/database/11.2.0.3/dbhome_1/OPatch/opatch
[oracle@subhen_db01 11883969]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.2
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_1
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/database/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.2
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/11883969_Mar_27_2013_17_55_56/apply2013-03-27_17-55-56PM_1.log

Applying interim patch '11883969' to OH '/oracle/app/database/11.2.0.3/dbhome_1'
Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following executables are active :
/oracle/app/database/11.2.0.3/dbhome_1/bin/oracle
Log file location: /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/11883969_Mar_27_2013_17_55_56/apply2013-03-27_17-55-56PM_1.log

Recommended actions: OPatch needs to modify files which are being used by some processes.

OPatch failed with error code 41


-- here the problem was in a different window i had logged in the same Database host so i closed the putty window and tried opatch again and it was success
-- close all the window except the one you are trying for opatch


-- here the opatch is rolling fashion so after 1st node patch apply done start the ORACLE_HOME in 1st node and stop the ORACLE_HOME in 2nd node continue the same for other remaining nodes also

[oracle@subhen_db01 11883969]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.2
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_1
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/database/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.2
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/11883969_Mar_27_2013_18_03_40/apply2013-03-27_18-03-40PM_1.log

Applying interim patch '11883969' to OH '/oracle/app/database/11.2.0.3/dbhome_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y



This node is part of an Oracle Real Application Cluster.
Remote nodes: 'subhen_db02' 'subhen_db03'
Local node: 'subhen_db01'
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/database/11.2.0.3/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...

Verifying the update...

The local system has been patched.  You can restart Oracle instances on it.


Patching in rolling mode.

Remaining nodes to be patched:
'subhen_db02' 'subhen_db03'
What is the next node to be patched?
subhen_db02
You have selected 'subhen_db02' from 'subhen_db02' 'subhen_db03'

The node 'subhen_db02' will be patched next.


Please shutdown Oracle instances running out of this ORACLE_HOME on 'subhen_db02'.
(Oracle Home = '/oracle/app/database/11.2.0.3/dbhome_1')

Is the node ready for patching? [y|n]
y
User Responded with: Y
Updating nodes 'subhen_db02'
   Apply-related files are:
     FP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_files.txt"
     DP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_dirs.txt"
     MP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt"
     RC = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/remote_cmds.txt"

Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt" with actual path.
Running command on remote node 'subhen_db02':
cd /oracle/app/database/11.2.0.3/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2


The node 'subhen_db02' has been patched.  You can restart Oracle instances on it.


The node 'subhen_db03' will be patched next.


Please shutdown Oracle instances running out of this ORACLE_HOME on 'subhen_db03'.
(Oracle Home = '/oracle/app/database/11.2.0.3/dbhome_1')

Is the node ready for patching? [y|n]
y
User Responded with: Y
Updating nodes 'subhen_db03'
   Apply-related files are:
     FP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_files.txt"
     DP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/copy_dirs.txt"
     MP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt"
     RC = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/remote_cmds.txt"

Propagating files to remote nodes...
Propagating directories to remote nodes...
Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/11883969_Dec_16_2011_12_53_41/rac/make_cmds.txt" with actual path.
Running command on remote node 'subhen_db03':
cd /oracle/app/database/11.2.0.3/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1 || echo REMOTE_MAKE_FAILED::>&2


The node 'subhen_db03' has been patched.  You can restart Oracle instances on it.

There were relinks on remote nodes.  Remember to check the binary size and timestamp on the nodes 'subhen_db02' 'subhen_db03' .
The following make commands were invoked on remote nodes:
'cd /oracle/app/database/11.2.0.3/dbhome_1/rdbms/lib; /usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1
'

Patch 11883969 successfully applied
Log file location: /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/11883969_Mar_27_2013_18_03_40/apply2013-03-27_18-03-40PM_1.log

OPatch succeeded.
[oracle@subhen_db01 11883969]$ sleep 2d

-- XBRL patch apply

[oracle@subhen_db01 XBRLRelease112022]$ pwd
/home/oracle/PATCH/XBRL/XBRL_patch/XBRLRelease112022
[oracle@subhen_db01 XBRLRelease112022]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.2
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_1
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/database/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.2
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/13347017_Mar_27_2013_19_07_55/apply2013-03-27_19-07-55PM_1.log

Applying interim patch '13347017' to OH '/oracle/app/database/11.2.0.3/dbhome_1'
Verifying environment and performing prerequisite checks...
Patch 13347017: Optional component(s) missing : [ oracle.rdbms, 11.2.0.2.0, higher version 11.2.0.3.0 found. ]
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y


Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...

Verifying the update...

Patching in all-node mode.

Updating nodes 'subhen_db02' 'subhen_db03'
   Apply-related files are:
     FP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_files.txt"
     DP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_dirs.txt"
     MP = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/make_cmds.txt"
     RC = "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/remote_cmds.txt"

Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_files.txt.instantiated" by replacing $ORCLE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_files.txt" with actual path.
Propagating files to remote nodes...
Instantiating the file "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_dirs.txt.instantiated" by replacing $ORALE_HOME in "/oracle/app/database/11.2.0.3/dbhome_1/.patch_storage/13347017_Feb_10_2012_13_10_13/rac/copy_dirs.txt" with actual path.
Propagating directories to remote nodes...
Patch 13347017 successfully applied
Log file location: /oracle/app/database/11.2.0.3/dbhome_1/cfgtoollogs/opatch/13347017_Mar_27_2013_19_07_55/apply2013-03-27_19-07-55PM_1.log

OPatch succeeded.

Opatch: LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo : OPatch failed with error code 73

{i was trying to opatch lsinventory but it was throwing error as below , i had tried to detach the ORACLE_HOME and attache it again but still i failed
so at last i had removed the oaInventory directory from /oracle/app (after taking a backup of the folder) and did the attach home activity again was successful and opatch lsinventory was also giving correct output}

-- the below was done in non-RAC database env.
-- opatch was throwing the below error

[oracle@subhen-db OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_11-22-00AM.log

List of Homes on this system:

  Home name= OraDb11g_home1, Location= "/oracle/app/database/11.2.0.3/dbhome_2/OPatch"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

-- we were also not able to detach the ORACLE_HOME also

[oracle@subhen-db bin]$ ./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/oracle/app/database/11.2.0.3/dbhome_2" ORACLE_HOE_NAME="OraDb11g_home1" "CLUSTER_NODES={}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1278 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/app/oraInventory
OUI-10198:Unable to create a new Oracle Home with name OraDb11g_home1. An Oracle Home with this name already exists. Please select another name.
'AttachHome' failed.

[oracle@subhen-db bin]$ ./runInstaller -silent -detachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/oracle/app/database/11.2.0.3/dbhome_2" ORACLE_HOE_NAME="OraDb11g_home1" "CLUSTER_NODES={}"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1278 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/app/oraInventory
'DetachHome' failed.

-- remove/move oraInventory directory in order to recreate it from fresh

[oracle@subhen-db app]$ pwd
/oracle/app
[oracle@subhen-db app]$ mv oraInventory oraInventory.old

[oracle@subhen-db bin]$ ./runInstaller -silent -attachHome ORACLE_HOME="/oracle/app/database/11.2.0.3/dbhome_2" ORACLE_HOME_NAME="OraDb11g_home2"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1278 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/app/oraInventory
'AttachHome' was successful.

[oracle@subhen-db OPatch]$ ./opatch lsinventory
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/database/11.2.0.3/dbhome_2
Central Inventory : /oracle/app/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/opatch2013-03-15_14-35-59PM.log

Lsinventory Output file location : /oracle/app/database/11.2.0.3/dbhome_2/cfgtoollogs/opatch/lsinv/lsinventory2013-03-15_14-35-59PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

TDE(transparent data encryption) in ORACLE database

TDE implementation in databases as below

-- pre checks in database for TDE ( should show closed)

select * from GV$ENCRYPTION_WALLET

  INST_ID WRL_TYPE             WRL_PARAMETER                                                          STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
         2 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         3 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         4 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         1 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED


select * from V$ENCRYPTION_WALLET

WRL_TYPE             WRL_PARAMETER                                                          STATUS
-------------------- ---------------------------------------------------------------------- ------------------
file                 /oracle/app/admin/ORCL/wallet                                      CLOSED

-- Create Directories in all respective node servers

mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet

ssh subhen-db03 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"
ssh subhen-db04 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"
ssh subhen-db05 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"

mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
ssh subhen-db03 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"
ssh subhen-db04 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"
ssh subhen-db05 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"

-- Create wallet in 1st node server

[oracle@subhen-db01 bin]$ which orapki
/oracle/app/database/11.2.0.3/dbhome_1/bin/orapki
[oracle@subhen-db01 bin]$ orapki wallet create -wallet /oracle/app/admin/ORCL/wallet -pwd 'XXXXXX' -auto_login
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.


-- Restart database
srvctl stop database -d ORCL
srvctl start database -d ORCL

-- Set Master Key from sqlplus

[oracle@subhen-db01 bin]$ orapki wallet display -wallet /oracle/app/admin/ORCL/wallet -pwd 'XXXXXX'
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "XXXXXX";

System altered.

-- Shutdown database
srvctl stop database -d ORCL


-- Copy wallets into all directories of all servers..

cd /oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db03:/oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db04:/oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db05:/oracle/app/admin/ORCL/wallet

cp -p * /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet/
scp -p * oracle@subhen-db03:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
scp -p * oracle@subhen-db04:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
scp -p * oracle@subhen-db05:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet

-- Startup database
srvctl start database -d ORCL


-- Check the status


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                                          STATUS
-------------------- ---------------------------------------------------------------------- ------------------
file                 /oracle/app/admin/ORCL/wallet                                      OPEN

SQL> select * from gv$encryption_wallet;

   INST_ID WRL_TYPE             WRL_PARAMETER                                                          STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
         1 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         3 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         2 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         4 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN


-- TDE implementation finished

-- creating a tablespace as encrypted and assigning a new user this encrypted tablespace

SQL> CREATE TABLESPACE subu_ENCRYPTED  DATAFILE '+DATA' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE UNLIMITED
  2    3  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
  5  ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> create user subu_XBRL identified by subu#123
default tablespace subu_ENCRYPTED
temporary tablespace TEMP
quota UNLIMITED on subu_ENCRYPTED
profile  APP_PROFILE;

ORA-16792: configurable property value is inconsistent with database setting

[oracle@subhen_PRODdb04 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL>  connect /
Connected.
DGMGRL> show configuration

Configuration - ORCLdg

  Protection Mode: MaxPerformance
  Databases:
    ORCL - Primary database
    ORCLB - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL> show instance verbose 'ORCLB2'

Instance 'ORCLB2' of database 'ORCLB'

  Host Name: orempr2db08.esp.aur.national.com.au
  PFILE:
  Properties:
    SidName                         = 'ORCLB2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XX.XX.XX.47)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=ORCLB_DGMGRL)(INSTANCE_NAME=ORCLB2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

  Instance Warning(s):
    ORA-16714: the value of property StandbyArchiveLocation is inconsistent with the database setting
    ORA-16714: the value of property AlternateLocation is inconsistent with the database setting

Instance Status:
WARNING

DGMGRL> edit instance 'ORCLB2' set property StandbyArchiveLocation='+BACKUP';
Error: ORA-16664: unable to receive the result from a database

Failed.



DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL


-- set dg_broker_start=FALSE in both database(primary and standby) and also remove the broker files from ASM location

SQL> alter system set dg_broker_start=FALSE scope=both sid='*';

-- set dg_broker_start=TRUE and proceed for reconfiguring dgmgrl again

alter system set dg_broker_start=TRUE scope=both sid='*';

DGMGRL> create configuration ORCLDG as primary database is 'ORCL' connect identifier is 'ORCL';
Configuration "ORCLdg" created with primary database "ORCL"
DGMGRL> add database 'ORCLB' as connect identifier is 'ORCLB' maintained as physical;
Database "ORCLB" added
DGMGRL> show configuration

Configuration - ORCLdg

  Protection Mode: MaxPerformance
  Databases:
    ORCL - Primary database
    ORCLB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration


Configuration - ORCLdg

  Protection Mode: MaxPerformance
  Databases:
    ORCL - Primary database
    ORCLB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


--- but i had a little proble from 2nd host of standby site

DGMGRL> connect /
Connected.
DGMGRL> show configuration


Configuration - ORCLdg

  Protection Mode: MaxPerformance
  Databases:
    ORCL - Primary database
    ORCLB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-01017: invalid username/password; logon denied
ORA-16625: cannot reach database "ORCL"
DGM-17017: unable to determine configuration status


-- so i had copied the password file from other standby host and replaced(renamed) it and it works fine

[INS-00001] unknown irrecoverable error during database creation

The below error was found during database creation:
[INS-00001] unknown irrecoverable error

error description: I was trying to install the database from a remote ORACLE software source to local VM.
as the network was very slow so it was throwing some internal error as above

Solution: copy the software to local locatio and try to install from there.

recovering standby database from incremental SCN from primary

One of my standby database was out of sync for 15 days and we didn't have our archivelogs to recover it, so i had followed the below steps to recover it by taking a incremental backup from primary

Step1

=======

Find the difference in SCN from primary Database and standby database.

---------

primary

---------

SQL> select NAME,CURRENT_SCN from v$database;

NAME      CURRENT_SCN

--------- -----------

ORCL     15819959

----------

standby

----------

SQL> select NAME,CURRENT_SCN from v$database;

NAME      CURRENT_SCN

--------- -----------

ORCL     11281979

step2

======

Take a incremental backup from primary database form the required SCN number as below , move and catalog the backups at standby database

-----------------------------

Incremental backup at primary

------------------------------

[oracle@subhen_prim07 backup_ORCL]$ pwd

/oracle/app/backup_ORCL

[oracle@subhen_prim07 backup_ORCL]$ echo $ORACLE_SID

ORCL1

[oracle@subhen_prim07 backup_ORCL]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:30:29 2013

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

connected to target database: ORCL (DBID=1213379060)

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 11281979 DATABASE FORMAT '/oracle/app/backup_ORCL/bkupsby_%U';

Starting backup at 11-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=6 instance=ORCL1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=71 instance=ORCL1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=137 instance=ORCL1 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=203 instance=ORCL1 device type=DISK

backup will be obsolete on date 18-JAN-13

archived logs will not be kept or backed up

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00017 name=+DATA/ORCL/datafile/wcccontts.1446.799932363

input datafile file number=00005 name=+DATA/ORCL/datafile/undotbs2.1388.799870311

input datafile file number=00004 name=+DATA/ORCL/datafile/users.1381.799870137

input datafile file number=00010 name=+DATA/ORCL/datafile/nwcont_ias_discuss.1439.799930727

channel ORA_DISK_1: starting piece 1 at 11-JAN-13

channel ORA_DISK_2: starting compressed full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00016 name=+DATA/ORCL/datafile/wcpcontts.1445.799932339

input datafile file number=00003 name=+DATA/ORCL/datafile/undotbs1.1380.799870137

input datafile file number=00009 name=+DATA/ORCL/datafile/nwcont_ias_portlet.1438.799930725

channel ORA_DISK_2: starting piece 1 at 11-JAN-13

channel ORA_DISK_3: starting compressed full datafile backup set

channel ORA_DISK_3: specifying datafile(s) in backup set

input datafile file number=00002 name=+DATA/ORCL/datafile/sysaux.1379.799870137

input datafile file number=00014 name=+DATA/ORCL/datafile/nwcont_urmserver.1443.799930741

input datafile file number=00015 name=+DATA/ORCL/datafile/nwcont_ipm.1444.799930747

input datafile file number=00011 name=+DATA/ORCL/datafile/nwcont_ocssearch.1440.799930729

input datafile file number=00008 name=+DATA/ORCL/datafile/nwcont_orairm.1437.799930725

channel ORA_DISK_3: starting piece 1 at 11-JAN-13

channel ORA_DISK_4: starting compressed full datafile backup set

channel ORA_DISK_4: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/ORCL/datafile/system.1378.799870137

input datafile file number=00012 name=+DATA/ORCL/datafile/nwcont_ias_activity.1441.799930729

input datafile file number=00013 name=+DATA/ORCL/datafile/nwcont_ocs.1442.799930735

input datafile file number=00007 name=+DATA/ORCL/datafile/nwcont_ias_webcenter.1436.799930721

input datafile file number=00006 name=+DATA/ORCL/datafile/nwcont_mds.1435.799930719

channel ORA_DISK_4: starting piece 1 at 11-JAN-13

channel ORA_DISK_1: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16

channel ORA_DISK_2: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:16

channel ORA_DISK_4: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_3: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:36

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

backup will be obsolete on date 18-JAN-13

archived logs will not be kept or backed up

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 11-JAN-13

channel ORA_DISK_1: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1 tag=TAG20130111T173046 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 11-JAN-13

--------------------------------------

catalog backup pieces at standby site

--------------------------------------

[oracle@subhen_sby07 backup_ORCL]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:46:10 2013

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

connected to target database: ORCL (DBID=1213379060, not open)

RMAN> catalog start with '/oracle/app/backup_ORCL/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /oracle/app/backup_ORCL/

List of Files Unknown to the Database

=====================================

File Name: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1

File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck

File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1

File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck

File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1

File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1

Step3

==========

Do the recover of the standby database

------------------------

recover standby database

------------------------

RMAN> recover database noredo;

Starting recover at 11-JAN-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=518 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=582 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=645 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=710 instance=ORCLSBY1 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: +DATA/ORCLSBY/datafile/system.1253.800020585

destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819

destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741

destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655

destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717

channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1

channel ORA_DISK_2: starting incremental datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621

destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835

destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793

destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681

destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767

channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1

channel ORA_DISK_3: starting incremental datafile backup set restore

channel ORA_DISK_3: specifying datafile(s) to restore from backup set

destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865

destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875

destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377

channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1

channel ORA_DISK_4: starting incremental datafile backup set restore

channel ORA_DISK_4: specifying datafile(s) to restore from backup set

destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883

destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873

destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849

destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481

channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/11/2013 17:48:00

ORA-19870: error while restoring backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1

ORA-19573: cannot obtain exclusive enqueue for datafile 1

--failed because the managed recovery was active for standby database, so cancel the managed recovery first and retry recovery

[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 17:49:41 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 4175568896 bytes

Fixed Size                  2234960 bytes

Variable Size            2432697776 bytes

Database Buffers         1728053248 bytes

Redo Buffers               12582912 bytes

Database mounted.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> exit

RMAN> recover database noredo;

Starting recover at 11-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=515 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=579 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=643 instance=ORCLSBY1 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=708 instance=ORCLSBY1 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: +DATA/ORCLSBY/datafile/system.1253.800020585

destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819

destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741

destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655

destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717

channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1

channel ORA_DISK_2: starting incremental datafile backup set restore

channel ORA_DISK_2: specifying datafile(s) to restore from backup set

destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621

destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835

destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793

destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681

destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767

channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1

channel ORA_DISK_3: starting incremental datafile backup set restore

channel ORA_DISK_3: specifying datafile(s) to restore from backup set

destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865

destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875

destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377

channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1

channel ORA_DISK_4: starting incremental datafile backup set restore

channel ORA_DISK_4: specifying datafile(s) to restore from backup set

destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883

destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873

destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849

destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481

channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1

channel ORA_DISK_1: piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_DISK_4: piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046

channel ORA_DISK_4: restored backup piece 1

channel ORA_DISK_4: restore complete, elapsed time: 00:00:07

channel ORA_DISK_3: piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046

channel ORA_DISK_3: restored backup piece 1

channel ORA_DISK_3: restore complete, elapsed time: 00:00:15

channel ORA_DISK_2: piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:00:55

Finished recover at 11-JAN-13

-- start the managed recovery in standby database again

[recover managed standby database cancel;]

[alter database recover managed standby database using current logfile disconnect from session;]

here in the alert log of standby we will find still it is looking for older archivelogs this is due to the control file is still the older one. so we will restore the standby controlfile from primary

-- backup standby control file from primary

RMAN> backup device type disk current controlfile for standby format '/oracle/app/backup_ORCL/forstandbyctrl.bck';

Starting backup at 11-JAN-13

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 11-JAN-13

channel ORA_DISK_1: finished piece 1 at 11-JAN-13

piece handle=/oracle/app/backup_ORCL/forstandbyctrl.bck tag=TAG20130111T173139 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 11-JAN-13

Starting Control File and SPFILE Autobackup at 11-JAN-13

piece handle=+BACKUP/ORCL/autobackup/2013_01_11/s_804447105.6477.804447105 comment=NONE

Finished Control File and SPFILE Autobackup at 11-JAN-13

-- move and catalog the controlfile in standby and restore the controlfile

RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/05/2013 14:49:30
RMAN-06496: must use the TO clause when the database is mounted or open

-- put the standby database in nomount mode and try again

[oracle@subhen_sby01 backup_ORCL]$ srvctl stop database -d ORCLB -o immediate
[oracle@subhen_sby01 backup_pptnwaut]$ echo $ORACLE_SID
ORCLB2
[oracle@subhen_sby01 backup_pptnwaut]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 14:53:21 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2232472 bytes
Variable Size             788533096 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17227776 bytes

-- restore again
RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';

Starting restore at 11-JAN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=899 instance=ORCLSBY1 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA/ORCLSBY/controlfile/current.1250.800020347

output file name=+FRA/ORCLSBY/controlfile/current.102570.800020347

Finished restore at 11-JAN-13

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

-- try the managed recovery again, here we might find the below error in alert log of standby due to datafile mismatch so use the alter database file rename command

[match the file id from primary database(v$datafile) and standby database (v$datafile)]

Managed Standby Recovery starting Real Time Apply

Fri Jan 11 18:06:38 2013

Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '+DATA/ORCL/datafile/system.1378.799870137'

ORA-17503: ksfdopn:2 Failed to open file +DATA/ORCL/datafile/system.1378.799870137

ORA-15012: ASM file '+DATA/ORCL/datafile/system.1378.799870137' does not exist

Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:

-- rename file at standby database

[before that make standby_file_management to manual after rename complete change the parameter again to auto]

SQL> alter system set standby_file_management=MANUAL scope=both sid='*';

SQL> alter database rename  file '+DATA/ORCL/datafile/sysaux.1379.799870137' to '+DATA/ORCLSBY/DATAFILE/SYSAUX.1254.800020621';

Database altered.

SQL> alter system set standby_file_management=AUTO scope=both sid='*';

-- shutdown and restart standby database in mount state and check for archive_log_dest parameters and enable managed recovery again

SQL> shu immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,

OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 18:29:28 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 4175568896 bytes

Fixed Size                  2234960 bytes

Variable Size            2449474992 bytes

Database Buffers         1711276032 bytes

Redo Buffers               12582912 bytes

Database mounted.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

-- in my case log_archive_dest_3 value was wrongly populated so i disabled that in standby database

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_3                   string      location="USE_DB_RECOVERY_FILE

                                                 _DEST",  valid_for=(STANDBY_LO

                                                 GFILE,STANDBY_ROLE)

SQL> alter system set log_archive_dest_3=' ' scope=both sid='*';

-- finally check the archivelogs from both primary and standby database





========================================
Dataguard broker configuration redo
========================================

After the standby databases back into sync i found that the DG broker configuaration is not working, so i had to redo the configuration these are the steps i followed

[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - ORCLdg

  Protection Mode: MaxAvailability
  Databases:
    ORCL  - Primary database
      Warning: ORA-16809: multiple warnings detected for the database

    ORCLSBY - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

-- disable configuration and remove it

DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration

-- login to both primary and standby Database disable broker and remove the broker files from ASM location of both servers

SQL> alter system set dg_broker_start=FALSE scope=both sid='*';

-- recreate the configuration

[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ORCL
Password:
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration ORCLDG as primary database is 'ORCL' connect identifier is 'ORCL';
Configuration "ORCLdg" created with primary database "ORCL"
DGMGRL> add database 'ORCLSBY' as connect identifier is 'ORCLSBY' maintained as physical;
Database "ORCLSBY" added
DGMGRL> show configuration

Configuration - ORCLdg

  Protection Mode: MaxAvailability
  Databases:
    ORCL  - Primary database
    ORCLSBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration


Configuration - ORCLdg

  Protection Mode: MaxAvailability
  Databases:
    ORCL  - Primary database
    ORCLSBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show database verbose 'ORCL';

Database - ORCL

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    ORCL1
    ORCL2

  Properties:
    DGConnectIdentifier             = 'ORCL'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/ORCLSBY, +DATA/ORCL'
    LogFileNameConvert              = '+FRA/ORCLSBY, +FRA/ORCL'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> show database verbose 'ORCLSBY';

Database - ORCLSBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    ORCLSBY1

  Properties:
    DGConnectIdentifier             = 'ORCLSBY'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '+DATA/ORCLSBY, +DATA/ORCL'
    LogFileNameConvert              = '+FRA/ORCLSBY, +FRA/ORCL'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'ORCLSBY1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.62.107)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=ORCLSBY_DGMGRL)(INSTANCE_NAME=ORCLSBY1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+BACKUP'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS