The simplest 11g Active deployment uard (ADG) configuration process (project steps)

The simplest 11g Active deployment uard (ADG) configuration process (project steps)

I. Environment Introduction:
I first installed a set of database software on the two Linux virtual machines db01 and db02, and created a database named woo on the db01 host; in this experiment, we set up an Oracle 11g Active replica uard to implement Database Synchronization and understand the basic functions of Oracle 11g DG.

Db01: 192.168.1.50
Db02: 192.168.1.51

Oracle 11g Active standby uard

Important configuration parameters of Oracle Data Guard

Configure Oracle 11g Data Guard based on the same host

Explore Oracle 11g elastic uard

Oracle Data Guard (RAC + DG) archive deletion policies and scripts

Role conversion for Oracle Data Guard

FAL gap in Oracle Data Guard logs

Oracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby

Ii. 11g ADG deployment:

1. Configure static listening on pri and sty

[Oracle @ sty admin] $ cat listener. ora
 
# Listener. ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener. ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/DBSoft/oracle/product/11.2.4/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = Woo)

(ORACLE_HOME =/DBSoft/oracle/product/11.2.4/dbhome_1)

)

)

 

[Oracle @ sty admin] $ cat tnsname. ora

# Tnsnames. ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames. ora

# Generated by Oracle configuration tools.

STY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.51) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = woo)

)

)

PRI =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.50) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = woo)

)

)

2. Modify the primary initialization parameter file

Startup mount;
 
Alter database archivelog;

Alter database force logging;

Alter database open;

Alter system set log_archive_config = 'dg _ CONFIG = (pri, sty) 'scope = spfile;

Alter system set log_archive_dest_1 = 'location =/DBBackup/Archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = pri 'scope = spfile;

Alter system set log_archive_dest_2 = 'service = sty lgwr sync VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = sty 'scope = spfile;

Alter system set log_archive_dest_state_1 = ENABLE;

Alter system set log_archive_dest_state_2 = ENABLE;

Alter system set fal_server = sty scope = spfile;

Alter system set fal_client = pri scope = spfile;

Alter system set standby_file_management = AUTO scope = spfile;

3. Copy the pfile parameter file and password file on the primary end to the corresponding location of the standby segment.

SQL> create pfile from spfile;
 

File created.

[Oracle @ db01 dbs] $ scp initwoo. ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs

Oracle@192.168.1.51's password:
Initwoo. ora 100% 1260 1.2KB/s
Orapwwoo 100% 1536 1.5KB/s

[Oracle @ db01 oracle] $ scp-r admin/diag/fast_recovery_area/oradata/192.168.1.51: $ ORACLE_BASE
Oracle@192.168.1.51's password:
Init. ora.512201522543 100% 1778 1.7KB/s
Dp. log 100% 116 0.1KB/s
........

4. Modify the standby listening file and initialization parameter file

-- Modify the listening File
 
[Oracle @ db02 ~] $ Cd $ ORACLE_HOME/network/admin

[Oracle @ db02 admin] $ vi listener. ora

# Listener. ora Network Configuration File:/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener. ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 ))

(ADDRESS = (PROTOCOL = TCP) (HOST = db02) (PORT = 1521 ))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/DBSoft/oracle/product/11.2.4/dbhome_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = woo)

(ORACLE_HOME =/DBSoft/oracle/product/11.2.4/dbhome_1)

(SID_NAME = woo)

)

)

ADR_BASE_LISTENER =/DBSoft/oracle

-- Start the listener

[Oracle @ db02 dbs] $ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0-Production on 17-JUN-2015 21:29:57

Copyright (c) 1991,201 3, Oracle. All rights reserved.

Starting/DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0-Production

System parameter file is/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener. ora

Log messages written to/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log. xml

Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1521 )))

Listening on: (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = db02) (PORT = 1521 )))

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 )))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0-Production

Start Date 17-JUN-2015 21:29:57

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener. ora

