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

Environment (you only need to install the database software to prepare the library):

Main library: 192.168.1.100 OS: CentOS 7.3x64 DB: ORACLE12.2.0.1 SID: ORA12C db_unique_name: ORA12C

Standby library: 192.168.1.101 OS: CentOS 7.3x64 DB: ORACLE12.2.0.1 SID: ORA12C db_unique_name: ORA12CDG

1. Set the main library to forced archive mode:

(1) SQL > alter database force logging

Database altered.

SQL > select name,force_logging from v$database

NAME FORCE_LOG

ORA12C YES

(2) if data files are added or deleted in the main database, these files also need to be added or deleted in the backup, and the following settings are required:

SQL > alter system set STANDBY_FILE_MANAGEMENT='AUTO' scope=spfile

By default, this parameter is MANUAL manual, which causes the log not to be applied when data files are added or deleted. SQL > show parameter STANDBY_FILE

two。 Create standby log files (alternate log file)

The slave library uses standby log files to save the redo logs received from the master library. Since the slave library is mainly used, why do you need to set up standby log files on the master library as well? The main reason is that the main library may be converted into a standby library, which requires standby log files. If you set up standby, you should pay attention to the following:

Standby log files is the same size as redo log files.

Query redo log files file size:

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

GROUP# THREAD# ARC STATUS BYTES/1024/1024

1 1 YES INACTIVE 200

2 1 NO CURRENT 200

3 1 YES INACTIVE 200

In general, the number of standbyredo log file groups is at least one more than the number of online redo log file groups in the primary database.

SQL > select member from v$logfile

SQL > alter database add standby logfile thread 1 group 11'/ oracle/oradata/ORA12C/stdby11.log' size 200m

SQL > alter database add standby logfile thread 1 group 12'/ oracle/oradata/ORA12C/stdby12.log' size 200m

SQL > alter database add standby logfile thread 1 group 13'/ oracle/oradata/ORA12C/stdby13.log' size 200m

SQL > alter database add standby logfile thread 1 group 14'/ oracle/oradata/ORA12C/stdby14.log' size 200m

3. Password file creation transfer

(1) the general database has a password file by default, which is stored as $ORACLE_HOME/dbs/orapwSID and here is orapwORA12C.

If not, create it manually:

[oracle@ora12c ~] $orapwd file=$ORACLE_HOME/dbs/orapwORA12C password=ccdadmin

(2) check whether the REMOTE_LOGIN_ PASSWORDFILER value is EXCLUSIVE.

SQL > show parameter REMOTE_LOGIN_PASSWORDFILE

If the value is not EXCLUSIVE, then: alter system set remote_login_passwordfile=exclusive scope=spfile

(3) copy the password file to the standby database

[oracle@ora12c ~] $scp $ORACLE_HOME/dbs/orapwORA12C oracle@192.168.1.101:/oracle/orahome/dbs/

4.db_name and db_unique_name

The default db_name and db_unique_name are the same as the instance name. Here is ORA12C. It should be noted that the db_unique_name of the master library and the slave library in DG is not the same and needs to be distinguished. Here we set the db_unique_name of the master library to ORA12C and the slave library to ORA12CDG

SQL > show parameter db_unique_name

NAME TYPE VALUE

-

Db_unique_name string ORA12C

Settings: SQL > alter system set db_unique_name=ORA12C SCOPE=SPFILE

Note: although the default db_unique_name and db_name are the same, it needs to be set explicitly, otherwise this parameter is not available in spfile.

5. Turn on flashback

SQL > select flashback_on from v$database

SQL > alter database flashback on

If you encounter an error in ORA-01153, you must be doing this in the standby database. You need to cancel the redo log application, enable flashback logging, and then re-enable the log application.

Enable flashback on the primary database, not synchronize the standby database and enable it. You must manually enable flashback on both the primary library and the standby database.

6. Configure snooping and tnsnames.ora

Main database monitoring:

[oracle@ora12c ~] $cd $ORACLE_HOME/network/admin

[oracle@ora12c admin] $more listener.ora

SID_LIST_PRIM =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORA12C)

(ORACLE_HOME = / oracle/orahome)

(SID_NAME = ORA12C)

)

)

PRIM =

