ORA-16795: the standby database needs to be re-created

DB CONFIGURATION

2 node primary database

2 node standby database

Fast_Start FailOver(observer)- Enabled

BHU_A è Primary database

BHU_B è standby database

n  Due to the network issues, FSFO failover the standby database (BHU_B) as a new primary and old primary database (BHU_A) is in the mount stage.

oracle BHU bhuora001> 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 /xxxxxxxxxxxxxx

Connected.

DGMGRL> show configuration

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_B - Primary database

      Warning: ORA-16817: unsynchronized fast-start failover configuration

    BHU_A - (*) Physical standby database (disabled)

      ORA-16795: the standby database needs to be re-created

Fast-Start Failover: ENABLED

Configuration Status:

WARNING

n  We check the flashback enabled option on both old & new primary. We are showing the output of new primary database

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

n  On the NEW PRIMARY SITE; check when the conversion happened(standby to primary) and you can see the time by looking to scn_to_timestamp conversion.

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

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

12788498729592

SQL> SELECT SCN_TO_TIMESTAMP(12788498729592) from dual;

SCN_TO_TIMESTAMP(12788498729592)

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

08-AUG-13 22.04.00.000000000

On the OLD PRIMARY DATABASE(BHU_A). Check the status of database

SQL> select name,open_mode,database_role from gv$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

BHU    MOUNTED              PRIMARY

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

n When we try to check  CURRENT_SCN number on the old primary and it has displayed as “0”

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

          0

Now, We are converting the old primary database as a NEW STANDBY DATABASE

n  We are mounting the database first

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2271580160 bytes

Fixed Size                  2230352 bytes

Variable Size            1191184304 bytes

Database Buffers         1073741824 bytes

Redo Buffers                4423680 bytes

Database mounted.

n  We have identified the failover time from the new database. so we will use that time to flashback the old primary database; we have add 20 further to actual time.

SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('2013-08-08 21:50:00', 'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

n  We are checking the current role before converting the old primary to new standby database.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

BHU    MOUNTED              PRIMARY

n  Converting database to standby database.

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

n  We are checking the current role after converting the old primary to new standby database.

SQL> select name,open_mode,database_role from v$database;

select name,open_mode,database_role from v$database

                                         *

ERROR at line 1:

ORA-01507: database not mounted

n  After converting the database; database will go to nomount stage; so we have to stop & start the database.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2271580160 bytes

Fixed Size                  2230352 bytes

Variable Size            1191184304 bytes

Database Buffers         1073741824 bytes

Redo Buffers                4423680 bytes

Database mounted.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

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

BHU    MOUNTED              PHYSICAL STANDBY

n  We could see the current status of the old primary as new standby database.

n  Now we are login to the dg broker to enable the new standby database

DGMGRL> enable database 'BHU_A';

Enabled.

n  Once enabled, oracle will take some time to apply the logs till the current time

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_B - Primary database

      Warning: ORA-16817: unsynchronized fast-start failover configuration

    BHU_A - (*) Physical standby database

      Warning: ORA-16817: unsynchronized fast-start failover configuration

Fast-Start Failover: ENABLED

Configuration Status:

WARNING

n  Now primary database & standby database are in sync.

DGMGRL> show configuration;

Configuration - DG_BHU

  Protection Mode: MaxAvailability

  Databases:

    BHU_B - Primary database

    BHU_A - (*) Physical standby database

Fast-Start Failover: ENABLED

Configuration Status:

SUCCESS



ORA-16649: possible failover to another database prevents this database from being opened

We have two-node primary RAC database & two-node standby RAC database. For the business testing purpose, they will want both side of the database in the READ WRITE Mode. So we have decided to disabled the log shipping and made the standby database as a new primary database using failover database.

During the standby failover process, we made the existing primary database down. We have successfully failover the standby database as a new primary database. Once the process completed, we have disabled the DG configuration.

When we try to start the old primary database, we go the below error message. After analyzing further, we found the DG configuration file has been update from the standby database DG configuration file. Since the standby database become a new primary. So it has update the DG configuration file in the old primary database.

ORA-16649: possible failover to another database prevents this database from being opened

SOLUTION

After disabling the DG_BROKER_START=FALSE;  the old primary database able to start in the READ WRITE mode.

SQL> startup force

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

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

System altered.

SQL> alter database open;

Database altered.

ORA-16649: possible failover to another database prevents this database from being opened

We have two node primary & standby RAC. It is in sync and i have to split the primary database & standby database.

I have shutdown the primary database and went to the standby database server.

I logged in as DGMGRL and performed a fail over to the standby. Now the standby database become a primary database. Once i complete the failover; i have disabled the DG configuration using the DG broker.

When i try to started the old primary which i have shutdown before performing a failover to the standby. I got a error message saying  “ORA-16649: possible failover to another database prevents this database from being opened”

SQL> startup force

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 7.2958E+10 bytes

Fixed Size                  2235808 bytes

Variable Size            1.9059E+10 bytes

Database Buffers         5.3687E+10 bytes

Redo Buffers              209866752 bytes

Database mounted.

ORA-16649: possible failover to another database prevents this database from being opened

WHEN I CHECKED THE DB ROLE; it is primary

SQL> select database_role from v$database;

DATABASE_ROLE

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

PRIMARY

Then i managed to find that database control file get the information from the DG broker config file. So i have disable the config file on the primary database.

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

System altered.

-         After disabling the DG broker; i can able to open the database without any issue.

SQL> alter database open;

Database altered.

index Rebuild - Progress & Index creation Progress

To identify the index rebuild progress.

i have used the oracle schema to identify the session which are active in nature.

Note: For some reason V$SESSION_LONGOPS doesn't show the progress of index Rebuild process. use the below query for index rebuild. index creation can be seen in the below query and other query with the
V$SESSION_LONGOPS

SQL> SELECT MESSAGE FROM V$SESSION_LONGOPS WHERE SID IN (SELECT SID FROM V$SESSION WHERE USERNAME='BHUVAN' AND STATUS='ACTIVE') ORDER BY START_TIME;

MESSAGE

-----------------------------------------------------------------------------------Table Scan:  (stale or locked) obj# 31940: 1426408 out of 1426408 Blocks done

Table Scan:  BHUVAN.EMP: 2388523 out of 2388523 Blocks done

Sort Output:  : 235280 out of 235280 Blocks done

Table Scan:  BHUVAN.EMP: 154318 out of 2388523 Blocks done

-- i have merge active session with the V$SESSION_LONGOPS view to retrieve progress of index creation with the index creation syntax.

SQL> col a.sid format 9999

SQL> select a.sid, sql_text ,target, sofar, totalwork, time_remaining still, elapsed_seconds tillnow

  2  from v$session a ,  v$sql b, v$session_longops c

  3  where a.sid=c.sid

  4  and a.sql_address = b.address

  5  and a.sql_address = c.sql_address

and status  = 'ACTIVE';

 SID

SQL_TEXT

TARGET                                                                SOFAR  TOTALWORK      STILL    TILLNOW

---------------------------------------------------------------- ---------- ------ 366

CREATE INDEX "EMP~EXT" ON "EMP" ("CLIENT", "BPEXT") PCTFREE 10 INITRANS 002 TABLESPACE PBHUVAN COMPRESS 2 STORAGE (INITIAL 0000000064 K NEXT 0000001024 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001)

BHUVAN.EMP                                                        894046    2388523        563        337

Hope this help you..... Happy Learning

Increasing ASM memory parameter

Note: if you are huge pages then automatic memory management is not support. please check that before using AMM.


Today I have increase the ASM memory on two environment. I have noticed huge difference in the ASM behaviour.

#1 (bhuora002/  bhuora003).

I have increase the ASM memory through command prompt.

Alter system set memory_max_target=2G scope=spfile sid=’*’;

Alter system set memory_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM start without any issues.

#2 ( bhuora004/  bhuora005).

I have increase the ASM memory through command prompt.

Alter system set sga_max_target=2G scope=spfile sid=’*’;

Alter system set sga_target=2G scope=spfile sid=’*’;

Behaviour: When I try to bounce the GRID, ASM is not start and it has thrown error message. I taken the backup of spfile and change the values in the init file.  Once I have modified the values, I have used the same pfile to start the ASM instance(mount stage). Once it is mount, I have created the spfile from the pfile which I have used.This spfile set in the all the places.  Then I have stopped the ASM instance & bounced the GRID.

 So please use the memory_target & memory_max_target parameters while trying to increase the ASM memory parameters.

ORA-00376: file 6 cannot be read at this time

When I try to start a standby database in the read only mode, I have been thrown the below error message

oracle BHU_1> srvctl start database -d BHU_B

PRCR-1079 : Failed to start resource ora.BHU_b.db

CRS-5017: The resource action "ora.BHU_b.db start" encountered the following error:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193'

Process ID: 9165

Session ID: 3497 Serial number: 1

. For details refer to "(:CLSN00107:)" in "/oracle/GRID/11203/log/bhurac01/agent/crsd/oraagent_oracle/oraagent_oracle.log"

It is an undo tablespace (datafile) which is throwing the error and I have checked the datafile it is in the recovery mode.

select file#,name,status,enabled from v$datafile where file#=6;

6 +BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193                                                                                                   RECOVER READ WRITE

SO I HAVE FOLLOWED BELOW PROCEDURE TO OVERCOME THIS PROBLEM

#1

1 A)  Stop the apply process in the standby database

