Shulou(Shulou.com)06/01 Report--

Preface

A few days ago, my friend asked me a question, a single-node RAC, to do a set of Data Guard, but the standby database can give the database memory is only about half of the main database, can be successful. I don't know. I haven't done it. Give it a try.

I. Overview

1. Technical points involved

1) RAC as primary database and nonRAC as standby database

2) use RMAN as the backup method of the database

3) use Backup-based duplication to create a standby library

4) the main library uses ASM storage mode, and the standby library uses filesystem as storage.

5) use standby logfile to enable real-time log update

two。 Summary of the information table of the master and standby database

Primary (RAC)

Standby (fs)

HOSTNAME

Yukki

Fuzhou

ORACLE_SID

Cs1

Stbcs1

DB_NAME

Cs

Cs

DB_UNIQUE_NAME

Cs

Stby

SERVICE_NAMES

Cs_pri

Cs_stb

INSTANCE_NAME

Cs1

Stbcs1

INSTANCE_NUMBER

one

one

THREAD

one

one

TEMPFILE_LOCATION

+ DATA/cs/tempfile

/ u01/db/oradata

II. Configuration of Primary main library

1. View Managed Standby components

SYS@ cs1 > select * from v$option where lower (parameter) = 'managed standby'

PARAMETER VALUE

Managed Standby TRUE

# Please make sure the value is true

two。 Check the settings of remote_login_passwordfile

SYS@ cs1 > show parameter remote_login_passwordfile

NAME TYPE VALUE

-

Remote_login_passwordfile string EXCLUSIVE

# if the parameter is not exclusive, modify it according to the following command, and restart to make it effective

SYS@ cs1 > alter system set remote_login=exclusive scope=spfile

3. Check whether the database is in archive mode

SYS@ cs1 > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination + DATA

Oldest online log sequence 87

Next log sequence to archive 89

Current log sequence 89

# if it is in non-archive mode, you need to cleanly shut down the database, start it to mount mode, and change it to archive mode before opening the library.

SYS@ cs1 > shutdown immediate

SYS@ cs1 > startup mount

SYS@ cs1 > alter database archivelog

SYS@ cs1 > alter database open

SYS@ cs1 > select log_mode from v$database

4. Check whether the database is enabled for force logging

SYS@ cs1 > select name,force_logging from v$database

NAME FOR

CS YES

# if force logging is not enabled in the database, then

SYS@ cs1 > alter database force logging

SYS@ cs1 > select name,force_logging from v$database

SYS@ cs1 > alter system archive log current

5. Check the MD5 value of the main library password file

[oracle@ yukki dbs] $openssl md5 orapwcs1

MD5 (orapwcs1) = 7836520c978614723e57330e12ccbe90

# make sure that the MD5 values of the master and slave database password files are the same, even if the sys keys are the same

6. Modification of main library parameters

SYS@ cs1 > alter system set db_unique_name=cs scope=spfile

SYS@ cs1 > alter system set log_archive_config='dg_config= (cs,stby)'

SYS@ cs1 > alter system set log_archive_dest_1='location=+DATA valid_for= (all_logfiles,all_roles) db_unique_name=cs'

SYS@ cs1 > alter system set log_archive_dest_2='service=dbstandby async valid_for= (online_logfiles,primary_roles) db_unique_name=stby'

SYS@ cs1 > alter system set log_archive_dest_state_1=enable

SYS@ cs1 > alter system set log_archive_dest_state_ 2 = enable

SYS@ cs1 > alter system set log_archive_max_processes=30

SYS@ cs1 > alter system set fal_server=dbstandby

SYS@ cs1 > alter system set standby_file_management= auto

SYS@ cs1 > alter system set db_file_name_convert=' + DATA / cs/datafile, / u01amp dbDB oradata 'scope=spfile

SYS@ cs1 > alter system set log_file_name_convert=' + DATA / cs/onlinelog, / u01/db/oradata'scope=spfile

SYS@ cs1 > alter system set service_names=cs_pri

3. Standby backup repository configuration

1. Prepare the password file for standby

# copy the password file of the master database to the $ORACLE_HOME/dbs directory of the slave database, and rename it to orapwstbcs1

[oracle@ yukki dbs] $scp orapwcs1 oracle@ fuzhou: $ORACLE_HOME/dbs

[oracle@fuzhou dbs] $mv orapwcs1 orapwstbcs1

# check the MD5 value of the slave password file to make sure it is the same as the main database

[oracle@fuzhou dbs] $openssl md5 orapwstbcs1

MD5 (orapwstbcs1) = 7836520c978614723e57330e12ccbe90

two。 Prepare the parameter file for standby

