Oracle 11g detailed steps for building a standuard (physical standby all operations)

Oracle 11g detailed steps for building a standuard (physical standby all operations)

Preface:

DataGuard establishes a reference relationship by establishing a PRIMARY and STANDBY group. Once STANDBY is created, DataGuard passes the REDO of the PRIMARY database (PRIMARY) to the STANDBY database, then, apply REDO in STANDBY to synchronize databases.

There are two types of STANDBY: Physical STANDBY and logical STANDBY

Physical STANDBY provides the same copy (Block-to-block) as the primary database, database SCHEMA, and indexes. It directly applies REDO to implement synchronization.

This is not true for logical STANDBY. In logical STANDBY, the logical information is the same, but the physical organization and data structure can be different, it is synchronized with the master database by converting the received REDO statements into SQL statements, and then executing SQL statements on STANDBY. In addition to disaster recovery, logical STANDBY has other functions, such as query and report.

1. installation environment

Set up database software in primary, establish lsnrctl listeners, use dbca to build instances, set up database software on standby, and set up listeners, but do not need to use dbca to create instances.

For how to build an Oracle database on Linux, refer to the previous article:

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

Project List	Primary Library	Standby Library
Operating System:	Centos6.4	Centos6.4
Oracle software version:	Oracle 11.2.0.1.0	Oracle 11.2.0.1.0
IP Address:	192.168.121.217	192.168.121.218
Db_unique_name:	Pdunq	Pdunq_dg

2. Prepare for the operation on primary.
2.1 enable Forced Logging Mode
Check 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>

Forced Archiving
SQL> alter database force logging;
 
Database altered.

SQL>
Confirm that the primary database is in archive Mode

Add a standby File
Alter database add standby logfile group 4 ('/home/oradata/powerdes/redo_dg_021.log') size 20 M;
Alter database add standby logfile group 5 ('/home/oradata/powerdes/redo_dg_022.log') size 20 M;
Alter database add standby logfile group 6 ('/home/oradata/powerdes/redo_dg_023.log') size 20 M;
Alter database drop standby logfile group 4;
Alter database drop standby logfile group 5;
Alter database drop standby logfile group 6;

View the list of all redo files, select * from v $ logfile order by 1;
SQL> select * from v $ logfile order by 1;

1 ONLINE/home/oradata/powerdes/redo03.log NO
2 ONLINE/home/oradata/powerdes/redo02.log NO
3 ONLINE/home/oradata/powerdes/redo01.log NO
4 STANDBY/home/oradata/powerdes/redo_dg_021.log NO
5 STANDBY/home/oradata/powerdes/redo_dg_022.log NO
6 STANDBY/home/oradata/powerdes/redo_dg_023.log NO

6 rows selected.

SQL>

2.3 prepare the parameter file
2.3.1 generate pfile
Create pfile from spfile;
Shutdown immediate

2.3.2 modify pfile
Cp $ ORACLE_HOME/dbs/initpowerdes. ora $ ORACLE_HOME/dbs/initpowerdes. ora. bak
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'

 

2.3.3 generate spfile
Create spfile from pfile;
Startup # It can be started or not started here. If it is not started here, remember startup later to make the new parameter file take effect.

2.4 modify the listening File
[Oracle @ powerlong4 admin] $ vim listener. ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.217) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))
)
)

ADR_BASE_LISTENER =/oracle/app/oracle
INBOUND_CONNECT_TIMEOUT_listener = 10

2.5. Modify the tns configuration file
[Oracle @ powerlong4 admin] $ vim tnsnames. ora
PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.217) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = pdunq)
)
)

SC _SID =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.218) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = powerdes)
(SERVER = DEDICATED)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1 ))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

2.6 restart the listening service
Lsnrctl stop
Lsnrctl start

2.7 configure the maximum available mode on primary:
SQL> startup
SQL> alter database set standby database to maximize availability;

2.8 back up a database
Backup database plus archivelog;
Backup current controlfile for standby;
Exit;
After the backup is complete, a backup file will be generated in the flash back area.

3. Configure standby for the database.
3.1 create a file directory
Including the dump file directory and data file directory, which can be viewed through show parameter dest; to maintain the same path address as primary

3.2 copy data files from primary to standby
Run the following command on the master 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
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/

3.3 modify the configuration file in the standby database on standby
[Oracle @ powerlong5 admin] $ vim listener. ora

# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = powerdes)
(ORACLE_HOME =/oracle/app/oracle/product/11.2.0/dbhome_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.121.218) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0 ))
)
)

Modify the tns file in standby

3.4. Modify the parameter file
*. Db_unique_name = 'pdunq _ dg'
*. Diagnostic_dest = '/oracle/app/oracle'
*. Dispatchers = '(PROTOCOL = TCP) (SERVICE = powerdes )'
*. Fal_client = 'pdunq'
*. Fal_server = 'pdunq _ dg'
*. Standby_file_management = 'auto'
*. Db_file_name_convert = '/home/oradata/powerdes', '/home/oradata/powerdes'
*. 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'

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.

 

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page