DGMGRL> edit database 'BHU_B' set state='APPLY-OFF';

Succeeded.

2 B)  It is RAC Database, kept only one instance in mount stage and other instance are in the offline mode(shutdown)

3

  C)  It is standby database (undo tablespace-datafile). So I am not able to do offline

#2 – Take a online backup of datafile through RMAN

RMAN> copy datafile 6 to '+BHU_B_DATA1';

Starting backup at 25-JUL-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1842 instance=BHU_1 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193

output file name=+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 tag=TAG20120725T124447 RECID=69 STAMP=789569388

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:05

Finished backup at 25-JUL-12

Starting Control File and SPFILE Autobackup at 25-JUL-12

piece handle=/oracle/BHU/11203/dbs/c-72629545-20120725-01 comment=NONE

Finished Control File and SPFILE Autobackup at 25-JUL-12

#3 I doing a rename through RMAN itself, there is no need of using the RENAME command in the sqlplus

RMAN> switch datafile 6 to copy;

datafile 6 switched to datafile copy "+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089"

RMAN> exit

#4 when I check the status of the datafile, it looks in the RECOVER MODE. So I cant open the database in the READ-ONLY MODE.

SQL> select name,status from v$datafile where file#=6;

NAME                                                 STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089  RECOVER

#5 Started the Recover through DG Broker

DGMGRL> edit database 'BHU_B' set state='APPLY-ON';

Succeeded.

#6 Monitor the apply Lag

I could see that the system is using the old archive log to recover the datafile.

Once the recovery is completed, you can open the database in the read only mode.

I have see the status of the datafile; it is in the online mode

SQL> select name,status from v$datafile where file#=6;

NAME                                  STATUS

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

+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 ONLINE

change the ASM spfile in 11gR2

How to change the ASM spfile in 11gR2

Logical steps to change the ASM spfile

1. Create intermediate pfile from the current spfile or pfile
2. Create spfile in a new disk group from the intermediate pfile
3. Restart the HA stack to verify that ASM starts up fine with moved spfile
4. Remove the original spfile

You can verify whether you are using the correct spfile for the ASM in the cluster environment.

Multiple way to check the spfile details in 11gR2

#1

oracle +ASM1> asmcmd spget

+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

#2

oracle +ASM1 > gpnptool get

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="11" ClusterUId="3aebd9e0996e5f57bf5fa6a0accabc43" ClusterName="bhurac1a" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net2" IP="10.217.11.16" Adapter="bond4" Use="cluster_interconnect"/><gpnp:Network id="net1" Adapter="bond2" IP="10.218.8.0" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="/dev/oracleasm/disks" SPFile="+OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>1ZtgbzWQAuybhO0J12R2X5D+gMc=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>A1Bx+lPu1QSsxGYrRJ5jVbhJ/oVkP8DcKxoCgV90gLk7/m4CztcatcRftHSDvg92z/0HzEog7AGltl0pDZZLMgA9sglWUop/GOPkzF1jxO9I7qbjQjeqLOoS79+XXV9M9LQ8KtosNvGE50VdE2tswdWc2IDJ8AelhL9wNCabBwg=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

#3

oracle +ASM1 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 23 12:22:00 2012

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 Real Application Clusters and Automatic Storage Management options

SQL> show parameter spfile

NAME                                 TYPE                                                  VALUE

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

spfile                               string  +OCR_VOTE/bhurac1a/asmparameterfile/registry.253.789386957

If you want to create a spfile from the existing spfile with some modification then you need to take backup from existing one.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile;

In the below case, my spfile is located in the environment and it is NOT picked up by the cluster during the startup.

So I am creating the pfile from spfile which is located in the diskgroup.

SQL> create pfile='/home/oracle/init+ASM.ora' from spfile='+OCR_VOTE/bhurac1a/ASMPARAMETERFILE/REGISTRY.253.757697363';

If you need modify any parameter in the ASM parameter, we can modify it.

Create a new spfile from the pfile

SQL> create spfile=’+OCR_VOTE’ from pfile='/home/oracle/init+ASM.ora';

Once the spfile is create, if you check spfile location in the cluster environment. it will show the new file.

You can verify using the below command. Note: THIS SPFILE WILL BE INCLUDING TO ALL THE CLUSTER NODES IN THE ENVIRONMENT.

#1 set the ASM environment values

$ asmcmd spget

(Or)

$ gpnptool get

Restart the cluster and you could see the cluster is picking up the new spfile.