Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to build DG", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to build DG" this article.

I. main library

1 archiving mode

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

2 mandatory log

SQL > alter database force logging

Database altered.

SQL > select force_logging from v$database

FOR

-

YES

3 add standby log

The number and size of standby logfile should be the same as that of redo logfile

SQL > select thread#,group#,members,bytes/1024/1024 from v$log

THREAD# GROUP# MEMBERS BYTES/1024/1024

--

1 1 1 50

1 2 1 50

1 3 1 50

SQL > col MEMBER for A25

SQL > select * from v$logfile

GROUP# STATUS TYPE MEMBER IS_

3 ONLINE / oradata/orcl/redo03.log NO

2 ONLINE / oradata/orcl/redo02.log NO

1 ONLINE / oradata/orcl/redo01.log NO

You can see from the figure that our main library has three sets of redo logfile with a size of 50m, so we also need to create the same number and size of standby logfile:

SQL > alter database add standby logfile group 11 ('/ oradata/orcl/stb01.log') size 50m

Database altered.

SQL > alter database add standby logfile group 12 ('/ oradata/orcl/stb02.log') size 50m

Database altered.

SQL > alter database add standby logfile group 13 ('/ oradata/orcl/stb03.log') size 50m

Database altered.

SQL > select group#,THREAD#,SEQUENCE#,ARCHIVED,STATUS from v$standby_log

GROUP# THREAD# SEQUENCE# ARC STATUS

-

11 0 0 YES UNASSIGNED

12 0 0 YES UNASSIGNED

13 0 0 YES UNASSIGNED

4 set the usage mode of the database password file

Check whether the value of remote_login_passwordfile is EXCLUSIVE

SQL > show parameter remote_login_passwordfile

NAME TYPE VALUE

-

Remote_login_passwordfile string EXCLUSIVE

If not, execute the following command to set it up and restart the database for it to take effect:

SQL > alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile

SQL > shutdown immediate

SQL > startup

5 Parameter (file) settin

SQL > show parameter db_unique_name

The construction of DG needs to modify many database parameters, and some of the parameters are somewhat different between the master and slave libraries, so you need to be more careful in the configuration process.

NAME TYPE VALUE

-

Db_unique_name string orcl

SQL > alter system set log_archive_config='dg_config= (orcl,orcls) 'scope=spfile

System altered.

-- where dg_config enters the db_unique_name of the master / slave database.

Modify the location of archive files

SQL > show parameter db_recovery_file_dest

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_

Area

Db_recovery_file_dest_size big integer 4182M

Set the local archive location. The parameter involves switching

Alter system set log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for= (all_logfiles,all_roles) db_unique_name=orcl' scope=spfile

Alter system set log_archive_dest_2='SERVICE=orcls ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcls' scope=spfile

Enable the log path of the setting

SQL > alter system set log_archive_dest_state_1=enable scope=spfile

SQL > alter system set log_archive_dest_state_2=enable scope=spfile

Set the maximum number of archive log processes (adjusted according to the actual situation):

SQL > alter system set log_archive_max_processes=30 scope=both

Set the database from which the standby library gets the archive logs (valid only for the standby library, which is set on the primary database so that it can be used as a standby database after failover):

SQL > alter system set fal_server=orcls scope=both

Set the file management mode to automatic, otherwise the slave database will not be created automatically after the data file is created by the main database:

SQL > alter system set standby_file_management=auto scope=spfile

Enable the OMF feature:

SQL > alter system set db_create_file_dest='/oradata/orcl' scope=spfile

-- if the storage paths of master and slave database files are different, you also need to set the following two parameters (database restart is required to take effect):

SQL > alter system set db_file_name_convert='/data/oradata/orcls/datafile','/data/oradata/orcl/datafile','/data/oradata/orcls/tempfile','/data/oradata/orcl/tempfile' scope=spfile

SQL > alter system set log_file_name_convert='/data/oradata/orcls/redo','/data/oradata/orcl/redo' scope=spfile

The order of this step is not the same in the main and standby database settings, we should pay attention!

Second, set up the parameters of the reserve database.

After completing the above steps, generate a pfile file for the slave database to use with the following command:

SQL > create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora' from spfile

File created.

Open the generated file and modify some parameters, as shown below:

-after revision