(DESCRIPTION =

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

)

Standby database monitoring:

[oracle@ora12cdg admin] $more listener.ora

SID_LIST_STDBY =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = ORA12C)

(ORACLE_HOME = / oracle/orahome)

(SID_NAME = ORA12C)

)

)

STDBY =

(DESCRIPTION =

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

)

Main library tnsnames.ora (slave database is the same as main library):

[oracle@ora12c ~] $cd $ORACLE_HOME/network/admin

[oracle@ora12c admin] $more tnsnames.ora

ORA12C =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ORA12C)

)

)

ORA12CDG =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SID = ORA12C)

)

)

7. Redo log transfer configuration

(1) configure the archive log path:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / oracle/archive

Oldest online log sequence 56

Next log sequence to archive 58

Current log sequence 58

SQL > alter system set log_archive_dest_1='LOCATION=/oracle/archive valid_for= (all_logfiles,all_roles) db_unique_name=ORA12C' scope=spfile

SQL > alter system set log_archive_dest_state_1='enable' scope=spfile

The official document says that using valid_for= (online_logfiles, all_roles) will prevent the repository from archiving alternate log files because they are not online logs. With the all_logfiles option, both the primary and standby libraries will be able to archive online as well as standby logs. If you want to back up in the repository and back up the archive logs at the same time, you must use all_logfiles

(2) configure redo log to backup database:

SQL > alter system set log_archive_dest_2='SERVICE=ORA12CDG lgwr sync affirm valid_for= (online_logfile,primary_role) db_unique_name=ORA12CDG'

SQL > alter system set log_archive_dest_state_2='enable' scope=spfile

(3) set db_file_name_convert and log_file_name_conver parameters:

SQL > alter system set log_file_name_convert='/oracle/archive','/oracle/archive' scope=spfile

SQL > alter system set db_file_name_convert='/oracle/oradata/ORA12C','/oracle/oradata/ORA12C' scope=spfile

If the directory structure of the primary and standby libraries is different, or if the primary and standby libraries are on the same server, you must set the db_file_name_convert and log_file_name_conver parameters to convert the save paths of data files and online logs.

(4) the STANDBY_ARCHIVE_DEST parameter is no longer needed and has been officially deprecated.

8. Configure FAL_SERVER

This parameter specifies where to find the missing archive log if there is a problem with the log transfer. It is used when there is a gap between the redo logs received by the repository. This can happen when log transfers are interrupted, such as when you need to maintain the repository. During the preparation and maintenance of the library, no logs are transmitted, and the gap occurs. When this parameter is set, the standby library will take the initiative to find those missing logs and ask the main library to transfer them.

In the master library: fal_server= slave library

The reverse from the library: fal_server= main library

SQL > alter system set FAL_SERVER='ORA12CDG'

Note: FAL_CLIENT has been deprecated in 11g and although it can be configured, it no longer works.

The name of another library in the 9.Data Guard configuration

SQL > alter system set log_archive_config = 'dg_config= (ORA12C,ORA12CDG)'

10. Create the required directories in the standby library to place data files, control files, trace files, etc.

[oracle@ora12cdg ~] $cd / oracle/

[oracle@ora12cdg oracle] $mkdir admin archive fast_recovery_area

[oracle@ora12cdg oracle] $cd admin/

[oracle@ora12cdg admin] $mkdir ORA12C

[oracle@ora12cdg admin] $cd ORA12C/

[oracle@ora12cdg ORA12C] $mkdir adump dpdump pfile

[oracle@ora12cdg ORA12C] $cd / oracle/fast_recovery_area/

[oracle@ora12cdg fast_recovery_area] $mkdir ORA12C

11. The main library generates pfile (initORA12C.ora) and transfers it to the standby library for modification

SQL > create pfile from spfile

[oracle@ora12c ~] $scp / oracle/orahome/dbs/initORA12C.ora oracle@192.168.1.101:/oracle/orahome/dbs/

Main library pfile:

[oracle@ora12c dbs] $more initORA12C.ora

ORA12C.__data_transfer_cache_size=0

ORA12C.__db_cache_size=2298478592

ORA12C.__inmemory_ext_roarea=0

ORA12C.__inmemory_ext_rwarea=0

ORA12C.__java_pool_size=16777216

