Start CRS even getting “ORA-15040: diskgroup is incomplete” on voting file/OCR diskgroup

Problem:

CRS was down on both nodes, during startup cluster encountered the following error when it was trying to mount diskgroup containing voting files and OCR:

WARNING: Disk Group VOTE containing configured OCR is not mounted
WARNING: Disk Group VOTE containing voting files is not mounted
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing from group number "1" 

The diskgroup, where OCR and voting files were located was not able to mount because one disk was missing. As a result CRS is down:

# crsctl status res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

We know that NORMAL redundancy diskgroup can tolerate one mirror problem at a time.

Solution:

1. Start HAS and check status of the local resoureces

# crsctl start has

# crsctl status res -t -init

---------------------------------------------------------------------------
Name          Target      State        Server      State details       
---------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------- 
ora.asm
       1       ONLINE      ONLINE       rac2         STABLE
 ora.cluster_interconnect.haip
       1        ONLINE     ONLINE       rac2         STABLE
 ora.crf
       1        OFFLINE    OFFLINE                   STABLE
 ora.crsd
       1        ONLINE      OFFLINE                  STABLE
 ora.cssd
       1        ONLINE      ONLINE       rac2        STABLE
 ora.cssdmonitor
       1        ONLINE      ONLINE       rac2        STABLE
 ora.ctssd
       1        ONLINE      ONLINE       rac2        OBSERVER,STABLE
 ora.diskmon
       1        OFFLINE      OFFLINE                 STABLE
 ora.drivers.acfs
       1        ONLINE      ONLINE       rac2        STABLE
 ora.evmd
       1        ONLINE      INTERMEDIATE rac2        STABLE
 ora.gipcd
       1        ONLINE      ONLINE       rac2        STABLE
 ora.gpnpd
       1        ONLINE      ONLINE       rac2        STABLE
 ora.mdnsd
       1        ONLINE      ONLINE       rac2        STABLE
 ora.storage
       1        ONLINE      OFFLINE      rac2        STABLE 

2. Connect to the ASM instance and mount diskgroup using force option.

ASM instance will be in nomount state, because diskgroup having voting files and OCR cannot be mounted.

Force option is mandatory, otherwise you will get the same ORA-15040 error.

# su - grid

$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 28 16:14:14 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter diskgroup VOTE mount force;
Diskgroup altered.

This operation sometimes takes ~6min to complete because of the following notification in alert_ASM?.log

"WARNING: Background operations delayed until 05/28/19 16:19:47 because ASM was not stopped cleanly and there could be disconnected client(s)"

The error message is self explanatory.

3. The diskgroup online operation on the 2nd step should trigger clusterware autostart, if not start it using the following command:

# crsctl start cluster

4. Check CRS status:

# crsctl status res -t 

---------------------------------------------------------------------------
Name           Target  State        Server       State details       
--------------------------------------------------------------------------- 
Local Resources

ora.ASMNET1LSNR_ASM.lsnr
                ONLINE  ONLINE       rac2        STABLE
ora.DATA.dg
                ONLINE  OFFLINE      rac2        STABLE
ora.FRA.dg
                ONLINE  OFFLINE      rac2        STABLE
ora.LISTENER.lsnr
                ONLINE  ONLINE       rac2        STABLE
ora.MGMT.dg
                ONLINE  OFFLINE      rac2        STABLE
ora.VOTE.dg
                ONLINE  ONLINE       rac2        STABLE
ora.chad
                ONLINE  OFFLINE      rac2        STABLE
ora.net1.network
                ONLINE  ONLINE       rac2        STABLE
ora.ons
                ONLINE  ONLINE       rac2        STABLE
ora.proxy_advm
                OFFLINE OFFLINE      rac2        STABLE
---------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------- 
ora.LISTENER_SCAN1.lsnr
       1        ONLINE  ONLINE       rac2        STABLE
 ora.LISTENER_SCAN2.lsnr
       1        ONLINE  ONLINE       rac2        STABLE
 ora.LISTENER_SCAN3.lsnr
       1        ONLINE  ONLINE       rac2        STABLE
 ora.MGMTLSNR
       1        OFFLINE OFFLINE                  STABLE
 ora.asm
       1        ONLINE  OFFLINE                  STABLE
       2        ONLINE  ONLINE       rac2        Started,STABLE
 ora.cvu
       1        ONLINE  ONLINE       rac2        STABLE
 ora.mgmtdb
       1        OFFLINE OFFLINE                  STABLE
 ora.qosmserver
       1        ONLINE  ONLINE       rac2        STABLE 
 ora.rac1.vip
       1        ONLINE  INTERMEDIATE rac2        FAILED OVER,STABLE
 ora.rac2.vip
       1        ONLINE  ONLINE       rac2        STABLE
 ora.scan1.vip
       1        ONLINE  ONLINE       rac2        STABLE
 ora.scan2.vip
       1        ONLINE  ONLINE       rac2        STABLE
 ora.scan3.vip
       1        ONLINE  ONLINE       rac2        STABLE