* .audit_file_dest='/u01/app/oracle/admin/orcls/adump'

* .audit_trail='db'

* .compatible='11.2.0.4.0'

* .control_files='/oradata/orcls/control01.ctl','/u01/app/oracle/fast_recovery_area/orcls/control02.ctl'

* .db_block_size=8192

* .db_create_file_dest='/oradata/orcls'

* .db_domain=''

* .db_name='orcl'

* .db_unique_name='ocrls'

* .db_recovery_file_dest_size=4385144832

* .db_recovery_file_dest=''

* .diagnostic_dest='/u01/app/oracle'

* .dispatchers=' (PROTOCOL=TCP) (SERVICE=orclsXDB)'

* .log_archive_config='dg_config= (orcl,orcls)'

* .log_archive_dest=''

* .log_archive_dest_1='LOCATION=/oradata/arch/archivelog valid_for= (all_logfiles,all_roles) db_unique_name=orcls'

* .log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

* .log_archive_dest_state_1='ENABLE'

* .log_archive_dest_state_2='ENABLE'

* .log_archive_format='%t_%s_%r.arch'

* .memory_target=780140544

* .open_cursors=300

* .processes=150

* .remote_login_passwordfile='EXCLUSIVE'

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

C) password file configuration

The password file is an indispensable part of creating DG. The password file of the main library is generally $ORACLE_HOME/dbs, and the naming format is: orapw+db_unique_name.

If this file does not exist, we can generate one with the following command:

# su-oracle

$cd $ORACLE_HOME/dbs

$orapwdfile=orapwocrl password=oracle

We copy the password file and the modified pfile to the $ORACLE_HOME/dbs directory of the repository, and rename the password file:

Modify password file name and parameter file on standby library

5.listener.ora and tnsnames.ora configuration

Both files are in the $ORACLE_HOME/network/admin directory. If you don't have them, you can create them yourself.

A) prepare the library configuration

The listener.ora content is as follows:

LISTENER=

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcls)

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcls)

)

)

The tnsnames.ora content is as follows:

Orcl =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

Orcls =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcls)

)

)

Restart the monitor:

$lsnrctl stop

$lsnrctl start

B) configuration of the main library

The listener.ora content is as follows:

LISTENER=

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ocrls)

(ORACLE_HOME = / u01/app/oracle/product/12.1.0/db_1)

(SID_NAME = ocrls)

)

)

The tnsnames.ora content is as follows:

Orcl =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

Orcls =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcls)

)

)

Execute the following command to restart monitoring for the configuration to take effect:

$lsnrctl stop

$lsnrctl start

After the above configuration, execute the following command on the primary and standby database to ensure that the network between the two hosts is connected:

$tnsping orcls

$tnsping orcls

6. Catalog creation

After the parameters and network are configured, we need to create a directory for the slave dump file (against the main library $ORACLE_BASE/admin):

[oracle@node2 ~] $echo $ORACLE_BASE

/ u01/app/oracle

[oracle@node2] $mkdir-p $ORACLE_BASE/admin/orcls/adump

[oracle@node2] $mkdir-p $ORACLE_BASE/admin/orcls/dpdump

Create a directory for the database files (that is, the previous directories for db_file_name_convert and log_file_name_convert)-/ oradata

Ocrls:/data/oradata/orls@standby > mkdir-p/data/oradata/ocrls/redo/

Ocrls:/data/oradata/ocrls@standby > mkdir-p/data/oradata/ocrls/datafile/

Ocrls:/data/oradata/ocrls@standby > mkdir-p / data/oradata/ocrls/control/

7.RMAN replication creates a standby library

Now that the preparations are complete, we can start the creation of the standby library.

Note: the following operations are completed in the preparation database.

A) File replication

First, we use the previously modified pfile to start the slave library to nomount state to generate spfile:

$echo $ORACLE_SID (confirm whether SID is set by us)

SQL > startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcls.ora'

ORACLE instance started.

Total System Global Area 776646656 bytes

Fixed Size 2257272 bytes

Variable Size 507514504 bytes

Database Buffers 264241152 bytes

Redo Buffers 2633728 bytes

SQL > create spfile from pfile

File created.

SQL > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL > exit

Launch from spfile

SQL > STARTUP NOMOUNT

SQL > show parameter db_unique_name

