Rebuilding Oracle Dataguard Standby database with Rman duplicate

Issue background

• Applicable situation:

Operating system: Redhat 6.5

Database: Oracle 11g R2

Problem Description: The primary repository cannot be recovered and started or loses the main failover after the

• Advantages

1. No need for primary database downtime
2. Simple execution

• Pre-Implementation preparatory work

1. Test Dumplicate

2. Test environment database using dumplicate to reconstruct Stanby database

Implementation steps

• Back up the new main library

Note the backup script should be backed up to the server's local disk instead of with the library.

rman_backup.sh backing up local scripts:

#!/bin/sh

#oracle environment ......

Export Oracle_base=/data/oracle/app

Export Oracle_home= $ORACLE _base/oracle/product/11.2.0/dbhome_1

Export Oracle_sid=orcl_stby

Export path= $PATH: $HOME/bin: $ORACLE _home/bin

Export Ld_library_path= $ORACLE _home/lib:/usr/lib

Export Nls_lang=american_america. Al32utf8

day= ' Date-u +%y%m%d '

Cd/data/bak/rman_backup

Rman Target/nocatalog Log=/data/bak/rman_backup/rman_backup$day.log <<eof

Crosscheck Archivelog All;

Crosscheck backup;

Delete noprompt expired archivelog all;

Delete noprompt expired backup;

run{ Allocate channel c1 type disk;

Allocate channel C2 type disk;

Backup database format '/data/bak/rman_backup/%d_full_%t%s%p.bck ';

SQL "alter system archive log current";

Backup Archivelog All format '/data/bak/rman_backup/%d_arc_%t%s%p.bck ';

Backup current Controlfile format = '/data/bak/rman_backup/controlfile%t%s%p.bck ';

Release channel C1;

Release channel C2;

}

Exit

Eof

• Delete a master library

After this step, the following steps are agreed to change to the original repository called "standby", the new main library called the "Main library."

1. Close the database;

Sql>shutdown immediate;

2. Re-open the database in restrict mode and boot to mount State;

Sqlplus/as SYSDBA

Sql>startup Restrict Mount; -# Only users with SYSDBA role permissions can log in to the database, and normal users cannot (prevent other users from accessing the database)

3. Re-confirm the database name to prevent accidental deletion, this is the ORCL of the minor deletion;

Sql>select name from V$database;

4. Use the DROP DATABASE statement;

Sql>drop database; -# (applicable for 10g and later versions)
# It only deletes the database files (control files, data files, log files, spfile), but does not delete the $oracle_base/admin/$ORACLE _sid directory files will not delete the initialization parameter files and password files, the archive log will not be deleted.

sql> shutdown 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.1.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.1.0 Production on Wed 23 14:52:03 2017

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

Connected to an idle instance.

Sql> startup restrict mount;

ORACLE instance started.

Total System Global area 6747725824 bytes

Fixed Size 2213976 bytes

Variable Size 5100275624 bytes

Database buffers 1610612736 bytes

Redo buffers 34623488 bytes

Database mounted.

Sql> select name from V$database;

NAME

---------

ORCL

sql> drop database;

Database dropped.

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

With the partitioning, OLAP, Data Mining and Real application testing options

Sql> exit

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.1.0 Production on Wed 23 14:56:20 2017

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

Connected to an idle instance.

Sql>

• Prepare the library for startup Nomount

Prepare the Pfile configuration file, preferably the pfile created when the Dataguard was originally built.

Note change the pfile to the Init$oracle_sid.ora format (Initorcl.ora)and put it in the/data/oracle/app/oracle/product/11.2.0/dbhome_1/ Under the dbs/directory:

Sql>startup Nomount;

• Rman connects to the main and standby libraries

Before you perform an Rman connection, verify that the following items are problematic:

1. Firewall shutdown

2.tnsnames.ora, the respective server must be able to listen to each other

3.sys Password Best Consistent

4.db_file_name_convert and Log_file_name_convert, if the directory is inconsistent, pfile need to develop these two parameters

These items are not affected in the production environment because Dataguard have been built before.

Rman target sys/[email protected]_stby Auxiliary sys/[email protected]

Rebuilding the standby database using the duplicate command

Because the path to the master repository is the same, use the following command:

Rman>duplicate target database for standby from active database Nofilenamecheck;

• Validating the database

To open the Standby library:

Sql>alter database open; #这一步可能报错, for the time being, test whether you can open

Sql>create SPFILE from pfile= '/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora ';

Sql>select status from V$instance;

Sql>select Open_mode from V$database;