Recommendation:

Change corrupted disks as soon as possible and make it online.

Create RAC database using DBCA silent mode

Real World Scenario: 

Previously, we had a vacancy on Senior DBA position. Some of our candidates had >15 years of experience in database administration.

So for testing their knowlege we created lab. There were already installed grid and database softwares, shared disks were present and diskgroups were already created.

The first task was to create RAC database in silent mode using DBCA.  They had an option to use the internet during the exam. But unfortunatelly they have not managed to do that.

So I decided to write the simple version of the script:

dbca -silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName orcl  \
-sid orcl  \
-SysPassword MyPassword123 \
-SystemPassword MyPassword123 \
-emConfiguration NONE \
-redoLogFileSize 2048  \
-recoveryAreaDestination FRA \
-storageType ASM \
-asmSysPassword MyPassword123 \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL32UTF8 \
-automaticMemoryManagement true \
-totalMemory 2536  \
-databaseType MULTIPURPOSE \
-nodelist rac1,rac2

Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
85% complete
94% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log” for further details.

RAC: root.sh | CRS-2672: Attempting to start ‘ora.storage’ | ORA-01017: invalid username/password

I was configuring clusterware on node1 and got the following error:

CRS-2672: Attempting to start ‘ora.storage’ on ‘node1’
ORA-01017: invalid username/password; logon denied
CRS-5017: The resource action “ora.storage start” encountered the following error:
Storage agent start action aborted. For details refer to “(:CLSN00107:)” in “/u01/app/oracle/diag/crs/node1/crs/trace/ohasd_orarootagent_root.trc”.
CRS-2883: Resource ‘ora.storage’ failed during Clusterware stack start.
CRS-4406: Oracle High Availability Services synchronous start failed.
CRS-4000: Command Start failed, or completed with errors.
2016/09/27 05:41:01 CLSRSC-117: Failed to start Oracle Clusterware stack

Died at /u01/app/12.1.0.2/grid/crs/install/crsinstall.pm line 930.
The command ‘/u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/crs/install /u01/app/12.1.0.2/grid/crs/install/rootcrs.pl ‘ exe ution failed

 

/u01/app/oracle/diag/crs/node1/crs/trace/ohasd_orarootagent_root.trc file says:

2016-09-27 05:40:56.787330*:kgfn.c@6018: kgfnConnect2Int: sysasm=0 envflags=0x10 srvrflags=0x3 unam=NULL password is NULL pstr=_ocr
2016-09-27 05:40:56.787330*:kgfn.c@6194: kgfnConnect2Int: cstr=(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/u01/app/12.1.0.2/grid/bin/oracle)(ARGV0=oracle+ASM1_ocr)(ENVS=’ORACLE_HOME=/u01/app/12.1.0.2/grid,ORACLE_SID=+ASM1′)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))’)(PRIVS=(USER=root)(GROUP=root)))(enable=setuser))
2016-09-27 05:40:57.273302 : AGENT:2583111424: {0:9:3} {0:9:3} Created alert : (:CRSAGF00113:) : Aborting the command: start for resource: ora.storage 1 1

 

So why user root???

See, when I connect using root I got ORA-01017

[root@node1 ~]# . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@node1 ~]# sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 05:59:01 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

If I connect through Oracle it is OK:

su – oracle

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [LBTCI1] ? +ASM1

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 05:59:45 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>

 

Look the connection string again there is “PROGRAM=/u01/app/12.1.0.2/grid/bin/oracle”, so let’s check file permissions.

[oracle@node1 ~]$ ll /u01/app/12.1.0.2/grid/bin/oracle
-rwsr-s–x 1 root root 295054213 Sep 27 05:26 /u01/app/12.1.0.2/grid/bin/oracle

It must be oracle:oinstall  not root:root 

chown oracle:oinstall /u01/app/12.1.0.2/grid/bin/oracle
chmod 6751 /u01/app/12.1.0.2/grid/bin/oracle

 

deconfigure(rootcrs.pl -deconfig  -verbose) crs and reconfigure(run root.sh) it again.

 

Transaction recovery: lock conflict caught and ignored

ALERT.LOG:

..... Transaction recovery: lock conflict caught and ignored
.....

And also some incident files are being created in $ORACLE_BASE/diag/rdbms/dbname/instancename/incident folder.

In my case the error started after SUPPLEMENTAL LOGGING enabled in a RAC environment. After disabling it the messages have not disappeared, but incident files are no longer being created.

