Monthly Archives: September 2013

Flashback through role transition for physical standby

September 26, 201311g DataGuard, 11g R2 RMAN, UncategorizedAnju Garg

In this post, I will demonstrate that database role does not change when a physical standby is flashed back through a switchover.

Overview:
– Note current SCN# (pre switchover)
– Check current configuration
pc00sby1 – Primary database
pc00prmy – Physical standby database
– Perform switchover
– check post switchover configuration
pc00prmy – Primary database
pc00sby1 – Physical standby database
– Flashback new primary (pc00prmy) to SCN# before switchover
– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary as SCN# of standby is ahead of primary
– To bring back the standby database into configuration, flashback it to a scn# before switchover
— check that both the databases have retain their current roles after flashback
pc00prmy – Primary database
pc00sby1 – Physical standby database

– Implementation –

- Check current configuration

pc00sby1 – Primary database
pc00prmy – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00sby1 - Primary database
pc00prmy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out current SCN# (before switchover)

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– Perform switchover

DGMGRL> switchover to pc00prmy;
Performing switchover NOW, please wait...
New primary database "pc00prmy" is opening...
Operation requires shutdown of instance "pc00sby1" on database "pc00sby1"
Shutting down instance "pc00sby1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pc00sby1" on database "pc00sby1"
Starting instance "pc00sby1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "pc00prmy"

– check that switchover has taken place successfully
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

PC00SBY1>select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

PC00PRMY>select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

– Flashback new primary (pc00prmy) to SCN# before switchover (1100354)

PC00PRMY>shu immediate;
         startup mount;
         flashback database to scn 1100354;
         alter database open resetlogs;

         alter system switch logfile;

– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
 ERROR

DGMGRL> show database pc00sby1;

Database - pc00sby1

Enterprise Manager Name: pc00sby1.us.oracle.com
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
pc00sby1

Database Error(s):
 ORA-16700: the standby database has diverged from the primary database
 ORA-16766: Redo Apply is stopped

Database Status:
 ERROR

— The reason for diverging is that SCN# of standby( 1101143) is ahead of primary (1100582)

PC00PRMY>select current_scn from v$database;

CURRENT_SCN
-----------
1100582

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1101143

— To bring back the standby database into configuration, we need to flashback it to an scn# behind primary.

– Let’s flashback standby database to a scn# before switchover (1100354) since we want to verify that databases maintain their current role after flashback.

PC00SBY1>shu immediate;
startup mount;
flashback database to scn 1100354;

alter database open;

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– switch log on primary –

PC00PRMY>alter system switch logfile;

System altered.

– check configuration –
— Both the databasese retain their current roles –
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this post was useful.

Enjoy!

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

ORA-01102: cannot mount database in EXCLUSIVE mode

September 13, 201311g DataGuard, UncategorizedAnju Garg

I received this error message when I was trying to mount the primary database in my dataguard configuration.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode

Here is how I resolved it:

- Shutdown the primary database

SQL> shu immediate;
ORA-01507: database not mounted

– Find out all the processes belonging to the database still running

[oracle@EDP1R3P0-pc00sby1 dbs]$ ps -ef |grep ora_|grep pc00sb

– Kill all the processes

[oracle@EDP1R3P0-pc00sby1 dbs]$ kill -9 32231

– Check that there are no more processes running

[oracle@EDP1R3P0-pc00sby1 dbs]$ ps -ef |grep ora_|grep pc00sb

– startup the database – succeeeds

SQL> startup
ORACLE instance started.

Total System Global Area  439406592 bytes
Fixed Size                  1337072 bytes
Variable Size             348129552 bytes
Database Buffers           83886080 bytes
Redo Buffers                6053888 bytes
Database mounted.
Database opened.

—————————————————————-
Related links:

Home

11g R2 RAC Index

INS-08109 unexpected error occured while validating inputs at state ‘InstallOptions’
ORA-15040: diskgroup is incomplete
PROT-35: The configured Oracle Cluster Registry locations are not accessible
PRVF-5636 : The DNS response time for an unreachable node exceeded 15000 ms

PROT-35: The configured Oracle Cluster Registry locations are not accessible

September 12, 201311g R2 RAC, UncategorizedAnju Garg

I received this error message when I was trying to restore OCR from its backup.

Reason: The ASM compatibility of the diskgroup to which I was trying to restore OCR was 11.0.0.0

Solution : Advance the ASM compatibility of the destination diskgroup to a value >= 11.2.0.0 and then try to restore OCR.

Hope this post was useful!

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

ORA-15040: diskgroup is incomplete

September 4, 201311g R2 RAC, ASM, UncategorizedAnju Garg

I received this error while I was trying to bring up cluster on the second in my 2-node cluster.

When I tried to start cluster, ora.asm resource was found in intermediate state.

[root@host02 ~]# crsctl start cluster
CRS-5702: Resource 'ora.evmd' is already running on 'host02'
CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'host02'
CRS-4000: Command Start failed, or completed with errors.

– Since asm was not up, OCR could not be read and hence cluster could not come up

[root@host02 ~]# crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  INTERMEDIATE host02                   OCR not started

– The alert log showed that DATA diskgroup could not be mounted.

2013-09-04 13:55:38.896
[/u01/app/11.2.0/grid/bin/oraagent.bin(28674)]CRS-5019:All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. Details are at “(:CLSN00100:)” in “/u01/app/11.2.0/grid/log/host02/agent/ohasd/oraagent_grid/oraagent_grid.log”.

– I tried to mount DATA diskgroup using SQL but I got ORA-15040 which indicated that disk “0” could not be read

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing from group number "1"

– When I listed ASM disks on second node, ASMDISK01 was not listed

[root@host02 bin]# oracleasm listdisks
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

– I issued oracleasm scandisks which led to discovery of ASMDISK01

[root@host02 bin]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDISK01"

– Now when I issued oracleasm listdisks, ASMDISK01 was also listed.

[root@host02 bin]# oracleasm listdisks
ASMDISK01
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

— Now I could mount DATA diskgroup successfully

SQL> alter diskgroup data mount;

Diskgroup altered.

I hope this post was helpful.

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

INS-08109 unexpected error occured while validating inputs at state ‘InstallOptions’

September 2, 201311g R2 RAC, UncategorizedAnju Garg

I received this error when I was trying to install 11.2.0.3 database software in my 2 node cluster employing 11.2.0.3 clusterware.

Reason: clusterware was not up

Solution: Start the clusterware and attempt database software installation again.

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

ORACLE IN ACTION

Let's do it simply…

Monthly Archives: September 2013

Flashback through role transition for physical standby

Like this:

ORA-01102: cannot mount database in EXCLUSIVE mode

Like this:

PROT-35: The configured Oracle Cluster Registry locations are not accessible

Like this:

ORA-15040: diskgroup is incomplete

Like this:

INS-08109 unexpected error occured while validating inputs at state ‘InstallOptions’

Like this:

Follow ORACLE IN ACTION

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Follow ORACLE IN ACTION