Listener Log File/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log. xml

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1521 )))

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = db02) (PORT = 1521 )))

Services Summary...

Service "PLSExtProc" has 1 instance (s ).

Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...

Service "woo" has 1 instance (s ).

Instance "woo", status UNKNOWN, has 1 handler (s) for this service...

The command completed successfully

-- View the listener status

[Oracle @ db02 dbs] $ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0-Production on 17-JUN-2015 21:30:02

Copyright (c) 1991,201 3, Oracle. All rights reserved.

Connecting to (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 )))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0-Production

Start Date 17-JUN-2015 21:29:57

Uptime 0 days 0 hr. 0 min. 4 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File/DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener. ora

Listener Log File/DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log. xml

Listening Endpoints Summary...

(DESCRIPTION = (ADDRESS = (PROTOCOL = ipc) (KEY = EXTPROC1521 )))

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = db02) (PORT = 1521 )))

Services Summary...

Service "PLSExtProc" has 1 instance (s ).

Instance "PLSExtProc", status UNKNOWN, has 1 handler (s) for this service...

Service "woo" has 1 instance (s ).

Instance "woo", status UNKNOWN, has 1 handler (s) for this service...

The command completed successfully

[Oracle @ db02 dbs] $

-- Modify the parameter file

[Oracle @ db02 ~] $ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015

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

Connected to an idle instance.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL> create spfile from pfile = '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo. ora ';

File created.

SQL>

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL>

Alter system set db_unique_name = sty scope = spfile;

Alter system set log_archive_config = 'dg _ CONFIG = (pri, DG) 'scope = spfile;

Alter system set log_archive_dest_1 = 'location =/DBBackup/Archive VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = sty' scope = spfile;

Alter system set log_archive_dest_2 = 'service = pri lgwr sync VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = pri 'scope = spfile;

Alter system set fal_server = pri scope = spfile;

Alter system set fal_client = sty scope = spfile;

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1188511744 bytes

Fixed Size 1364228 bytes

Variable Size 754978556 bytes

Database Buffers 419430400 bytes

Redo Buffers 12738560 bytes

SQL>

SQL>

5. Use Rman Duplicate on the primary side to create a slave database and run the following command on db01:

Rman target sys/oracle @ pri auxiliary sys/oracle @ sty nocatalog
Duplicate target database for standby from active database nofilenamecheck;

[Oracle @ db01 ~] $ Rman target sys/oracle @ pri auxiliary sys/oracle @ sty nocatalog

Recovery Manager: Release 11.2.0.4.0-Production on Wed Jun 17 22:33:54 2015

Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.

Connected to target database: WOO (DBID = 4221729487)
Using target database control file instead of recovery catalog
Connected to auxiliary database: WOO (not mounted)

RMAN>

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 17-JUN-15
Allocated channel: ORA_AUX_DISK_1
Channel ORA_AUX_DISK_1: SID = 19 device type = DISK

Contents of Memory Script:
{
Backup as copy reuse
Targetfile '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' auxiliary format
'/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo ';
}
Executing Memory Script

Starting backup at 17-jun15-15
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 44 device type = DISK
Finished backup at 17-JUN-15

Contents of Memory Script:
{
Backup as copy current controlfile for standby auxiliary format '/DBSoft/oracle/oradata/woo/control01.ctl ';
Restore clone controlfile to '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from
'/DBSoft/oracle/oradata/woo/control01.ctl ';
}
Executing Memory Script

Starting backup at 17-jun15-15
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile copy
Copying standby control file
Output file name =/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag = TAG20150617T223502 RECID = 1 STAMP = 882657308
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 17-JUN-15

Starting restore at 17-jun15-15
Using channel ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-jun15-15

Contents of Memory Script:
{
SQL clone 'alter database mount standby database ';
}
Executing Memory Script

SQL statement: alter database mount standby database