1. Dead Trasaction

SQL> select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn

xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks,

a.ktuxesta txstatus

from x$ktuxe a, undo$ b

where a.ktuxecfl like ‘%DEAD%’

and a.ktuxeusn = b.us#;

USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS TXSTATUS
_SYSSMU7_881277423$ 1 3 7 13 1829999 1 ACTIVE
_SYSSMU8_4204495590$ 1 3 8 32 3045564 1 ACTIVE
_SYSSMU10_1314081219$ 1 3 10 3 11844457 1 ACTIVE

Transaction id is  XID_USN.XID_SLOT.XID_SEQ

So in our case, for the first row it will be 7.13.1829999

2.  Read transaction table from undo header.

ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU7_881277423$’;

….

  TRN TBL::

 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt

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

   0x00    9    0x03  0x1bf45c  0x000b  0x0000.789de808  0x00c242eb  0x0000.000.00000000  0x00000001   0x00c242eb  1367258143

   0x01    9    0x00  0x1c031b  0x0014  0x0000.789e6018  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225

   0x02    9    0x00  0x1c147a  0x000e  0x0000.789e694b  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230

   0x03    9    0x00  0x1c06f9  0x0016  0x0000.789e601c  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225

   0x04    9    0x00  0x1c06c8  0x0009  0x0000.789e3566  0x00c242f9  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x05    9    0x00  0x1c1167  0x0015  0x0000.789e357f  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x06    9    0x00  0x1c2716  0x0017  0x0000.789e69e1  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230

   0x07    9    0x00  0x1c1045  0x000c  0x0000.789e1bdb  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367258170

   0x08    9    0x00  0x1c2614  0x0005  0x0000.789e357e  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x09    9    0x00  0x1bfa03  0x0021  0x0000.789e3574  0x00c242f9  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x0a    9    0x00  0x1bf712  0x001e  0x0000.789e3246  0x00c242f1  0x0000.000.00000000  0x00000001   0x00000000  1367258190

   0x0b    9    0x00  0x1c1e01  0x0007  0x0000.789e1bd9  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367258170

   0x0c    9    0x00  0x1c08e0  0x000a  0x0000.789e3244  0x00c242f1  0x0000.000.00000000  0x00000006   0x00000000  1367258190

   0x0d   10    0x90  0x1bec6f  0x0038  0x0000.789e783e  0x00c242fb  0x0000.000.00000000  0x00000001   0x00c242fb  0

   0x0e    9    0x00  0x1c068e  0x0010  0x0000.789e694d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230

   0x0f    9    0x00  0x1c151d  0x0012  0x0000.789e3578  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x10    9    0x00  0x1c26bc  0x0006  0x0000.789e69df  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258230

   0x11    9    0x00  0x1c16eb  0x0000  0x0000.789cbd77  0x00c242eb  0x0000.000.00000000  0x00000001   0x00000000  1367257923

   0x12    9    0x00  0x1c082a  0x001d  0x0000.789e357c  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x13    9    0x00  0x1c1459  0x001f  0x0000.789e7891  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238

   0x14    9    0x00  0x1c14b8  0x0003  0x0000.789e601a  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225

   0x15    9    0x00  0x1c0457  0x0020  0x0000.789e39d3  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258195

   0x16    9    0x00  0x1c1326  0x0002  0x0000.789e601d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225

   0x17    9    0x00  0x1c0db5  0x001c  0x0000.789e788a  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238

   0x18    9    0x00  0x1bffe4  0x001b  0x0000.789e400d  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258200

   0x19    9    0x00  0x1c16e3  0x0001  0x0000.789e5fd2  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258225

   0x1a    9    0x00  0x1bdbb2  0x0018  0x0000.789e400b  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258200

   0x1b    9    0x00  0x1c1141  0x0019  0x0000.789e453a  0x00c242fa  0x0000.000.00000000  0x00000001   0x00000000  1367258204

   0x1c    9    0x00  0x1bc9a0  0x0013  0x0000.789e788e  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238

   0x1d    9    0x00  0x1c02ef  0x0008  0x0000.789e357d  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

   0x1e    9    0x00  0x1c0b6e  0x0004  0x0000.789e3250  0x00c242f9  0x0000.000.00000000  0x00000009   0x00000000  1367258190

   0x1f    9    0x00  0x1c00ad  0xffff  0x0000.789e78a1  0x00c242fc  0x0000.000.00000000  0x00000001   0x00000000  1367258238

   0x20    9    0x00  0x1c166c  0x001a  0x0000.789e39dd  0x00c242fa  0x0000.000.00000000  0x00000002   0x00000000  1367258195

   0x21    9    0x00  0x1c160b  0x000f  0x0000.789e3576  0x00c242ec  0x0000.000.00000000  0x00000001   0x00000000  1367258192

  EXT TRN CTL::

  usn: 7

 
