Oracle DataGuard Setting

Reference : DataGuard Configuration USING RMAN

• oracle online document : http://download.oracle.com/docs/cd/E11882_01/server.112/e10700/create_ps.htm#i63561
• http://www.oracle.com/technology/obe/11gr1_db/ha/dataguard/physstby/physstdby.htm#t2
• http://hungrydba.com/DataGuard_via_RMAN.aspx

Reference : DataGuard Broker Configuration USING DBMGRL

• http://download.oracle.com/docs/cd/E11882_01/server.112/e10702/toc.htm

Reference : Duplicating Database

• http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV423

Backup & Recovery 構築

• Backup & Recovery POLICY
• Backup & Recovery METHOD - RMAN
• Duplicating Database Script - Daily Execute
• Maintenance for Flashback database

New Features

• DBMS_CONNECTION_POOL 
• RMAN
• RESOURCE MANAGER
• Automatic Client Failover
• DBMS_NETWORK_ACL_ADMIN

DataGuard 構築 (PHYSICAL STANDBY)- SUICA : SUICAPR,SUICASB

1. OS User Setting

2. CreateDB.sql create controlfile

3. initSUICAPR.ora & initSUICASB.ora

initTOMATPR.ora & initTOMATOSB.ora

4. ORACLE PASSWORD FILE CREATION

5. ORA*Net Files : listener.ora, tnsnames.ora, sqlnet.ora

6. Enable archive log mode - ALTER DATABASE ARCHIVELOG;

7. Enable Force Logging - ALTER DATABASE FORCE LOGGING;

8. Create Standby logfile

9. Copy Files from the Primary System to the Standby System Using RMAN

10. Configure DataGuard Broker

convert to snapshot stnadby

★★★★★11. Configure Protection Mode & Fast-Start Failover , Observer Start

★★★★★ Standby Database Rebuild ★★★★★

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

ØPreparing the Primary Database for Standby Database Creation - 준비작업

üEnable Forced Logging

üConfigure Redo Transport Authentication

üConfigure the Primary Database to Receive Redo Data

üSet Primary Database Initialization Parameters

üEnable Archiving 

 

ØStep-by-Step Instructions for Creating a Physical Standby Database - 생성

üCreate a Backup Copy of the Primary Database Datafiles

üCreate a Control File for the Standby Database

üCreate a Parameter File for the Standby Database

üCopy Files from the Primary System to the Standby System

üSet Up the Environment to Support the Standby Database

üStart the Physical Standby Database

üVerify the Physical Standby Database Is Performing Properly

 

 

ØPost-Creation Steps – 자동 Failover 를 위한 Database Mode 변경

üUpgrade the data protection mode - Maximum availability

üEnable Flashback Database

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

Scenario 1: DataGuard Broker Configuration 생성, 추가

Scenario 2: Configuration Setting – Edit Database Properties 설정

Scenario 3: Enabling the Configuration and Databases – Configuration 적용

Scenario 4: Setting the Configuration Protection Mode - Maxavailability

Scenario 5: Fast-Start Failover 적용 및 Observer Start

Scenario 6: Routine Management Tasks 수행

Scenario 7: Performing a Switchover Operation – Switchover Test

Scenario 8: Performing a Manual Failover Operation – Failover Test

Scenario 9: Reinstating a Failed Primary Database – Primary Database 재구성

Scenario 10: Converting a Physical Standby to a Snapshot Standby

################################################################

•  

############################################################################## 

----- ----- ----- ----- ----- SUMMARY : Create Physical Standby Database Using RMAN ----- ----- ----- ----- -----

## CREATE STANDBY LOGFILE (PRIMARY)

. .bash_profile1

 ALTER DATABASE ADD STANDBY LOGFILE
   ('/oracle/oradata/first/slog1.rdo') SIZE 10485760;
ALTER DATABASE ADD STANDBY LOGFILE
   ('/oracle/oradata/first/slog2.rdo') SIZE 10485760;
ALTER DATABASE ADD STANDBY LOGFILE
   ('/oracle/oradata/first/slog3.rdo') SIZE 10485760;

## RMAN FULL BACKUP (PRIMARY)

. .bash_profile1

backup database include current controlfile for standby plus archivelog;

## CREATE STANDBY DATABASE (SECOND)

sqlplus sys/oracle@second

startup nomount pfile='initSECOND.ora'

. .bash_profile1

rman

connect target /

connect auxiliary sys/oracle@second