Generate pfile in the main library and transfer it to the standby database for modification

SYS@ cs1 > create pfile='/tmp/pfile2019110 1 'from spfile

[oracle@ yukki tmp] $scp pfile2019110 1 oracle@fuzhou:/tmp / initstbcs1.ora

[oracle@fuzhou dbs] $vi initstbcs1.ora

Stbcs1._...

...

* .audit_file_dest='/u01/db/admin/cs/adump'

* .audit_trail='db'

* .compatible='11.2.0.4.0'

* .control_files='/u01/db/oradata/control01.ctl','/u01/db/oradata/control02.ctl'#Restore Controlfile

* .db_block_size=8192

* .db_create_file_dest='/u01/db/oradata'

* .db_domain=''

* .db_file_name_convert='+DATA/cs/datafile','/u01/db/oradata'

* .db_name='cs'

* .db_recovery_file_dest='/u01/db/fast_recovery_area'

* .db_recovery_file_dest_size=4385144832

* .db_unique_name='STBY'

* .diagnostic_dest='/u01/db'

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

* .enable_goldengate_replication=TRUE

* .fal_server='DBPRIMARY'

* .log_archive_config='DG_CONFIG= (STBY,CS)'

* .log_archive_dest_1='location=/u01/db/arch valid_for= (all_logfiles,all_roles) db_unique_name=stby'

* .log_archive_dest_2='service=dbprimary async valid_for= (online_logfiles,primary_roles) db_unique_name=cs'

* .log_archive_dest_state_1='ENABLE'

* .log_archive_dest_state_2='ENABLE'

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

* .log_archive_max_processes=30

* .log_file_name_convert='+DATA/cs/onlinelog','/u01/db/oradata'

* .open_cursors=300

* .pga_aggregate_target=109715200

* .processes=150

* .remote_login_passwordfile='exclusive'

* .service_names='CS_STB'

* .sga_target=329145600

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

# it should be noted here that due to the experimental requirements, the sga_target and pga_aggregate_target in the parameter file of the standby library need to be modified to half of the main library.

# parameters cancelled in 11g:

* .standby_archive_dest

* .fal_client

3. Create the necessary directory structure

[oracle@ fuzhou] $mkdir-p / u01/db/admin/cs/adump

[oracle@ fuzhou] $mkdir-p / u01/db/oradata

[oracle@ fuzhou] $mkdir-p / u01/db/arch

[oracle@ fuzhou] $mkdir-p / u01/db/fast_recovery_area

4. Create spfile and start instance

[oracle@ fuzhou ~] $export ORACLE_SID=stbcs1

[oracle@ fuzhou ~] $sqlplus / as sysdba

SYS@ stbcs1 > create spfile from pfile

SYS@ stbcs1 > startup nomount

SYS@ stbcs1 > show parameter spfile

NAME TYPE VALUE

Spfile string / u01/db/product/11204/dbhome_1/dbs/spfilestbcs1.ora

IV. Backup-based duplication replication physical standby

1. Listener.ora configuration

# because there is only oracle software on the standby side, and the instance cannot be started to the mount status, the PMON process cannot be automatically registered, so static monitoring is adopted.

Main library:

[grid@ yukki ~] $cat / u01/11.2.0/grid/network/admin/listener.ora

LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER) # line added by Agent

LISTENER_SCAN1= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC) (KEY=LISTENER_SCAN1) # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME= cs_pri)

(ORACLE_HOME = / u01/db/product/11204/dbhome_1)

(SID_NAME = cs1)

)

)

Prepare the library:

[oracle@ fuzhou ~] $cat / u01/db/product/11204/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: / u01/db/product/11204/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME= cs_stb)

(ORACLE_HOME = / u01/db/product/11204/dbhome_1)

(SID_NAME = stbcs1)

)

)

2. Tnsnames.ora configuration

Add to the $ORACLE_HOME/network/admin/tnsnames.ora of the master and standby library:

Dbprimary =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cs_pri)

)

)

Dbstandby =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cs_stb)

)

)

3. Back up the primary database

1) View the physical structure of the database

[oracle@ yukki ~] $rman target /

Recovery Manager: Release 11.2.0.4.0-Production on Mon Nov 4 17:40:28 2019

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

Connected to target database: CS (DBID=1434125244)

RMAN > report schema

Using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name CS

List of Permanent Datafiles

= =

File Size (MB) Tablespace RB segs Datafile Name

1 750 SYSTEM * * + DATA/cs/datafile/system.256.1018198953

2580 SYSAUX * * + DATA/cs/datafile/sysaux.257.1018198953