State# 10 means active transaction.

dba points to starting UNDO block address.

usn: Undo segment number

usn.index.wrap# gives transaction id.

 
An active transaction 0x0007.00d.001bec6f is available in slot 0x0d which has a dba of 0x00c242fb (12731131 in decimal)

3. Reading UNDO Block:

Identify fileID and blockID:

fileID:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12731131) from x$dual;

3

blockID:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12731131) from x$dual;

148219

Dumping block

alter system dump datafile 3 block 148219;


UNDO BLK: 
xid: 0x0007.00d.001bec6f  seq: 0x41f9 cnt: 0x6   irb: 0x5   icl: 0x0   flg: 0x0000

 
Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset

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

0x01 0x1f98     0x02 0x1f2c     0x03 0x1d7c     0x04 0x1d10     0x05 0x1ca0    
0x06 0x1bfc    
 
*—————————–

* Rec #0x1  slt: 0x0d  objn: 0(0x00000000)  objd: 0  tblspc: 0(0x00000000)

*       Layer:   5 (Transaction Undo)   opc: 7  rci 0x00  
Undo type:  Regular undo    Begin trans    Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000Ext idx: 0

flg2: 0

*—————————–

uba: 0x00c242fa.41f9.37 ctl max scn: 0x0000.789b7668 prv tx scn: 0x0000.789bb8d7

txn start scn: scn: 0x0000.789e783e logon user: 88

prev brb: 12731116 prev bcl: 0

 
*—————————–

* Rec #0x2  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1  rci 0x00  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000

*—————————–

KDO undo record:

KTB Redo

op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1

op: L  itl: xid:  0x0012.01c.00322281 uba: 0x0102c5f0.3fa9.0a

                      flg: C—    lkc:  0     scn: 0x0000.789ca3f4

KDO Op code: LKR row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x038180fc  hdba: 0x018d64e2

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 14 to: 0

 
*—————————–

* Rec #0x3  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x02  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000

*—————————–

KDO undo record:

KTB Redo

op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 1

op: C  uba: 0x00c242fb.41f9.02

KDO Op code: URP row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x038180fc  hdba: 0x018d64e2

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 14(0xe) flag: 0x2c lock: 1 ckix: 0

ncol: 9 nnew: 6 size: 0

col  1: [ 7]  78 71 04 1d 13 01 01

col  2: [ 2]  c1 13

col  3: [ 1]  80

col  4: [16]  10 e5 00 2e 10 d1 10 d0 10 d7 10 e3 10 db 10 d8

col  5: [174]

10 d0 10 ed 10 d0 10 e0 10 d8 10 e1 00 20 10 d0 00 2e 10 e0 00 2e 00 20 10

de 10 e0 10 dd 10 d9 10 e3 10 e0 10 d0 10 e2 10 e3 10 e0 10 d8 10 e1 00 20

10 e1 10 d0 10 d2 10 d0 10 db 10 dd 10 eb 10 d8 10 d4 10 d1 10 dd 00 20 10

dc 10 d0 10 ec 10 d8 10 da 10 d8 10 e1 00 20 10 e3 10 e4 10 e0 10 dd 10 e1

00 20 10 d2 10 d0 10 db 10 dd 10 db 10 eb 10 d8 10 d4 10 d1 10 d4 10 da 10

e1 00 20 10 d1 10 d0 10 e2 10 dd 10 dc 00 20 10 d2 10 d8 10 dd 10 e0 10 d2

10 d8 00 20 10 de 10 d4 10 e0 10 d0 10 dc 10 d8 10 eb 10 d4 10 e1 00 2e

col  6: [36]

00 54 00 01 04 0c 00 00 00 02 00 00 00 01 00 00 09 07 b0 63 00 10 09 00 00

00 00 00 00 00 00 00 00 00 00 00

 
*—————————–

* Rec #0x4  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x03  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000

*—————————–

KDO undo record:

KTB Redo

op: 0x04  ver: 0x01 
compat bit: 4 (post-11) padding: 1

op: L  itl: xid:  0x000c.017.000d65d6 uba: 0x0103df2c.22a5.20

                      flg: C—    lkc:  0     scn: 0x0000.789c4694

KDO Op code: LKR row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x03833994  hdba: 0x0181f832

itli: 1  ispac: 0  maxfr: 4858

tabn: 0 slot: 7 to: 0

 
*—————————–

* Rec #0x5  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1  rci 0x04  
Undo type:  Regular undo   Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000

*—————————–

KDO undo record:

KTB Redo

op: 0x02  ver: 0x01 
compat bit: 4 (post-11) padding: 1

op: C  uba: 0x00c242fb.41f9.04

