How to fix the standby database after ORACLE 11G replica uard Failover, and restore uardfailover

How to fix the standby database after ORACLE 11G replica uard Failover, and restore uardfailover


Problem scenarios after failover:
Due to the failover test, a standby database has been changed to the primary database. How can I change this new primary database (the original standby database) back to the standby database?
Both are primary, p1, and p2. How can I set one primary database 1 to p1, And the other primary database p2 to the p1 standby database?


1. Problem Description
Original primary database:
SQL> select open_mode, database_role from v $ database;


OPEN_MODE DATABASE_ROLE
------------------------------------
READ WRITE PRIMARY


SQL>


The new failover becomes primary (originally the standby database) p2,
SQL> select open_mode, database_role from v $ database;


OPEN_MODE DATABASE_ROLE
------------------------------------
READ WRITE PRIMARY


SQL>
In addition to cloning standby again, is there another way to convert p2 back to the standby of p1?


A simple method is to open the flashback database in the standby database.
Then, after failover, use flashback to roll back to the time point before switching.
If it is not enabled, you can only clone the slave database again.


2. Solution
Check whether the database is enabled on standby,
SQL> SELECT FLASHBACK_ON FROM v $ database;


FLASHBACK_ON
------------------
NO


SQL>
Flash back is not enabled, physical dg is generally not enabled, and general oracle Database flash back is not enabled by default, You need to manually enable.
Physical dg facilitates reconstruction, But redo applications cannot be updated in time.

Only clone and reconstruction can be performed again.


3. Select clone to rebuild standby.
3.1 first confirm that the primary database is in archive Mode
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 362
Next log sequence to archive 364
Current log sequence 364
SQL>


3.2 Add the standby File
The standby file has been in the working state because it was already in the dg mode.
Select * from v $ logfile order by 1;


3.3 generate a parameter file
Generate pfile
Create pfile from spfile;
Shutdown immediate;
Check the parameter file. Because it was rebuilt in the previous dg Environment, you do not need to make too many modifications to the parameter file. Perform a routine check on OK.
Vim $ ORACLE_HOME/dbs/initpowerdes. ora
*. Db_unique_name = pdunq
*. Diagnostic_dest = '/oracle/app/oracle'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = powerdesXDB )'
*. Fal_client = 'pdunq'
*. Fal_server = 'pdunq _ dg'
*. Standby_file_management = 'auto'
*. Db_file_name_convert = '/home/oradata/powerdes', '/home/oradata/pwerdes'
*. Log_file_name_convert = '/home/oradata/powerdes', '/home/oradata/powerdes'
*. Log_archive_config = 'dg _ CONFIG = (pdunq, pdunq_dg )'
*. Log_archive_dest_2 = 'service = pdunq_dg lgwr sync affrem VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = pdunq_dg'
*. Log_archive_dest_state_2 = 'enable'


Generate a new spfile by Convention
Shutdown
Create pfile from spfile;
Startup


3.4. register the listening mode again.
The listener. ora and tnsnames. ora on the primary do not need to be modified as the original dg Environment has been configured.
Configure the maximum available mode:
Configure the maximum available mode:
SQL> startup
SQL> alter database set standby database to maximize availability;

3.5. Back up the database on the new primary
RMAN> backup database plus archivelog;
RMAN> backup current controlfile for standby;
RMAN> exit


3.6. Backup set, parameter file, and control file synchronization
Including the dump file directory and data file directory. Run the show parameter dest; command. Because standby already exists, you do not need to recreate all directories. This step is omitted.