ORA12C.__large_pool_size=83886080

ORA12C.__oracle_base='/oracle'#ORACLE_BASE set from environment

ORA12C.__pga_aggregate_target=1056964608

ORA12C.__sga_target=3154116608

ORA12C.__shared_io_pool_size=167772160

ORA12C.__shared_pool_size=570425344

ORA12C.__streams_pool_size=0

* .audit_file_dest='/oracle/admin/ORA12C/adump'

* .audit_trail='db'

* .compatible='12.2.0'

* .control_files='/oracle/oradata/ORA12C/control01.ctl','/oracle/fast_recovery_area/ORA12C/control02.ctl'

* .db_block_size=8192

* .db_file_name_convert='/oracle/oradata/ORA12C','/oracle/oradata/ORA12C'

The name of * .db_name='ORA12C' # database, which should be the same in all master and slave databases of the entire DG

* .db_recovery_file_dest='/oracle/fast_recovery_area/ORA12C'

* .db_recovery_file_dest_size=8016m

* .db_unique_name='ORA12C'

* .diagnostic_dest='/oracle'

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

* .fal_server='ORA12CDG'

* .log_archive_config='dg_config= (ORA12C,ORA12CDG)'

* .log_archive_dest_1='LOCATION=/oracle/archive valid_for= (all_logfiles,all_roles) db_unique_name=ORA12C'

* .log_archive_dest_2='SERVICE=ORA12CDG lgwr sync affirm valid_for= (online_logfile,primary_role) db_unique_name=ORA12CDG'

* .log_archive_dest_state_1='enable'

* .log_archive_dest_state_2='enable'

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

* .log_file_name_convert='/oracle/archive','/oracle/archive'

* .nls_language='AMERICAN'

* .nls_territory='AMERICA'

* .open_cursors=300

* .pga_aggregate_target=1000m

* .processes=500

* .remote_login_passwordfile='EXCLUSIVE'

* .sga_target=3000m

* .standby_file_management='AUTO'

* .undo_tablespace='UNDOTBS1'

Preparation for modification of the library:

* .db_file_name_convert='/oracle/oradata/ORA12C','/oracle/oradata/ORA12C'

* .db_unique_name='ORA12CDG'

* .fal_server='ORA12C'

* .log_archive_config='dg_config= (ORA12CDG,ORA12C)'

* .log_archive_dest_1='LOCATION=/oracle/orabackup/archive valid_for= (all_logfiles,all_roles) db_unique_name=ORA12CDG'

* .log_archive_dest_2='SERVICE=ORA12C lgwr sync affirm valid_for= (online_logfile,primary_role) db_unique_name=ORA12C'

* .log_file_name_convert='/oracle/archive','/oracle/archive'

twelve。 Start slave to nomount status:

SQL > conn / as sysdba

SQL > create spfile from pfile='/oracle/orahome/dbs/initORA12C.ora'

SQL > startup nomount

13. Execute RMAN on the primary system and connect the primary and secondary databases (that is, standby)

[oracle@ora12c admin] $rman target sys/passwd@ORA12C auxiliary sys/passwd@ORA12CDG

Connected to target database: ORA12C (DBID=376201757)

Connected to recovery catalog database

Connected to auxiliary database: ORA12C (not mounted)

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

14. Enable the physical standby database:

After the duplicate is completed, the repository is mount.

SQL > select open_mode from v$database

OPEN_MODE

-

MOUNTED

SQL > alter database recover managed standby database disconnect from session

15. Check the synchronization status of master and slave libraries:

SQL > alter system switch logfile; / / main library

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

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

16. Enable flashback for slave database:

SQL > select flashback_on from v$database

SQL > alter database recover managed standby database cancel

SQL > alter database flashback on

SQL > alter database recover managed standby database disconnect from session

17. If you want to enable Active Data Guard, follow these steps:

(1) cancel the management of standby database Redo Apply

SQL > alter database recover managed standby database cancel

(2) then open the database as read-only

SQL > alter database open

(3) restart Redo Apply

SQL > alter database recover managed standby database disconnect from session

(4) View the open mode of backup database.

SQL > select open_mode from v$database

OPEN_MODE

-

READ ONLY WITH APPLY

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.