Detailed steps and precautions for building a single instance DG for two Oracle 11g nodes RAC

Detailed steps and precautions for building a single instance DG for two Oracle 11g nodes RAC

Environment Introduction:

OS: [root @ java3 ~] # Uname-

Linux java3 2.6.18-308. el5 #1 SMP Tue Feb 21 20:06:06 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

Master Database: database version: RAC Node 1 of two nodes 11.2.0.3.0: 192.168.15.26 Node 2: 192.168.15.27

Standby database version: 11.2.0.3.0 IP 192.168.15.9

Only the database software is installed on the slave database, and no database is created.

Procedure:

1. Install the database software on 192.168.15.9.

Ii. Modify master database Parameters

The parameters can be found in the example in the 11g Official manual book list -- DAT -- Data Guard Concepts and Administration -- 3.1 Preparing the Primary Database for Standby Database Creation -- 3.1.4 to be included in the parameter file of the master Database. parameter (if no value is added ), as follows:

DB_NAME = chicago # Name of the master database
DB_UNIQUE_NAME = chicago # The Database Name of the master database. The slave database also has its own unique database name.
LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (chicago, boston) '# Note the location of the master and slave databases in brackets
CONTROL_FILES = '/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl' # this parameter is not required for the master database, the standby database must be changed to its own path LOG_ARCHIVE_DEST_1 = # this parameter is used to specify the location of local (master database) log files archived.
'Location =/arch1/chicago/
VALID_FOR = (ALL_LOGFILES, ALL_ROLES)
DB_UNIQUE_NAME = chicago # the unique name of the standby Database
LOG_ARCHIVE_DEST_2 = # this parameter is used as the master database to transmit online logs to the database's only standby database named boston.
'Service = boston ASYNC # asynchronous transmission
VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)
DB_UNIQUE_NAME = boston # enter the name of the standby database here,
LOG_ARCHIVE_DEST_STATE_1 = ENABLE # ENABLE log transmission.
LOG_ARCHIVE_DEST_STATE_2 = ENABLE
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE # When the remote logon password file is used to verify the management user or redo the transfer session, it must be set to exclusive or shared
LOG_ARCHIVE_FORMAT = % t _ % s _ % r. arc # set the format of the archived Log File

FAL_SERVER = boston # enter the Database Name of the slave database. This parameter is only useful when used as the standby database. It means that the slave database uses the Database Name of the specified master database as the server, and obtain the request. For this parameter, if you do not intend to convert the master database to the slave database, you do not need to set this parameter in the master database.
DB_FILE_NAME_CONVERT = '/backup/', '+ data/jlprojct/datafile/' # this parameter is a static parameter, the function of recovere during duplicate execution in the master database is to convert the data file location of the master database to the appropriate location of the slave database specified here. The test is based on the parameter change of the slave database, it has no relationship with the master database. That is to say, executing duplicate in the master database will generate data files in the slave database based on the location specified by this parameter in the slave database. If you add a new data file to the master database, you must create a data file in the corresponding location of the slave database.
LOG_FILE_NAME_CONVERT = # similar to the previous parameter. When you modify this parameter, if the log file of your master database has several different paths, you must write them in,
'/Arch1/boston/', '/arch1/chicago/', '/arch2/boston/', '/arch2/chicago /'

STANDBY_FILE_MANAGEMENT = AUTO # This parameter indicates that after you set it to auto, you will automatically perform operations in the slave database to add or delete data files in the master database, do not overwrite existing data files. Do not use the same name.

 

If the master database cannot be stopped (that is, the master database cannot be restarted), you can use the following commands to modify the parameters of the master database. After modification, create pfile. ora = '/backup/pfile. ora from spfile ='/Oracle/product/11.2.3/dbs/initJLPROJCT1.ora ';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'location = + arch/jlprojct/archivelog VALID_FOR = (ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME = JLPROJCT ';

 

 

System altered.

 

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'service = JLPROJCT3 ASYNC VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME = JLPROJCT3 ';

 

System altered.

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = ENABLE scope = spfile;

 

System altered.

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope = spfile;

 

System altered.

 

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE scope = spfile;

 

System altered.

 

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT = '% t _ % s _ % r. arc 'scope = spfile; # static parameter. It does not work because it does not restart or change it.

 

System altered.

 

SQL> ALTER SYSTEM SET FAL_CLIENT = JLPROJCT SCOPE = SPFILE; # fal_client is the setting itself, and fal_server is the other party
 

 

 

System altered.

 

SQL> ALTER SYSTEM SET FAL_SERVER = JLPROJCT3 SCOPE = SPFILE;

 

System altered.

 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO SCOPE = SPFILE;

 

System altered.

 

 

 

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT = '/backup/', + data/jlprojct/datafile/'scope = spfile; # static parameters, no restart, no effect, so don't restart it.

 

System altered.

 

 

 

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT = '+ DATA/jlprojct/onlinelog/', '/backup/', '+ ARCH/jlprojct/onlinelog /', '/backup/' scope = spfile; # static parameter. It does not restart and does not take effect. Therefore, you cannot restart it.

 

System altered.

3. Modify the listener and tnsname. ora file of the master database.

1. The master database listens to the added listener. ora file (if any)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = JLPROJCT)

(ORACLE_HOME =/oracle/product/11.2.3)

(SID_NAME = JLPROJCT1 )))

 

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)
 
2. Add information about the slave database to the master database tnsname. ora.

JLPROJCT3 = # slave database information

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = JLPROJCT3)

(INSTANCE_NAME = JLPROJCT3)

)

)

Db26 = # Before executing duplicate, rman must connect the master database and the slave database at the same time. This connection string is used when the master database is connected.

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = JLPROJCT)

 