Copy data files from primary to standby
Run the following command on the primary database:
Ps: run on primary
Copy flash back content
Copy Flash files
Cd/oracle/app/oracle/flash_recovery_area/
Scp-r./* 192.168.121.218:/oracle/app/oracle/flash_recovery_area/


Copy parameter file
Cd/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
Scp-r./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs


Copy the listening file. Because the original dg Environment already exists, you do not need to copy the file. The following steps can be omitted.
Cd/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/
Scp-r./* 192.168.121.218:/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/


PS: At this point, the following operations are basically performed on standby. If there is any additional prompt


4. modify the configuration file on standby.
Modify the configuration file in the standby database on standby. Modify db_unique_name and log_archive_dest_2 as follows:
[Oracle @ powerlong5 admin] $ vim listener. ora
*. Db_unique_name = 'pdunq _ dg '# enter the db_unique_name name of standby.
*. Keys = 'service = Your lgwr sync affrem VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = pdunq '# enter the primary db_unique_name, which is mainly used for switchover.
PS: Set *. log_archive_dest_2 = change DB_UNIQUE_NAME to the value of DB_UNIQUE_NAME of primary to pdunq. In this way, the new primary can upload the redo log to the new standby during switchover.
The purpose of log_archive_dest_N is to tell the database to put the archive in it. The first option is local, and then the remote slave database is considered. Therefore, assume that A is the master database and B is the slave database, after switching, B is the master database and A is the slave database. Therefore, log_archive_dest_N must be set to the peer database.


4.1. Restart the standby listener.
Lsnrctl stop;
Lsnrctl start;


4.2. Restore the database
Operate on standby
Close oracle, generate the parameter file, start oracle to nomount, and restore rman
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> startup nomount
[Oracle @ powerlong5 admin] $ rman target sys/syspl1758 @ pdunq_dg auxiliary/
RMAN> run {
Allocate auxiliary channel c1 device type disk;
Allocate auxiliary channel c2 device type disk;
Duplicate target database for standby nofilenamecheck dorecover;
Release channel c1;
Release channel c2;
}


4.3. Disable oracle
Shutdown immediate
Start Database
Startup nomount;
Alter database mount standby database;
Alter database add standby logfile;
Alter database add standby logfile;
Alter database add standby logfile;
Alter database recover managed standby database using current logfile disconnect from session;


4.4. Verify the redo log application status on primary and standby.
Archive log list;
The standby database has the following exceptions:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL>
The redo log is not transmitted.


4.5. view the archive parameters and reset the settings:
Alter system set log_archive_dest_2 = 'service = pdunq_dg lgwr sync affrem VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = pdunq_dg ';
Select open_mode, database_role from v $ database;


Then go to the alert Log On primary, with the following information:
**************************************** *******************************


Fatal NI connect error 12514, connecting:
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.218) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = pdunq_dg) (CID = (PROGRAM = oracle) (HOST = powerlong4) (USER = oracle ))))


Version information:
TNS for Linux: Version 11.2.0.1.0-Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0-Production
Time: 10-FEB-2015 16:11:34
Tracing not turned on.
Tns error struct:
Ns main err code: 12564

TNS-12564: TNS: connection refused
Ns secondary err code: 0
Nt main err code: 0
Nt secondary err code: 0
Nt OS err code: 0
Error 12514 removed ed logging on to the standby
Errors in file/oracle/app/oracle/diag/rdbms/pdunq/powerdes/trace/powerdes_arc3_6627.trc:
ORA-12514: TNS: listener does not currently know of service requested in connect descriptor
PING [ARC3]: Heartbeat failed to connect to standby 'pdunq _ dg '. Error is 12514.


4.6. check the standby database and check the name status. The db_unique_name is not set as follows:
SQL> show parameter name;


NAME TYPEVALUE
-----------------------------------------------------------------------------
Db_file_name_convert string/home/oradata/powerdes,/home/
Oradata/powerdes
Db_name stringpowerdes
Db_unique_name stringpdunq
Global_names booleanFALSE
Instance_name stringpowerdes
Lock_name_space string
Log_file_name_convert string/home/oradata/powerdes,/home/
Oradata/powerdes
Service_names stringpdtest
SQL>
Db_unique_name of the standby database is incorrect and needs to be modified.


4.7. Modify the spfile parameter on the standby database.
SQL> create pfile from spfile;
SQL> shutdown immediate;
[Oracle @ powerlong5 dbs] $ cp $ ORACLE_HOME/dbs/initpowerdes. ora $ ORACLE_HOME/dbs/initpowerdes. ora. bak
[Oracle @ powerlong5 dbs] $ vim $ ORACLE_HOME/dbs/initpowerdes. ora
*. Db_unique_name = 'pdunq _ dg'
SQL> create spfile from pfile;
SQL> startup mount;


4.8. check the standby archive again. Normally, as shown below:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 32
SQL>
Check the primary archiving status as follows:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL>


OK. Now you can open the standby database.


5. Open the database and start the redo application.
Alter database open;
Start the redo Application
Alter database recover managed standby database using current logfile disconnect;


The primary database performs the alter system switch logfile operation to check whether new logs exist in standby.
Primary database:
SQL> alter system switch logfile;


System altered.


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
SQL>

Standby database:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 32
Next log sequence to archive 0
Current log sequence 33
SQL>


OK. Archive logs are transmitted to the standby database in a timely manner. After failover, the new standby reconstruction is successfully completed.
Bytes ----------------------------------------------------------------------------------------------------------------
<All Rights Reserved. This document can be reprinted, but the source address must be indicated by link. Otherwise, we will be held legally responsible.>
Original blog address: http://blog.itpub.net/26230597/viewspace-1433720/
Original Author: Huang Shan (mchdba)
Bytes ----------------------------------------------------------------------------------------------------------------