run {   allocate channel prmy1 type disk;  
 allocate channel prmy2 type disk; 
 allocate channel prmy3 type disk;  
 allocate channel prmy4 type disk;  
 allocate auxiliary channel stby type disk;
 duplicate target database for standby from active database    
 spfile      
  parameter_value_convert 'first','second'      
  set db_unique_name='second'      
  set db_file_name_convert='/first/','/second/'      
  set log_file_name_convert='/first/','/second/'
  set control_files='/arch1/second/control1.ctl', '/arch2/second/control2.ctl'
  set log_archive_max_processes='5'    
  set fal_server='first'      
  set standby_file_management='AUTO'      
  set log_archive_config='dg_config=(first,second)'     
  set log_archive_dest_1='LOCATION=/arch1/second/   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=second'   
  set LOG_ARCHIVE_DEST_2='SERVICE=first ASYNC   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=first'
     ;
     }

## standby database  into managed standby mode:

sqlplus sys/oracle@second

SQL> alter database recover managed standby database disconnect;

----- ----- ----- ----- ----- END SUMMARY  ----- ----- ----- ----- -----

 ####################################################################################

Put the DR database into managed standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:

 

On Primary Server:

SQL> alter database commit to switchover to standby;

 

This may cause the following error to be generated:

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

If this does occur then restart the database, as below, before retrying the above command:

SQL> shutdown immediate

SQL> startup

 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

 

The primary server is now configured as a DR standby database.

 

On DR Server:

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

 

The DR server is now configured as the primary database.

To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows:

 

SQL> alter database recover managed standby database cancel;

SQL> alter database activate standby database;

SQL> shutdown immediate

SQL> startup

 

The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

 

Opening the Standby Database in Read Only Mode

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary.

 

On standby server:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

 

The standby database is now open and available for querying in read only mode.

To put the standby database back into standby mode:

 

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

---------- DGMGRL BASE Commands--------------------------

show configuration

switchover to second

failover to second

show fast_start failover

enable fast_start failover (disable)

start observer (stop)

--How to Re Instate Database (dgmgrl)

. .bash_profile1

dgmgrl>startup mount

. .bash_profile2

dgmgrl>reinstate database first

#######################################################################

#################### TROUBLESHOOT ######################################

• troubleshoot --12537

-- password file 생성 후 remote 접속 테스트

test) sqlplus oracle@FIRSTsys/xxx@suicapr as sysdba

test) sqlplus sys/xxx@suicasb as sysdba

12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.
// *Action: None needed; this is an information message.
 

=> confirm listener status

• troubleshoot --1031

-- password file 생성 후 remote 접속 테스트

sqlplus '/as sysdba'

ERROR:
ORA-01031: insufficient privileges

<answer>

=> confirm sqlnet.ora

# sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /oracle
#SQLNET.AUTHENTICATION_SERVICES = (NONE)
#SQLNET.AUTHENTICATION_SERVICES = (NTS) 

DGMGRL> disable configuration
Error: ORA-16654: fast-start failover is enabled

Failed.
DGMGRL> disable fast_start failover;
Error: ORA-16652: fast-start failover target standby database is disabled

Failed.
DGMGRL> disable fast_start failover force;
 

• troubleshoot --16191

-- primary open 이후 remote 연결 상태 자동 체크

ORA-16191: Primary log shipping client not logged on standby
PING[ARC2]: Heartbeat failed to connect to standby 'SECOND'. Error is 16191.

<answer>

lsnrctl reload

or

Modify SQL*Net file (sqlnet.ora, tnsnames.ora, listener.ora)

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

RMAN 사용중

ORA-17628: Oracle error 19505 returned by remote Oracle server

[oracle@suica-1 DBA]$ oerr ora 17628
17628, 00000, "Oracle error %s returned by remote Oracle server"
// *Cause:  Oracle server on the remote instance has returned an error.
// *Action: Look at remote instance alert log/trace file for more information
//          and take appropriate action or contact Oracle Support Services
//          for further assistance
[oracle@suica-1 DBA]$ oerr ora 19505
19505, 00000, "failed to identify file \"%s\""
// *Cause:  call to identify the file returned an error
// *Action: check additional messages, and check if the file exists
 

stand by file destination 확인

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

backup database include current controlfile for standby plus archivelog;

RMAN-06059: expected archived log not found, lost of archived log
compromises recoverability
ORA-19625: error identifying file /u02/oradata/scr9/archive/1_1160.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

<answer>

Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:
 

change archivelog all crosscheck;