To view the main library:

Sql>select status from V$instance;

Sql>select Open_mode from V$database;

View Gap_status

Sql>select STATUS, gap_status from v$archive_dest_status WHERE dest_id = 2;

If the status is defer

Sql>alter SYSTEM SET log_archive_dest_state_2= ' ENABLE ' Scope=both;

To start a live synchronization:

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

Sql>select process,thread#,status from V$managed_standby;

Sql>select sequence#,applied from V$archived_log;

Sql>select switchover_status from V$database;

• Restoring DMGRL Relationships

Dgmgrl>show database verbose ORCL;

Query database state or Databases Status:shutdown

Log in to the repository and start Dg_broker:

Sql> Show parameter Dg_broker_start;

NAME TYPE VALUE

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

Dg_broker_start Boolean FALSE

Sql> alter system Set Dg_broker_start = True Scope=both;

System altered.

Sql>!ps-ef|grep Dmon

• Left unanswered

The test lasted only 3 hours, resulting in the new archive of 15 archived logs, Duplicat completed, enabled Log_archive_dest_state_2, only 6 recovered, although log indicators check no problem, the database can open, But is there a consistency problem with the data?

Production environment because one hours an archive, the entire operation can be completed in 3 hours, so there is no need to worry about the log missing issues.

• The production process formally implements new discoveries and solves problems

1. Production implementation of the discovery of the main library log_archive_dest_2 state is inactive, should be the last failover after not complete, so that the main library is lost Log_archive_dest_2

Sql> SELECT STATUS, gap_status from v$archive_dest_status WHERE dest_id = 2;

STATUS Gap_status

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

INACTIVE

Then execute the following SQL to replace the log_archive_dest_2 parameter:

Alter system set log_archive_dest_2= ' Service=orcl lgwr SYNC valid_for= (online_logfiles,primary_role) db_unique_name= Orcl ' Scope=both;

Gap status becomes resolvable gap, after switching logs, it becomes no gap.

2.BROKER Primary and Standby database state configuration is not correct, need to rebuild broker

A. Deleting the original configuration

DISABLE Fast_start FAILOVER Force;

(1) on the viewer

Disable configuration;

Remove Database ORCL;

Remove Database Orcl_stby;

Remove configuration;

(2) on two libraries

alter system Set Dg_broker_start = False Scope=both;

Show parameter broker;

Rename the/data/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/under the

Dr1orcl_stby.dat and Dr2orcl_stby.dat files

(3) on two libraries

alter system Set Dg_broker_start = True Scope=both;

B. Rebuilding the configuration

Dgmgrl> Create configuration DG_ORCL as primary database is Orcl_stby connect identifier is orcl_stby;

dgmgrl> Add database ORCL as connect identifier is ORCL maintained as physical;

Dgmgrl> Show Database Orcl_stby;

Dgmgrl> Show Database ORCL;

Dgmgrl> Show database verbose orcl_stby;

dgmgrl> Edit Database ' ORCL ' Set property ' archivelagtarget ' = ' 0 ';

dgmgrl> Edit Database ' ORCL ' Set property ' logarchiveminsucceeddest ' = ' 1 ';

dgmgrl> Edit Database ' Orcl_stby ' Set property ' delaymins ' = ' 0 ';

dgmgrl> Edit Database ' ORCL ' Set property ' delaymins ' = ' 0 ';

dgmgrl> enable configuration;

Dgmgrl> show configuration;

C. Enable Fast_start FAILOVER

Dgmgrl> EDIT CONFIGURATION SET property faststartfailoverlaglimit=1800;

Dgmgrl> EDIT CONFIGURATION SET Property Faststartfailoverthreshold = 15;

gmgrl> EDIT DATABASE orcl_stby SET Property faststartfailovertarget= ' ORCL ';

Property "Faststartfailovertarget" updated

dgmgrl> EDIT DATABASE orcl SET Property faststartfailovertarget= ' Orcl_stby ';

Property "Faststartfailovertarget" updated

SHOW DATABASE ORCL Logxptmode

SHOW DATABASE Orcl_stby Logxptmode

EDIT DATABASE ORCL SET Property logxptmode= ' SYNC ';

EDIT DATABASE orcl_stby SET Property logxptmode= ' SYNC ';

EDIT CONFIGURATION SET PROTECTION MODE as maxavailability;

ENABLE Fast_start FAILOVER;

SHOW Fast_start FAILOVER;

SHOW CONFIGURATION VERBOSE;

Rebuilding Oracle Dataguard Standby database with Rman duplicate