KDO Op code: LMN row dependencies Disabled

  xtype: XA flags: 0x00000000  bdba: 0x03833994  hdba: 0x0181f832

itli: 1  ispac: 0  maxfr: 4858

 
*—————————–

* Rec #0x6  slt: 0x0d  objn: 89703(0x00015e67)  objd: 92020  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1  rci 0x05  
Undo type:  Regular undo    User Undo Applied  Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000

*—————————–

KDO undo record:

irb points to last UNDO RECORD in UNDO block.

rci points to previous UNDO RECORD. if rci=0, it’s the first UNDO RECORD.

Recovery operation starts from irb and chain is followed by rci until rci is zero.

The transaction starts recovery from UNDO RECORD of 0x5.

4. Reading UNDO Records:


* Rec #0x5  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x04  
….

* Rec #0x4  slt: 0x0d  objn: 89834(0x00015eea)  objd: 93214  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x03  
….

* Rec #0x3  slt: 0x0d objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x02  

* Rec #0x2  slt: 0x0d  objn: 110769(0x0001b0b1)  objd: 110769  tblspc: 6(0x00000006)

*       Layer:  11 (Row)   opc: 1   rci 0x00  

objn means object id.

5. Find these objects

The following objects need recovery:

select * from dba_objects

where object_id in (89834,110769);

………………………………………………………..

This problem is Oracle Bug:9857702:

.....
Affects:
Product (Component) Oracle Server (Rdbms)  
Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1  
Versions confirmed as being affected
•11.2.0.1 
•11.1.0.7 
 
Platforms affected Generic (all / most platforms affected)  

Fixed:
This issue is fixed in
•12.1 (Future Release) 
•11.2.0.2 (Server Patch Set) 
•11.1.0.7.8 Patch Set Update 
•11.1.0.7 Patch 40 on Windows Platforms  
.....

6. Workaround:

  • Recreate objects that need recovery.
  • Or drop them Smile

Recreate Oracle 11g OEM DBConsole manually for RAC

If you have problems with existing OEM, the best way is to reconfigure it. Here are the steps, how to do it correctly:

$ emca -config dbcontrol db -repos recreate -cluster

STARTED EMCA at Jan 22, 2013 6:04:10 PM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database unique name: orcl
Service name: orcl
Listener ORACLE_HOME [ /u01/app/11.2.0/grid ]:
Password for SYS user:
Database Control is already configured for the database orcl
You have chosen to configure Database Control for managing the database orcl
This will remove the existing configuration and the default settings and perform a fresh configuration
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: y
Password for DBSNMP user:
Password for SYSMAN user:
Cluster name: oracle-db

!!!Stop here for a while: if you don’t know your cluster name run the following command:

$ su – grid
cemutlo -n

…continuing configuration

Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /u01/app/11.2.0/grid ]:
ASM port [ 1521 ]:
ASM username [ ASMSNMP ]:
ASM user password:
Jan 22, 2013 6:05:02 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineRemotely
WARNING: Error during db connection : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

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

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Database instance hostname ……………. Listener ORACLE_HOME ……………. /u01/app/11.2.0/grid
Listener port number ……………. 1521
Cluster name ……………. oracle-db
Database unique name ……………. orcl
Email address for notifications …………… mariam.kupa@gmail.com
Outgoing Mail (SMTP) server for notifications …………… mail.tbilisi.gov.ge
ASM ORACLE_HOME ……………. /u01/app/11.2.0/grid
ASM port ……………. 1521
ASM user role ……………. SYSDBA
ASM username ……………. ASMSNMP

—————————————————————–
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: y
Jan 22, 2013 6:05:18 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/orcl/emca_2013_01_22_18_04_10.log.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5540 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 5520 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.PortManager isPortInUse
WARNING: Specified port 1158 is already in use.
Jan 22, 2013 6:05:20 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Jan 22, 2013 6:06:01 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Jan 22, 2013 6:08:00 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Jan 22, 2013 6:08:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Jan 22, 2013 6:11:39 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 22, 2013 6:11:44 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) …
Jan 22, 2013 6:12:17 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Jan 22, 2013 6:12:18 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_oracle-node1_orcl to remote nodes . ..
Jan 22, 2013 6:12:20 PM oracle.sysman.emcp.EMDBCConfig instantiateOC4JConfigFiles
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_oracle-node2_orcl to remote nodes . ..
Jan 22, 2013 6:12:26 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oracle-node1_orcl to remote nodes …
Jan 22, 2013 6:12:28 PM oracle.sysman.emcp.EMAgentConfig deployStateDirs
INFO: Propagating /u01/app/oracle/product/11.2.0/db_1/oracle-node2_orcl to remote nodes …
Jan 22, 2013 6:12:31 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Jan 22, 2013 6:13:00 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Jan 22, 2013 6:13:20 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 22, 2013 6:13:20 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://oracle-node1.mr.gov.ge:1158/em <<<<<<<<<<<
Jan 22, 2013 6:13:22 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
**************** Current Configuration ****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
———- ———- ———————

