Issue background
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
- No need for primary database downtime
- 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
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;
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
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