Contents of Memory Script:
{
Set newname for tempfile 1
"/DBSoft/oracle/oradata/woo/temp01.dbf ";
Switch clone tempfile all;
Set newname for datafile 1
"/DBSoft/oracle/oradata/woo/system01.dbf ";
Set newname for datafile 2
"/DBSoft/oracle/oradata/woo/sysaux01.dbf ";
Set newname for datafile 3
"/DBSoft/oracle/oradata/woo/undotbs01.dbf ";
Set newname for datafile 4
"/DBSoft/oracle/oradata/woo/users01.dbf ";
Backup as copy reuse
Datafile 1 auxiliary format
"/DBSoft/oracle/oradata/woo/system01.dbf" datafile
2 auxiliary format
"/DBSoft/oracle/oradata/woo/sysaux01.dbf" datafile
3 auxiliary format
"/DBSoft/oracle/oradata/woo/undotbs01.dbf" datafile
4 auxiliary format
"/DBSoft/oracle/oradata/woo/users01.dbf ";
SQL 'alter system archive log current ';
}
Executing Memory Script

Executing command: SET NEWNAME

Renamed tempfile 1 to/DBSoft/oracle/oradata/woo/temp01.dbf in control file

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Starting backup at 17-jun15-15
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00001 name =/DBSoft/oracle/oradata/woo/system01.dbf
Output file name =/DBSoft/oracle/oradata/woo/system01.dbf tag = TAG20150617T223532
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:58
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00002 name =/DBSoft/oracle/oradata/woo/sysaux01.dbf
Output file name =/DBSoft/oracle/oradata/woo/sysaux01.dbf tag = TAG20150617T223532
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:30
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00003 name =/DBSoft/oracle/oradata/woo/undotbs01.dbf
Output file name =/DBSoft/oracle/oradata/woo/undotbs01.dbf tag = TAG20150617T223532
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00004 name =/DBSoft/oracle/oradata/woo/users01.dbf
Output file name =/DBSoft/oracle/oradata/woo/users01.dbf tag = TAG20150617T223532
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 17-JUN-15

SQL statement: alter system archive log current

Contents of Memory Script:
{
Switch clone datafile all;
}
Executing Memory Script

Datafile 1 switched to datafile copy
Input datafile copy RECID = 1 STAMP = 882658052 file name =/DBSoft/oracle/oradata/woo/system01.dbf
Datafile 2 switched to datafile copy
Input datafile copy RECID = 2 STAMP = 882658052 file name =/DBSoft/oracle/oradata/woo/sysaux01.dbf
Datafile 3 switched to datafile copy
Input datafile copy RECID = 3 STAMP = 882658052 file name =/DBSoft/oracle/oradata/woo/undotbs01.dbf
Datafile 4 switched to datafile copy
Input datafile copy RECID = 4 STAMP = 882658052 file name =/DBSoft/oracle/oradata/woo/users01.dbf
Finished Duplicate Db at 17-JUN-15

# The restoration has been completed.

6. Add standby logs on the primary and standby terminals.

SQL> alter database add standby logfile
 
Group 4 ('/DBSoft/oracle/oradata/woo/styredo04.log') size 50 m,

Group 5 ('/DBSoft/oracle/oradata/woo/styredo05.log') size 50 m,

Group 6 ('/DBSoft/oracle/oradata/woo/styredo06.log') size 50 m,

Group 7 ('/DBSoft/oracle/oradata/woo/styredo07.log') size 50 m;

SQL> SELECT GROUP #, THREAD #, SEQUENCE #, ARCHIVED, STATUS FROM V $ STANDBY_LOG;

GROUP # THREAD # SEQUENCE # ARC STATUS

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

4 0 0 YES UNASSIGNED

5 0 0 YES UNASSIGNED

6 0 0 YES UNASSIGNED

7 0 0 YES UNASSIGNED

7. enable real-time log application on standby

SQL> recover managed standby database using current logfile disconnect from session;
 
Media recovery complete.

SQL>

1 2 3 Next Page