orcl oracle-node1 oracle-node1.mr.gov.ge
orcl oracle-node2 oracle-node1.mr.gov.ge
Jan 22, 2013 6:13:22 PM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encry ption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/oracle-node1_orcl/sysman/config/emkey.o ra. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 22, 2013 6:13:22 PM

For me to access OEM the URL is  https://oracle-node1.mr.gov.ge:1158/em

Good Luck!

Enable Real-Time apply on Standby(Standalone,RAC)

In this post we will discuss how to enable real-time apply feature on standalone and Real Application Cluster standby databases.

On standalone standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On physical:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

On logical:

SQL> alter database start logical standby apply immediate;

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY

On RAC standby:

1. See what is recovery mode for now:

SQL> select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED

2. Enable real-time apply

On node1:

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.

3. Check

SQL>  select dest_name,status,type,recovery_mode
from v$archive_dest_status
where dest_id=1;

DEST_NAME               STATUS    TYPE           RECOVERY_MODE
———————– ——— ————– ————————-
LOG_ARCHIVE_DEST_1      VALID     LOCAL          MANAGED REAL TIME APPLY

Installing standalone standby database for RAC

In this post we will install standalone standby database for 2-node RAC. Assume that we have already configured RAC, if not see the instructions here.

RAC description:

Instance name: orcl1
Hostname        : node1

Instance name: orcl2
Hostname        : node2

Standby description:

Instance name will be: stbydb
Hostname                    :  stbynode

All of these instances are using ASM.

So let’s start…

On primary database:

0. Add entries in hosts file.

On standby :

127.0.0.1 localhost

192.168.34.150 node1
192.168.34.151 node2

10.10.2.50 node1-priv
10.10.2.51 node2-priv

192.168.34.154 node1-vip
192.168.34.155 node2-vip

192.168.34.160 rac-scan

192.168.34.156 stbynode

On node1,node2:

127.0.0.1               localhost.localdomain localhost

192.168.34.150 node1
192.168.34.151 node2

10.10.2.50 node1-priv
10.10.2.51 node2-priv

192.168.34.154 node1-vip
192.168.34.155 node2-vip

192.168.34.160 rac-scan

192.168.34.156 stbynode

1. Enable archiving.

On node1:

[oracle@node1 ~]$ export ORACLE_SID=orcl1

sqlplus / as sysdba

SQL> select log_mode from v$database;

LOG_MODE
————
NOARCHIVELOG

SQL> shutdown immediate;

SQL> startup mount;

On node2:

export ORACLE_SID=orcl2

sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

Note that: In RAC environment to take database to archivelog mode , you must take all instances into mount state.

On node1:

SQL> alter database archivelog;

SQL> alter database open;

On node2:

SQL> alter database open;

On any node:

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

2. Enable force logging.

On node1:

SQL> select force_logging from v$database;

FOR

NO

SQL> alter database force logging;

SQL> select force_logging from v$database;

FOR

YES

3. Modify the following parameters:

On node1:

SQL> show parameter pfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      +DATA/orcl/spfileorcl.ora

SQL> create pfile from spfile;

Edit the following parameters into initorcl1.ora:

log_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
db_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
fal_client=’orcl1′
fal_client=’orcl2′
fal_server=’stbydb’
log_archive_config=’dg_config=(orcl,stbydb)’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’ENABLE’
log_archive_dest_state_3=’enable’
log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’
log_archive_dest_2=’SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb’
log_archive_dest_3=’LOCATION=+DATA/orcl/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl’
log_archive_max_processes=7
log_archive_min_succeed_dest=2
remote_login_passwordfile=’EXCLUSIVE’
standby_file_management=’auto’
sec_case_sensitive_logon=FALSE

My initorcl1.ora looks like this:

orcl1.__db_cache_size=503316480
orcl2.__db_cache_size=486539264
orcl1.__java_pool_size=16777216
orcl2.__java_pool_size=16777216
orcl1.__large_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl1.__pga_aggregate_target=587202560
orcl2.__pga_aggregate_target=587202560
orcl1.__sga_target=872415232
orcl2.__sga_target=872415232
orcl1.__shared_io_pool_size=0
orcl2.__shared_io_pool_size=0
orcl1.__shared_pool_size=318767104
orcl2.__shared_pool_size=335544320
orcl1.__streams_pool_size=0
orcl2.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/orcl/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/orcl/controlfile/current.260.793127259′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’orcl’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_format=’%t_%s_%r.dbf’
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’exclusive’
orcl1.thread=1
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS1′
orcl1.undo_tablespace=’UNDOTBS2′
log_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
db_file_name_convert=’+DATA/stbydb’,’+DATA/orcl’
fal_client=’orcl1′
fal_client=’orcl2′
fal_server=’stbydb’
log_archive_config=’dg_config=(orcl,stbydb)’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’ENABLE’
log_archive_dest_state_3=’enable’
log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl’
log_archive_dest_2=’SERVICE=stbydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb’
log_archive_dest_3=’LOCATION=+DATA/orcl/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcl’
log_archive_max_processes=7
log_archive_min_succeed_dest=2
remote_login_passwordfile=’EXCLUSIVE’
standby_file_management=’auto’
*.sec_case_sensitive_logon=FALSE

SQL> shutdown immediate;

SQL> startup nomount pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora’

On node2:

[oracle@node2 ~]$ export ORACLE_SID=orcl2
[oracle@node2 ~]$ sqlplus / as sysdba

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

On node1:

SQL> create spfile=’+DATA/ORCL/spfileorcl.ora’ from pfile=’/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl1.ora’;

Edit initorcl1.ora file and write just the following entry:

SPFILE=’+DATA/ORCL/spfileorcl.ora’

And start the instance.

SQL> shutdown immediate;

SQL> startup

On node2:

If you try to open node2, it will stuck because it will try to send archivelog to standby location, which is not completely configured yet. So let node2 to be in down state.

3. Create same password files for all instances(passwords must be the same ).

On node1:

[oracle@node1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle force=y entries=5 ignorecase=y

On node2:

[oracle@node2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle force=y entries=5 ignorecase=y

On stbynode:

[oracle@stbynode ~]$ orapwd file=$ORACLE_HOME/dbs/orapwstbydb password=oracle entries=5 force=y  ignorecase=y

4. Create standby logfiles on primary.

On node1:

SQL> sqlplus / as sysdba
SQL>  select max(length(member)) from v$logfile;

MAX(LENGTH(MEMBER))
——————-
42

SQL> col member for a42
SQL> select group#,member,type from v$logfile order by 1;

GROUP# MEMBER                                     TYPE
———- —————————————— ——-
1 +DATA/orcl/onlinelog/group_1.261.793127267 ONLINE
2 +DATA/orcl/onlinelog/group_2.262.793127267 ONLINE
3 +DATA/orcl/onlinelog/group_3.266.793128605 ONLINE
4 +DATA/orcl/onlinelog/group_4.267.793128605 ONLINE

SQL> alter database add standby logfile thread 1;

SQL> alter database add standby logfile thread 2;

SQL> select group#,member,type from v$logfile order by 1;

GROUP# MEMBER                                     TYPE
———- —————————————— ——-
1 +DATA/orcl/onlinelog/group_1.261.793127267 ONLINE
2 +DATA/orcl/onlinelog/group_2.262.793127267 ONLINE
3 +DATA/orcl/onlinelog/group_3.266.793128605 ONLINE
4 +DATA/orcl/onlinelog/group_4.267.793128605 ONLINE
5 +DATA/orcl/onlinelog/group_5.273.793983817 STANDBY
5 +DATA/orcl/onlinelog/group_5.274.793983819 STANDBY
6 +DATA/orcl/onlinelog/group_6.271.793983823 STANDBY
6 +DATA/orcl/onlinelog/group_6.272.793983823 STANDBY

5. Create parameter file for standby database.

SQL> create pfile=’$ORACLE_HOME/dbs/initstbydb.ora’ from spfile;

Edit required parameters. Delete red entries. Green fields are corrected entries:

orcl2.__db_cache_size=486539264
orcl1.__
db_cache_size=503316480
orcl2.__java_pool_size=16777216
orcl1.__
java_pool_size=16777216
orcl2.__large_pool_size=16777216
orcl1.__
large_pool_size=16777216
orcl1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=587202560
orcl1.__
pga_aggregate_target=587202560
orcl2.__sga_target=872415232
orcl1.__
sga_target=872415232
orcl2.__shared_io_pool_size=0
orcl1.__shared_io_pool_size=0
orcl2.__shared_pool_size=335544320
orcl1.__
shared_pool_size=318767104
orcl2.__streams_pool_size=0
orcl1.__
streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stbydb/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/stbydb/controlfile/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=‘+DATA/orcl’,’+DATA/stbydb’
*.db_name=’orcl’
*.db_unique_name=’stbydb’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)’
*.fal_client=’stbydb
*.fal_server=’orcl
orcl2.instance_number=2
orcl1.instance_number=1
*.log_archive_config=’dg_config=(orcl,stbydb)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb
*.log_archive_dest_2=’SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl
*.log_archive_dest_3=’LOCATION=+DATA/stbydb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb
*.log_archive_dest_state_1=’enable’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_dest_state_3=’enable’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.log_file_name_convert=’+DATA/orcl‘,’+DATA/stbydb
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
*.standby_file_management=’auto’
orcl1.thread=1
orcl2.thread=2
orcl2.
undo_tablespace=’UNDOTBS1′
orcl1.undo_tablespace=’UNDOTBS2′
sec_case_sensitive_logon=FALSE

—initstbydb.ora

db_cache_size=503316480
java_pool_size=16777216
large_pool_size=16777216
pga_aggregate_target=587202560
sga_target=872415232
shared_pool_size=318767104
streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stbydb/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/stbydb/controlfile/control01.ctl’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_file_name_convert=’+DATA/orcl’,’+DATA/stbydb’
*.db_name=’orcl’
*.db_unique_name=’stbydb’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbydbXDB)’
*.fal_client=’stbydb’
*.fal_server=’orcl’
*.log_archive_config=’dg_config=(stbydb,orcl)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb’
*.log_archive_dest_2=’SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl’
*.log_archive_dest_3=’LOCATION=+DATA/stbydb/STANDBYLOG  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stbydb’
*.log_archive_dest_state_1=’enable’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_dest_state_3=’enable’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_archive_max_processes=7
*.log_archive_min_succeed_dest=2
*.log_file_name_convert=’+DATA/orcl’,’+DATA/stbydb’
*.memory_target=1449132032
*.open_cursors=300
*.processes=150
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’EXCLUSIVE’
STANDBY_FILE_MANAGEMENT=’auto’
sec_case_sensitive_logon=FALSE