NAME TYPE VALUE

-

Db_unique_name string orcls

SQL > show parameter name

NAME TYPE VALUE

-

Cell_offloadgroup_name string

Db_file_name_convert string

Db_name string orcl

Db_unique_name string orcls

Global_names boolean FALSE

Instance_name string orcls

Lock_name_space string

Log_file_name_convert string

Processor_group_name string

Service_names string orcls

SQL >

Copy data files and operate on the standby library

[oracle@node2 dbs] $rman target sys/oracle@orcl auxiliary sys/oracle@orcls

Recovery Manager: Release 11.2.0.4.0-Production on Fri Jun 15 00:33:22 2018

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

Connected to target database: ORCL (DBID=1506854844)

Connected to auxiliary database: ORCL (not mounted)

RMAN >

After confirming that we have connected the main library and standby library, execute the following command:

If you do not specify the nofilenamecheck parameter when RMAN is restored

Then a RMAN-05501 error occurs when the data file is restored with the same file name

RMAN > duplicate target database for standby from active database nofilenamecheck

After the command is executed, you can see that the main library begins to copy files to the standby library

After the replication is complete, open the database to enable real-time synchronization:

SQL > ALTER DATABASE ARCHIVELOG

SQL > ALTER DATABASE OPEN

SQL > ARCHIVE LOG LIST

SQL > alter database recover managed standby database using current logfile disconnect from session

View database status

Log in to the main library

$sqlplus / as sysdba

SQL > select database_role from v$database

DATABASE_ROLE

-

PRIMARY

Log in to the standby library:

$sqlplus / as sysdba

SQL > select database_role from v$database

DATABASE_ROLE

-

PHYSICAL STANDBY

Check that the archive log can be transferred properly (the sequence number of the log must be the same):

Main library

SQL > select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

2 17-JUN-18 18-JUN-18 NO YES

3 18-JUN-18 18-JUN-18 NO YES

4 18-JUN-18 18-JUN-18 NO YES

5 18-JUN-18 18-JUN-18 NO YES

6 18-JUN-18 19-JUN-18 NO YES

7 19-JUN-18 19-JUN-18 NO YES

8 19-JUN-18 19-JUN-18 NO YES

9 19-JUN-18 19-JUN-18 NO YES

10 19-JUN-18 19-JUN-18 NO YES

11 19-JUN-18 19-JUN-18 NO YES

11 19-JUN-18 19-JUN-18 YES YES

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

12 19-JUN-18 19-JUN-18 NO YES

12 19-JUN-18 19-JUN-18 NO YES

13 rows selected.

Prepare the library

SQL > select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

11 19-JUN-18 19-JUN-18 YES YES

12 19-JUN-18 19-JUN-18 IN-MEMORY YES

B) switch log tests

Main library

SQL > alter system switch logfile

System altered.

SQL > select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

2 17-JUN-18 18-JUN-18 NO YES

3 18-JUN-18 18-JUN-18 NO YES

4 18-JUN-18 18-JUN-18 NO YES

5 18-JUN-18 18-JUN-18 NO YES

6 18-JUN-18 19-JUN-18 NO YES

7 19-JUN-18 19-JUN-18 NO YES

8 19-JUN-18 19-JUN-18 NO YES

9 19-JUN-18 19-JUN-18 NO YES

10 19-JUN-18 19-JUN-18 NO YES

11 19-JUN-18 19-JUN-18 NO YES

11 19-JUN-18 19-JUN-18 YES YES

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

12 19-JUN-18 19-JUN-18 NO YES

12 19-JUN-18 19-JUN-18 NO YES

13 19-JUN-18 19-JUN-18 NO YES

13 19-JUN-18 19-JUN-18 NO YES

15 rows selected.

Prepare the library

SQL > select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

11 19-JUN-18 19-JUN-18 YES YES

12 19-JUN-18 19-JUN-18 IN-MEMORY YES

SQL > select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC

11 19-JUN-18 19-JUN-18 YES YES

12 19-JUN-18 19-JUN-18 YES YES

13 19-JUN-18 19-JUN-18 IN-MEMORY YES

SQL > select max (sequence#) from v$archived_log

SQL > select max (sequence#) from v$archived_log

MAX (SEQUENCE#)

-

thirteen

These are all the contents of the article "how to build DG". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.