4. Use scp to set the listener in the master database. ora, tnsname. ora, initJLPROJCT1.ora, pfile. ora sends the parameter file to the slave database and then modifies it accordingly (the parameter file does not need to be modified, but the name must be changed to the same as the backup database instance name: [oracle @ java3 dbs] $ mv orapwJLPROJCT orapwJLPROJCT3)

[Oracle @ rac1 ~] $ Scp listener. ora tnsnames. ora initJLPROJCT1.ora oracle@192.168.15.: 'pwd'; # The pwd after the colon means to upload the file to the current path of the slave database (that is, the path displayed by pwd on the host at this time)

Change the listening information to the standby database, and add information about the primary database to tnaname. ora.

1. the standby database listens to listener. ora as follows:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = JLPROJCT3)

(ORACLE_HOME =/opt/oracle/product/11.2.0/dbhome_1)

(SID_NAME = JLPROJCT3 )))

 

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.15.9) (PORT = 1521) # HOST IP

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

)

)

ADR_BASE_LISTENER =/opt/oracle

2. Content of the slave database tnsname. ora:

JLPROJCT = # The connection string in the master database is different from that in tnsname. ora. It does not affect the name.

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = JLPROJCT)

JLPROJCT3 = # do not fill in the relevant information of the standby database if it is only a standby database.

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = JLPROJCT3)

(INSTANCE_NAME = JLPROJCT3)

)

)

Modify the parameter file of the slave database:

DB_NAME = JLPROJCT # It remains unchanged because it is a database.

DB_UNIQUE_NAME = JLPROJCT3 # the unique name of the slave database must be written here. It is used to distinguish the master database from the slave database and is not renamed even if the role is switched.

LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = (JLPROJCT3, JLPROJCT) '# The front and back positions in the brackets and in the master database brackets are changed.

LOG_ARCHIVE_DEST_1 =

'Location =/opt/oracle # Fill in the directories in the standby database to ensure free space and permission for reading and writing,

VALID_FOR = (ALL_LOGFILES, ALL_ROLES)

DB_UNIQUE_NAME = JLPROJCT3 '# backup database name

LOG_ARCHIVE_DEST_2 =

'Service = jlprojct async # Name of the master database

VALID_FOR = (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME = JLPROJCT '# master database name

LOG_ARCHIVE_DEST_STATE_1 = ENABLE

LOG_ARCHIVE_DEST_STATE_2 = ENABLE

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

LOG_ARCHIVE_FORMAT = % t _ % s _ % r. arc

FAL_SERVER = JLPROJCT # master database name

DB_FILE_NAME_CONVERT = '+ data/jlprojct/datafile/', '/backup/' # change the location of the two paths after the equal sign

LOG_FILE_NAME_CONVERT =

'+ DATA/jlprojct/onlinelog/', '/backup/', '+ ARCH/jlprojct/onlinelog/', '/backup/' # change the location of the two paths after the equal sign

STANDBY_FILE_MANAGEMENT = AUTO
5. On the slave database, create the modified pfile as spfile, rename it, and start the slave database instance to the nomount status SYS @ JLPROJCT3> create pfile from spfile;
File created.

6. Add the standby log group to the master database as follows: (the number of original standby logs in the master database is n + 1) * Number of threads. The number of threads mentioned here can be interpreted as the number of instances, there are 10 log groups, so add (10 + 1) * 2 = 22 groups. It is best to keep the size and path consistent with the original one,
DG has two log transmission methods: one is the common archivr log, which is controlled by the ARCH background process and transmitted to the standby database, and the other is the same as the redo log, controlled by LGWR, the background process that generates the redo log, the latter requires the standby log
In the maximum available and maximum protection modes, standby logfile must be used because lgwr sync is used for redo transfer. However, standby logfile is recommended in the maximum performance mode, it is said that more data can be restored when the switchover fails.

Add the following command:

Alter database add standby logfile group 15 ('+ DATA/jlprojct/onlinelog/group_15') size 2000 M;

6. After the test is successful on the master database, enable the listener on the master database and disable the firewall,

[Oracle @ rac1 ~] $ Tnsping JLPROJCT3

TNS Ping Utility for Linux: Version 11.2.0.3.0-Production on 05-MAR-2015 17:45:23

Copyright (c) 1997,201 1, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.15.9) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = JLPROJCT3) (INSTANCE_NAME = JLPROJCT3 )))
OK (0 msec)

[Oracle @ rac1 ~] $ Tnsping db26

TNS Ping Utility for Linux: Version 11.2.0.3.0-Production on 05-MAR-2015 17:46:43

Copyright (c) 1997,201 1, Oracle. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.15.26) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = JLPROJCT )))
OK (10 msec)
7. Use rman to connect the master database to the slave database at the same time.
[Oracle @ rac1 ~] $ Rman target sys/xxxxx @ db26 auxiliary sys/xxxxx @ JLPROJCT3

Recovery Manager: Release 11.2.0.3.0-Production on Thu Mar 5 17:51:23 2015

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

Connected to target database: JLPROJCT (DBID = 2115662724)
Connected to auxiliary database: JLPROJCT (not mounted)

8. Execute the duplicate command in the master database. The most critical step is to back up the control files, data files, and log files of the master database, then transmit them to the slave database, and recover them in the slave database, if no error is reported, the Standby database is Open.

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

9. Open the standby database instance in read-only mode.

SYS @ JLPROJCT3> alter database open read only;

Database altered.

10. Open the MRP process in the slave database, apply logs, and repeat changes. Note: Make sure that the standby database instance is enabled in the read-only mode. Otherwise, it cannot be synchronized.

SYS @ JLPROJCT3> alter database recover managed standby database disconnect from session;

Database altered.
11: Test Results: create a user in the master database and create a table under the user. It is found that the slave database will also have this, proving that the building of DG is successful.