Copy parameter file to standby.

[oracle@node1 dbs]$ scp initstbydb.ora oracle@stbynode:$ORACLE_HOME/dbs

The authenticity of host ‘stbynode (192.168.34.156)’ can’t be established.
RSA key fingerprint is 73:fa:1f:a0:04:41:68:98:30:25:28:55:59:9c:07:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘stbynode,192.168.34.156’ (RSA) to the list of known hosts.
oracle@stbynode’s password:
initstbydb.ora                                100% 1479     1.4KB/s   00:00

6. Create required directories on primary and standby databases.

On node1:

mkdir -p /u01/app/oracle/backup

On stbynode

mkdir -p  /u01/app/oracle/admin/stbydb/adump
mkdir -p /u01/app/oracle/backup

su – grid

[grid@stbynode ~]$ asmcmd
ASMCMD> ls
CRS/
DATA/
ASMCMD> cd data
ASMCMD> ls
ASMCMD> mkdir stbydb
ASMCMD> cd stbydb
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE STANDBYLOG TEMPFILE

7. Add tns entries in tnsnames.ora file and listener entry in listener.ora file:

–tnsnames.ora

On node1, node2 and stbynode:

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

stbydb=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbynode)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stbydb)
)
)

On node1, node2 and stbynode:

[oracle@node2 dbs]$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 14-SEP-2012 17:04:13

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)
[oracle@node2 dbs]$ tnsping stbydb

TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 14-SEP-2012 17:04:20

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stbynode)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = stbydb)))
OK (20 msec)

8. Add entry in oratab.

On stbynode:

vi /etc/oratab

+ASM:/u01/app/11.2.0/grid:N
stbydb:/u01/app/oracle/product/11.2.0/db_1:N

9. Making backups on primary(node1) and copy them to standby.

On node1:

export ORACLE_SID=orcl1
rman target /
RMAN> configure channel device type disk format ‘/u01/app/oracle/backup/%U’;
RMAN> backup database plus archivelog;
RMAN> backup current controlfile for standby;
RMAN> backup archivelog all;

[oracle@node1 ~]$ scp /u01/app/oracle/backup/* oracle@stbynode:/u01/app/oracle/backup/

10.  Duplicate database.

On stbynode:

[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ sqlplus / as sysdba

SQL> startup nomount;

[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ rman target sys/oracle@orcl auxiliary /

Recovery Manager: Release 11.2.0.1.0 – Production on Fri Sep 14 19:50:02 2012

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

connected to target database: ORCL (DBID=1320907995)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;

[oracle@stbynode ~]$ export ORACLE_SID=stbydb
[oracle@stbynode ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

11. Startup all.

On node2:

SQL> startup;

On stbynode:

alter database open;

12. Test.

On node1:

SQL> create table b(b number);
SQL> insert into b values(1);
SQL> commit;
SQL> alter system switch logfile;

On node2:

SQL> alter system switch logfile;

On stbynode:

SQL> select * from b;

B
———-
1

On node1:

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#;

On node2:

SQL> alter system switch logfile;

On stbynode:

ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;