3 75 UNDOTBS1 * * + DATA/cs/datafile/undotbs1.258.1018198953

4 5 USERS * * + DATA/cs/datafile/users.259.1018198953

5 50 TEST * + DATA/cs/datafile/test.dbf

List of Temporary Files

=

File Size (MB) Tablespace Maxsize (MB) Tempfile Name

1 29 TEMP 32767 + DATA/cs/tempfile/temp.268.1018199043

2) backup database and control files

Run {

Sql 'alter system archive log current'

Allocate channel c1 device type disk

Allocate channel c2 device type disk

Allocate channel c3 device type disk

Backup database filesperset 1 format'/ backup/whole_%d_%U_%t.bus'

Backup current controlfile for standby format'/ backup/ctl_%d_%U_%t.bus'

Release channel c1

Release channel c2

Release channel c3

}

3) back up the archive log

Run {

Sql 'alter system archive log current'

Allocate channel c1 device type disk

Allocate channel c2 device type disk

Backup archivelog all format'/ backup/ arch _% dumped% Utility% t.bus'

Release channel c1

Release channel c2

}

4) transfer the backup to the backup machine

[oracle@ yukki ~] $scp / backup/* oracle@fuzhou:/backup

4. Using duplicate for database recovery

1) create a script

[oracle@ yukki ~] $vi duplicate.sh

Connect target sys/oracle@dbprimary

Connect auxiliary sys/oracle@dbstandby

Run {

Allocate channel c1 device type disk

Allocate channel c2 device type disk

Allocate channel c3 device type disk

Allocate auxiliary channel aux1 device type disk

Allocate auxiliary channel aux2 device type disk

Allocate auxiliary channel aux3 device type disk

Set until sequence=87 thread=1

Set newname for tempfile 1 to'/ u01According to SQL oradata temp01.dbf'

Duplicate target database for standby nofilenamecheck dorecover

Release channel aux1

Release channel aux2

Release channel aux3

Release channel c1

Release channel c2

Release channel c3

}

# since there is no parameter temp_file_name_convert, you need to give tempfile set newname operation before duplicate

# when manually assigning replication channels, you must add allocate auxiliary channel, otherwise you will be prompted:

RMAN-05503: at least one auxiliary channel must be allocated to execute this command

# if the keyword from active database is used in duplicate (the active database duplication method of direct transmission through the network does not need backup of the main database, which saves disk space and time for transferring backup, but there is some pressure on the master database in the process of replication and a certain network bandwidth is required), the channel must be assigned to the master database, otherwise it will be prompted:

RMAN-06034: at least 1 channel must be allocated to execute this command

2) use nohup to call the script to run in the background

[oracle@ yukki ~] $nohup rman cmdfile=duplicate.sh > duplicate.log &

5. Start physical standby

SYS@ stbcs1 > shutdown immediate

SYS@ stbcs1 > startup

SYS@ stbcs1 > recover managed standby database disconnect from session

SYS@ stbcs1 > select name,open_mode,database_role,protection_mode,switchover_status,controlfile_type from v$database

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS CONTROL

CS READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE NOT ALLOWED STANDBY

V. DATAGUARD uses standby logfile

1. Standby logfile creation requirements

# make sure that the log files of the master / slave database are of the same size. It is recommended that the standby logfile of the slave database is one more than the redo logfile of the master database. The purpose is to ensure that the slave database has a set of free logs available at any time.

# when using rman to generate a backup of controlfile for standby, there will be relevant prompts in the alert log, as follows:

Clearing standby activation ID 1434109882 (0x557ac7ba)

The primary database controlfile was created using the

'MAXLOGFILES 192 'clause.

There is space for up to 189 standby redo logfiles

Use the following SQL commands on the standby database to create

Standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800

WARNING: OMF is enabled on this database. Creating a physical

Standby controlfile, when OMF is enabled on the primary

Database, requires manual RMAN intervention to resolve OMF

Datafile pathnames.

NOTE: Please refer to the RMAN documentation for procedures

Describing how to manually resolve OMF datafile pathnames.

two。 Add standby logfile to standby library

# first check the information of the main library online redo logfiles

SYS@ cs1 > select group#,thread#,bytes from v$log

GROUP# THREAD# BYTES

1 1 52428800

2 1 52428800

3 1 52428800

# make sure that the size of the ORLs log group of the main database is the same, then configure SRLs, and when adding standby logfile to the slave database, stop the MRP process first:

SYS@ stbcs1 > recover managed standby database cancel

SYS@ stbcs1 > alter database add standby logfile thread 1 group 11'/ u01 group

SYS@ stbcs1 > alter database add standby logfile thread 1 group 12'/ u01/db/oradata/stb_redo0 2.log 'size 52428800

SYS@ stbcs1 > alter database add standby logfile thread 1 group 13'/ u01/db/oradata/stb_redo0 3.log 'size 52428800

SYS@ stbcs1 > alter database add standby logfile thread 1 group 14'/ u01/db/oradata/stb_redo0 4.log 'size 52428800

# since there are three groups of ORLs in the main database, if you do not specify the number of groups when creating the SRLs, the default will be 4-7, so it will cause confusion if log groups are added to the main database later, so configure standby redo logfiles from group 11.

# in addition, when the master database has multiple instances, the slave database should also be configured with multiple thread, in order to enable real time apply. However, if only thread 1 is created in the slave database, it will not affect the transmission and application of archive log. However, the slave database will not use real time apply, and the master database online redo cannot transfer applications in real time. It will only be applied when the backup database is archived and switched.

3. Add standby logfile to the main library

SYS@ cs1 > alter database add standby logfile thread 1 group 1 1'+ DATA/cs/onlinelog/stb y _ redo01.log' size 52428800

SYS@ cs1 > alter database add standby logfile thread 1 group 1 2'+ DATA/cs/onlinelog/stb y _ redo0 2 .log 'size 52428800

SYS@ cs1 > alter database add standby logfile thread 1 group 1 3'+ DATA/cs/onlinelog/stb y _ redo0 3 .log 'size 52428800

SYS@ cs1 > alter database add standby logfile thread 1 group 1 4'+ DATA/cs/onlinelog/stb y _ redo0 4 .log 'size 52428800

# when configuring the standby logfile of the slave database, it also needs to be pre-configured on the main database for future switching.

VI. Description of some parameters

1. Db_name

Database name, in a Data Guard environment, you need to keep the db_name of the master and slave libraries the same.

2. Db_unique_name

The unique name used to distinguish between the primary and secondary libraries in the DG environment, and the db_unique_name will not change even if the roles of the primary and standby libraries are reversed.

3. Log_archive_config

This parameter sets all db_unique_name in the same Data Guard environment through dg_configs, separated by commas, and defines this parameter to ensure that the master / slave database can send or receive logs.

4. Log_archive_dest_1

Set the local path to the log archive through location, and the master and slave libraries need to define the archive address of their respective Online Redo Log. In this example, log_archive_dest_1='location=+DATA valid_for= (all_logfiles,all_roles) db_unique_name=cs' can be understood as for the master library (cs), regardless of whether she is the master or slave database (all_roles), she will complete the archiving operation herself and archive the logs under the local path + DATA.

5. Log_archive_dest_2

This parameter takes effect only when the database role is primary, specifying that primary transmits redo log to the standby database defined by this parameter, where the setting of service is the Oracle Net name defined in tnsnames.ora. Log_archive_dest_2 can be said to be one of the most important parameters on dataguard. It defines the transmission mode (sync or async) and transmission target (standby apply node) of redo log, which directly determines the data protection level of dataguard.

6. Fal_server

Fal is fatch archive log. Its value is the Oracle Net name of the remote database service in tnsnames.ora, and fal_server is the parameter set in the slave database. Once the slave database generates gap, it will request to transfer the missing logs to the master database through the fal_server parameter. Of course, for switchover, this parameter should also be pre-configured on the master database.

7. Db_file_name_convert

Define the data file path conversion of the master and slave libraries, with the far end at the front and the local side at the back. If there are more than one, indicate the mapping relationship one by one.

8. Log_file_name_convert

Define the path conversion of online log files for master and slave libraries, with the far end at the front and the local end at the back. If there are more than one, indicate the mapping relationship one by one.

9. Standby_file_management

Standby parameters, which are used to control whether the changes to the tablespaces or data files added to the main library will be propagated to the physical repository.

Auto: tablespace creation operations performed by the main library are propagated to the physical repository for execution.

Manual: default, you need to manually copy the newly created data file to the physical standby server.

10. Service_name (parameter in tnsnames.ora)

Service_name is after the emergence of multiple instances, in order to facilitate the application of the parameter proposed to connect to the database, this parameter directly corresponds to the database rather than an instance, so this parameter is not directly related to sid and does not have to be the same as sid. When static monitoring is configured in the server-side listener.ora, the service_name in the client-side tnsnames.ora corresponds to the GLOBAL_DBNAME in the server-side static monitoring, and does not have to correspond to the service_names parameters in the server-side database. However, if static listening is not configured, the service_name in the client-side tnsnames.ora needs to take the value from the service_names in the server-side database.

Above, the memory of the master and slave libraries is inconsistent, so you can build a Data Guard environment.

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.