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

This article mainly introduces "what is the method of building physical DG by Oracle". In daily operation, I believe that many people have doubts about the method of building physical DG by Oracle. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the method of Oracle to build physical DG?" Next, please follow the editor to study!

Prerequisites:

Complete the construction of Oracle stand-alone, Oracle software and database installation.

Complete the installation of Oracle software for Oracle standby.

Please refer to the article:

Http://blog.itpub.net/22996654/viewspace-2149816/

After completing the above installation, start to build the physical DG:

Host IP:172.16.0.21

Standby IP:172.16.0.75

Set up both hosts / etc/hosts

[root@aa ~] # cat / etc/hosts

172.16.0.21 aa

172.16.0.75 bb

In the slave library, all the data directories, log directories and archive directories corresponding to the master library are established.

[oracle@bb db_1] $mkdir-p $ORACLE_BASE/admin/$ORACLE_SID/adump

[oracle@bb db_1] $mkdir-p $ORACLE_BASE/oradata/WMSPROD

[oracle@bb db_1] $mkdir-p $ORACLE_BASE/flash_recovery_area

[oracle@bb db_1] $chown-R oracle:oinstall / usr/local/bin/*

[oracle@bb oradata] $mkdir-p standbylog

[oracle@bb oradata] $ll

Total 8

Drwxr-xr-x 2 oracle oinstall 4096 May 15 15:45 standbylog

Drwxr-xr-x 2 oracle oinstall 4096 May 14 20:25 WMSPROD

Set up monitoring:

Main library:

[oracle@aa admin] $cat listener.ora

# listener.ora Network Configuration File: / data/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

ADR_BASE_LISTENER = / data/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=wmsprod)

(ORACLE_HOME=/data/oracle/product/11.2.0/db_1)

(SID_NAME=wmsprod)

)

)

[oracle@aa admin] $cat tnsnames.ora

WMSPROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WMSPROD)

)

)

WMSPRODDG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WMSPRODDG)

)

)

Prepare the library:

[oracle@bb admin] $cat listener.ora

# listener.ora Network Configuration File: / data/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

)

ADR_BASE_LISTENER = / data/oracle

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=wmsproddg)

(ORACLE_HOME=/data/oracle/product/11.2.0/db_1)

(SID_NAME=wmsproddg)

)

)

[oracle@bb admin] $cat tnsnames.ora

WMSPRODDG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WMSPRODDG)

)

)

WMSPROD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = WMSPROD)

)

)

Restart listening.

Add standby log files to the main library: one more set than redo log files.

SQL > alter database add standby logfile

2 GROUP 7 ('/ data/oracle/oradata/standbylog/standby01.log') SIZE 600m

3 GROUP 8 ('/ data/oracle/oradata/standbylog/standby02.log') size 600m

4 GROUP 9 ('/ data/oracle/oradata/standbylog/standby03.log') size 600m

5 GROUP 10 ('/ data/oracle/oradata/standbylog/standby04.log') size 600m

6 GROUP 11 ('/ data/oracle/oradata/standbylog/standby05.log') size 600m

7 GROUP 12 ('/ data/oracle/oradata/standbylog/standby06.log') size 600m

8 GROUP 13 ('/ data/oracle/oradata/standbylog/standby07.log') size 600m

Database altered.

Modify the master-slave init file:

Main library:

[oracle@aa dbs] $cat initwmsprod.ora

* .db_name='WMSPROD'

* .memory_target=5G

* .processes = 2000

* .audit_file_dest='/data/oracle/admin/wmsprod/adump'

* .audit_trail = 'db'

* .db_block_size=8192

* .db_domain=''

* .db_recovery_file_dest='/data/oracle/flash_recovery_area'

* .db_recovery_file_dest_size=2G

* .diagnostic_dest='/data/oracle'

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

* .open_cursors=300

* .remote_login_passwordfile='EXCLUSIVE'

* .undo_tablespace='UNDOTBS1'

* .control_files = (/ data/oracle/oradata/WMSPROD/ora_control01.ctl,/data/oracle/oradata/WMSPROD/ora_control02.ctl)

* .compatible = '11.2.0'

* .standby_file_management='AUTO'

* .LOG_ARCHIVE_CONFIG='DG_CONFIG= (wmsprod,wmsproddg)'

* .log_archive_dest_1='location=/data/oracle/oradata/WMSPROD/archivelog

VALID_FOR= (ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=wmsprod'

* .DB_UNIQUE_NAME=wmsprod

* .DB_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'

* .LOG_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'

* .log_archive_dest_2='SERVICE=wmsproddg LGWR ASYNC

Valid_for= (online_logfiles,primary_role)

Db_unique_name=wmsproddg'

* .LOG_ARCHIVE_DEST_STATE_1=ENABLE

* .LOG_ARCHIVE_DEST_STATE_2=ENABLE

* .FAL_SERVER=wmsproddg

* .fal_client=wmspro

Prepare the library:

[oracle@bb dbs] $cat initwmsproddg.ora

* .db_name='WMSPROD'

* .memory_target=5G

* .processes = 2000

* .audit_file_dest='/data/oracle/admin/wmsprod/adump'

* .audit_trail = 'db'

* .db_block_size=8192

* .db_domain=''

* .db_recovery_file_dest='/data/oracle/flash_recovery_area'

* .db_recovery_file_dest_size=2G

* .diagnostic_dest='/data/oracle'

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

* .open_cursors=300

* .remote_login_passwordfile='EXCLUSIVE'

* .undo_tablespace='UNDOTBS1'

* .control_files = (/ data/oracle/oradata/WMSPROD/ora_control01.ctl,/data/oracle/oradata/WMSPROD/ora_control02.ctl)

* .compatible = '11.2.0'

* .standby_file_management='AUTO'

* .LOG_ARCHIVE_CONFIG='DG_CONFIG= (wmsprod,wmsproddg)'

* .log_archive_dest_1='location=/data/oracle/oradata/WMSPROD/archivelog

VALID_FOR= (ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=wmsproddg'

* .DB_UNIQUE_NAME=wmsproddg

* .DB_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'

* .LOG_FILE_NAME_CONVERT='/data/oracle/oradata/WMSPROD/','/data/oracle/oradata/WMSPROD/'

* .log_archive_dest_2='SERVICE=wmsprod LGWR ASYNC

Valid_for= (online_logfiles,primary_role)

Db_unique_name=wmsprod'

* .LOG_ARCHIVE_DEST_STATE_1=ENABLE

* .LOG_ARCHIVE_DEST_STATE_2=ENABLE

* .FAL_SERVER=wmsprod

* .fal_client=wmsprodd

The password file of the cp master database generates the password file of the slave database and places it in the corresponding location of the slave database:

[oracle@aa dbs] $cp orapwwmsprod orapwwmsproddg

Create the spfile file of the standby library, and start the standby library to nomount mode

SQL > shutdown immediate

SQL > create spfile from pfile

SQL > startup nomount

RMAN copies the primary library to the standby library

First connect to the target database and the secondary database from the library RMAN

Rman target sys/password@wmsprod auxiliary sys/password@wmsproddg

Use the duplicate command of RMAN to copy. The directory structure on both sides is the same. You need to add the nofilenamecheck parameter.

RMAN > duplicate target database for standby from active database nofilenamecheck

After the copy is successful, the slave library is automatically loaded into mount mode and checked in sqlplus.

SQL > select status from v$instance

Prepare the execution process of the library:

[oracle@bb admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 18:09:13 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL > create spfile from pfile

File created.

SQL > startup nomount

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size 2262656 bytes

Variable Size 3724544384 bytes

Database Buffers 1593835520 bytes

Redo Buffers 24088576 bytes

SQL >!

[oracle@bb admin] $rman target sys/oracle@wmsprod auxiliary sys/oracle@wmsproddg

Recovery Manager: Release 11.2.0.4.0-Production on Fri May 15 18:10:47 2020

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

Connected to target database: WMSPROD (DBID=1351169222)

Connected to auxiliary database: WMSPROD (not mounted)

RMAN > duplicate target database for standby from active database nofilenamecheck

Starting Duplicate Db at 15-MAY-20

Using target database control file instead of recovery catalog

Allocated channel: ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: SID=2376 device type=DISK

Contents of Memory Script:

{

Backup as copy reuse

Targetfile'/ data/oracle/product/11.2.0/db_1/dbs/orapwwmsprod' auxiliary format

'/ data/oracle/product/11.2.0/db_1/dbs/orapwwmsproddg'

}

Executing Memory Script

Starting backup at 15-MAY-20

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=96 device type=DISK

Finished backup at 15-MAY-20

Contents of Memory Script:

{

Backup as copy current controlfile for standby auxiliary format'/ data/oracle/oradata/WMSPROD/ora_control01.ctl'

Restore clone controlfile to'/ data/oracle/oradata/WMSPROD/ora_control02.ctl' from

'/ data/oracle/oradata/WMSPROD/ora_control01.ctl'

}

Executing Memory Script

Starting backup at 15-MAY-20

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Copying standby control file

Output file name=/data/oracle/product/11.2.0/db_1/dbs/snapcf_wmsprod.f tag=TAG20200515T181846 RECID=1 STAMP=1040494726

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 15-MAY-20

Starting restore at 15-MAY-20

Using channel ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 15-MAY-20

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 to

"/ data/oracle/oradata/WMSPROD/temp01.dbf"

Switch clone tempfile all

Set newname for datafile 1 to

"/ data/oracle/oradata/WMSPROD/system01.dbf"

Set newname for datafile 2 to

"/ data/oracle/oradata/WMSPROD/sysaux01.dbf"

Set newname for datafile 3 to

"/ data/oracle/oradata/WMSPROD/undotbs01.dbf"

Set newname for datafile 4 to

"/ data/oracle/oradata/WMSPROD/users01.dbf"

Backup as copy reuse

Datafile 1 auxiliary format

"/ data/oracle/oradata/WMSPROD/system01.dbf" datafile

2 auxiliary format

"/ data/oracle/oradata/WMSPROD/sysaux01.dbf" datafile

3 auxiliary format

"/ data/oracle/oradata/WMSPROD/undotbs01.dbf" datafile

4 auxiliary format

"/ data/oracle/oradata/WMSPROD/users01.dbf"

Sql 'alter system archive log current'

}

Executing Memory Script

Executing command: SET NEWNAME

Renamed tempfile 1 to / data/oracle/oradata/WMSPROD/temp01.dbf in control file

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Executing command: SET NEWNAME

Starting backup at 15-MAY-20

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00004 name=/data/oracle/oradata/WMSPROD/users01.dbf

Output file name=/data/oracle/oradata/WMSPROD/users01.dbf tag=TAG20200515T181853

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00001 name=/data/oracle/oradata/WMSPROD/system01.dbf

Output file name=/data/oracle/oradata/WMSPROD/system01.dbf tag=TAG20200515T181853

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00002 name=/data/oracle/oradata/WMSPROD/sysaux01.dbf

Output file name=/data/oracle/oradata/WMSPROD/sysaux01.dbf tag=TAG20200515T181853

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00003 name=/data/oracle/oradata/WMSPROD/undotbs01.dbf

Output file name=/data/oracle/oradata/WMSPROD/undotbs01.dbf tag=TAG20200515T181853

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 15-MAY-20

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=1040494790 file name=/data/oracle/oradata/WMSPROD/system01.dbf

Datafile 2 switched to datafile copy

Input datafile copy RECID=2 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/sysaux01.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=3 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/undotbs01.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=4 STAMP=1040494790 file name=/data/oracle/oradata/WMSPROD/users01.dbf

Finished Duplicate Db at 15-MAY-20

View the archive mode of the standby library:

[oracle@bb admin] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 15 18:32:46 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oracle/oradata/WMSPROD/archivelog

Oldest online log sequence 7

Next log sequence to archive 0

Current log sequence 8

If you didn't build this archivelog directory.

Synchronized archive log files are generated in the parent directory.

The main library cut log group:

Cut the log:

SQL > alter system switch logfile

2

System altered.

[oracle@aa archivelog] $ll

Total 105956

-rw-r- 1 oracle oinstall 56715776 May 14 20:50 1_2_1040413510.dbf

-rw-r- 1 oracle oinstall 47865344 May 15 17:33 1_3_1040413510.dbf

-rw-r- 1 oracle oinstall 44544 May 15 17:33 1_4_1040413510.dbf

-rw-r- 1 oracle oinstall 2842624 May 15 18:18 1_5_1040413510.dbf

-rw-r- 1 oracle oinstall 28160 May 15 18:19 1_6_1040413510.dbf

-rw-r- 1 oracle oinstall 151040 May 15 18:24 1_7_1040413510.dbf

-rw-r- 1 oracle oinstall 844800 May 15 18:50 1_8_1040413510.dbf

Prepare the database log synchronization:

[oracle@bb archivelog] $ll

Total 828

-rw-r- 1 oracle oinstall 844800 May 15 18:50 1_8_1040413510.dbf

Consult the library:

SQL > select status from v$instance

STATUS

-

MOUNTED

SQL > select name from v$database

NAME

-

WMSPROD

Turn off the audit:

Step 1: check to see if the audit function is enabled.

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/ORCL/adump

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string NONE

The value of audit_trail is NONE, which means that it is not enabled.

The value of audit_trail is FALSE, which means that it is not enabled.

The value of audit_trail is DB, which means it is enabled.

The value of audit_trail is TURE, which means it is enabled.

The value value of audit_trail is OS, indicating that the audit record is written to an operating system file (not particularly understood)

Step 2: turn on the audit function

SQL > alter system set audit_sys_operations=TRUE scope=spfile;-- audit management user (log in as sysdba/sysoper)

SQL > alter system set audit_trail=db,extended scope=spfile

Restart the instance

Step 3: turn off the audit function

SQL > conn / as sysdba

SQL > show parameter audit

SQL > alter system set audit_trail = none scope=spfile

Restart the instance

OK .

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / data/oracle/admin/wmsprod/adu

Mp

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

SQL > alter system set audit_trail = none scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 5344731136 bytes

Fixed Size 2262656 bytes

Variable Size 3724544384 bytes

Database Buffers 1593835520 bytes

Redo Buffers 24088576 bytes

Database mounted.

Database opened.

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / data/oracle/admin/wmsprod/adu

Mp

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string NONE

Open the real-time log application in the slave database

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

Query the role status of the active and standby database

SQL > select switchover_status,database_role from v$database

-- the main library shows: TO STANDBY/PRIMARY. If SESSION ACTIVE shows that there is still an active session, you need to close the active session and check again.

-- display of the standby library: NOT ALLOWED/PHYSICAL STANDBY

Test DG

Perform log switching test

Switch the archive on the main database, and check whether the switch has also occurred in the standby database.

Perform log switching on the main library

SQL > archive log list

SQL > alter system switch logfile

SQL > archive log list

Check it on the database, and the sequence number of the log has also changed.

SQL > archive log list

one

View the DG process started by the slave library

SQL > select PROCESS, STATUS from v$managed_standby

PROCESS STATUS

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

RFS IDLE

RFS IDLE

MRP0 APPLYING_LOG

View the protection mode of the database

SQL > select database_role,protection_mode,protection_level,open_mode from v$database

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE

-

PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE

View the log information of DG

SQL > select * from v$dataguard_status

one

Open Read Only Standby database

Open the standby library in read-only mode and open the real-time log application

SQL > shutdown immediate

SQL > startup

SQL > select database_role,protection_mode,protection_level,open_mode from v$database

SQL > select process,client_process,sequence#,status from v$managed_standby

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

Execute on DG:

SQL > select database_role from v$database

DATABASE_ROLE

-

PHYSICAL STANDBY

Physical repository.

SQL > select status from vastness instance status

-

MOUNTED

Originally, DG is in mount state.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Database altered.

Disconnect application synchronization.

SQL > alter database open read only

Database altered.

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION

Database altered.

Turn on application synchronization.

SQL > select RECOVERY_MODE from v$archive_dest_status where rownum SELECT NAME,VALUE,UNIT,TIME_COMPUTED

2 FROM V$DATAGUARD_STATS

3 WHERE NAME IN ('transport lag','apply lag')

NAME VALUE UNIT TIME_COMPUTED

-

Transport lag + 00 00:00:00 day (2) to second (0) interval 05ax 15max 2020 19:41:55

Apply lag + 00 00:00:00 day (2) to second (0) interval 05ax 15max 2020 19:41:55

Main library log information:

SQL > select thread#, max (sequence#) from v$archived_log group by thread#

THREAD# MAX (SEQUENCE#)

--

1 14

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oracle/oradata/WMSPROD/archivelog

Oldest online log sequence 10

Next log sequence to archive 15

Current log sequence 15

Prepare database log information:

SQL > select thread#, max (sequence#) from v$archived_log where applied='YES' group by thread#

THREAD# MAX (SEQUENCE#)

--

1 13

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oracle/oradata/WMSPROD/archivelog

Oldest online log sequence 10

Next log sequence to archive 0

Current log sequence 15

The synchronization of active and standby is normal.

At this point, the study on "what is the method of building physical DG by Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.