Oracle 10GR2 Dataguard Build (non-duplicate mode)

My lab Environment:
Source Production library (main Library):
IP Address: 192.168.1.30
Oracle 10.2.0.5 Single Instance

New DG Library (repository):
IP Address: 192.168.1.31
Oracle 10.2.0.5 Single Instance

• 1. Source Production Library Open Archive
• 2.rman Backup Source Production Library
• 3. Modifying source Production library parameters
• 4. Configure Tnsnames.ora
• 5. Synchronizing Password files
• 6. Configuring the Pfile File
• 7. Create a Repository control file
• 8. Restore the Standby library
• 9. Turn on the log app
• 10.switchover Test
• 11. Create a restore point to activate the repository test
• 12.failover Test

1. Source Production Library Open Archive

Deploying the Dataguard environment requires the main library to have the archive mode turned on, and if not, you need to request an outage to open the archive first.

shutdown immediatestartup mountalter database archivelog;alter database open;archive log list;

Note: Archive log storage location, and develop the archive log deletion policy;

Archive Log Deletion example (deleted 7 days ago Archive):

If the archive is turned on before the main library and there is a reasonable backup strategy, it is likely that this step is not required to clear the archive separately.

2.rman Backup Source Production Library

The main repository Rman fully prepared, depending on the circumstances choose whether to compress the backup set, I have limited space here choose to compress the backup set.

Backup script:

[[email protected] rman]$ cat backup.sql run {allocate channel d1 type disk;allocate channel d2 type disk;backup as compressed backupset database format ‘/orabak/rman/data_%d_%T_%s.bak‘ plus archivelog format ‘/orabak/rman/log_%d_%T_%s.bak‘;release channel d1;release channel d2;}[[email protected] rman]$ cat backup.sh#!/bin/bashexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=ora10export PATH=$ORACLE_HOME/bin:$PATHrman target / @backup.sql log backup.log

To perform a backup:
[email protected] rman]$ nohup sh backup.sh &

[[email protected] rman]$ tail-200f backup.log Recovery manager:release 10.2.0.5.0-production on Wed Jan 4 21:47:  2017Copyright (c) 1982, Oracle. All rights reserved.connected to target database:ora10 (dbid=914008358) rman> run {2> allocate channel D1 type disk; 3> Allocate channel D2 type disk;4> backup as compressed backupset database format '/orabak/rman/data_%d_%t_%s.bak ' Plus archivelog format '/orabak/rman/log_%d_%t_%s.bak ';5> release channel d1;6> release channel d2;7>}8> USI NG target Database control file instead of recovery catalogallocated channel:d1channel d1:sid=143 devtype=diskallocated  Channel:d2channel d2:sid=142 devtype=diskstarting backup at 04-jan-17current log archivedchannel d1:starting compressed Archive Log Backupsetchannel d1:specifying archive log (s) in Backup SetInput archive log thread=1 sequence=46 recid=45 s Tamp=932415090input Archive Log thread=1 sequence=47 recid=46 stamp=932420845channel d1:starting piece 1 At 04-jan-17channel d2:starting Compressed archive log backupsetchannel d2:specifying archive log (s) in Backup SetInput Archive log thread=1 sequence=43 recid=42 stamp=932414314input archive log thread=1 sequence=44 recid=43 stamp=932414362i Nput Archive Log thread=1 sequence=45 recid=44 stamp=932415036channel d2:starting piece 1 at 04-jan-17channel D1:finishe D piece 1 at 04-jan-17piece Handle=/orabak/rman/log_ora10_20170104_102.bak tag=tag20170104t214725 Comment=NONEchannel D1:backup set complete, elapsed Time:00:00:02channel d2:finished piece 1 at 04-jan-17piece handle=/orabak/rman/log_ora1 0_20170104_103.bak tag=tag20170104t214725 Comment=nonechannel d2:backup set complete, elapsed time:00:00:02finished Backup at 04-jan-17starting backup @ 04-jan-17channel d1:starting compressed full datafile backupsetchannel d1:specifyi ng DataFile (s) in Backupsetinput datafile fno=00001 name=/oradata/ora10/ora10/datafile/o1_mf_system_d5tw48bw_. Dbfinput datafile fno=00002 name=/oradata/orA10/ora10/datafile/o1_mf_undotbs1_d5tw48d8_.dbfinput datafile fno=00004 name=/oradata/ora10/ora10/datafile/o1_mf_  Users_d5tw48dg_.dbfchannel d1:starting piece 1 at 04-jan-17channel d2:starting compressed full datafile Backupsetchannel D2:specifying DataFile (s) in Backupsetinput datafile fno=00003 name=/oradata/ora10/ora10/datafile/o1_mf_sysaux_ D5tw48c3_.dbfinput datafile fno=00007 Name=/oradata/ora10/ora10/datafile/o1_mf_forhapoc_d5x5bm2b_.dbfinput DataFile fno=00005 name=/oradata/ora10/ora10/datafile/o1_mf_dbs_d_ji_d5x45hbj_.dbfinput datafile fno=00006 name=/ Oradata/ora10/ora10/datafile/o1_mf_dbs_i_ji_d5x45jd9_.dbfchannel d2:starting piece 1 at 04-JAN-17channel d2:finished Piece 1 at 04-jan-17piece Handle=/orabak/rman/data_ora10_20170104_105.bak tag=tag20170104t214728 Comment=NONEchannel D2:backup set complete, elapsed Time:00:00:35channel d1:finished piece 1 at 04-jan-17piece Handle=/orabak/rman/data_ora 10_20170104_104.bak tag=tag20170104t214728 Comment=nonechannel D1:backupSet complete, elapsed time:00:00:42finished backup @ 04-jan-17starting backup at 04-jan-17current log archivedchannel d1 : Starting compressed archive log Backupsetchannel d1:specifying archive log (s) in Backup SetInput archive log thread=1 s  equence=48 recid=47 Stamp=932420890channel d1:starting piece 1 at 04-jan-17channel d1:finished piece 1 at 04-JAN-17piece Handle=/orabak/rman/log_ora10_20170104_106.bak tag=tag20170104t214810 Comment=nonechannel d1:backup set Complete, Elapsed time:00:00:02finished Backup at 04-jan-17starting Control File and SPFILE autobackup at 04-jan-17piece handle=/or abak/rman/20170104/controlfilec-914008358-20170104-06 comment=nonefinished Control File and SPFILE autobackup at 04- jan-17released channel:d1released channel:d2recovery Manager complete.  [1]+ done nohup sh backup.sh[[email protected] rman]$

After the backup is complete, copy the backup set to the standby machine.

3. Modifying source Production library parameters

View Main library files (data files, temp files, redo log files) directory:

select name from v$datafile union allselect name from v$tempfile union allselect member from v$logfile;

To modify source production library parameters:

--Set the Convert parameter (I have both the source and target sides set db_create_file_dest, without setting these two parameters) alter system set log_file_name_convert= "," Scope=spfile Alter system set db_file_name_convert= ', ' scope=spfile;--set the database to force Loggingalter DB Force logging;--settings db_ Unique_name, Log_archive_config (here the db_unique_name of the main library, due to the minimal impact on the existing production environment is not changed) alter system set Db_unique_name= ' ORA10 ' Scope=spfile;alter system set log_archive_config= ' dg_config= (ORA10,ORA10DG) '; --Archive Log directory alter system set log_archive_dest_1= ' Location=/orabak/arch valid_for= (all_logfiles,all_roles) db_unique_ NAME=ORA10 '; alter system set log_archive_dest_2= ' SERVICE=ORA10DG ASYNC lgwr valid_for= (online_logfiles,primary_role) DB_UNIQUE_NAME=ORA10DG ';--Archive log file naming rules alter system set log_archive_format= ' Arch_%r_%t_%s.arc ' SCOPE=SPFILE;--DG Fal_ Server and Fal_clientalter system set fal_server= ' ORA10DG '; alter system set fal_client= ' ora10 ';--set Standby_file_ Management for the automatic alter system set Standby_file_management=auto; --Set up the Repository log file group, typically the target library log file group +1alter database add standby logfile Group 11 Size 52428800; ALTER DATABASE ADD standby logfile group size 52428800; ALTER DATABASE ADD standby logfile group size 52428800;  ALTER DATABASE ADD standby logfile group size 52428800;

4. Configure Tnsnames.ora

Dataguard environment, log_archive_config configuration, is used to tnsnames.ora the alias in the configuration file.

--配置tnsnames.ora在主库所有节点上的tnsnames.ora添加相应的连接串，并传给备库。---主库tnsnames.ora添加vi $ORACLE_HOME/network/admin/tnsnames.oraORA10 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ora10)    )  )ORA10DG =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ora10dg)    )  )  

Upload the Tnsnames.ora to the repository, and if you have other content before tnsnames, you can choose to add content, as long as the master repository can be guaranteed access to each other.

5. Synchronizing Password files

--密码文件从主库拷贝到备库[[email protected] dbs]$ scp orapwora10 192.168.1.31:/u01/app/oracle/product/10.2.0/db_1/dbs/[email protected]‘s password: orapwora10                                                                                                                                                  100% 1536     1.5KB/s   00:00    

6. Configuring the Pfile File

Dataguard environment, the main library's parameter files need to be uploaded to the repository for modification, especially note that the path exists in the repository and has appropriate permissions.

--创建pfile文件从主库的spfile中导出pfile文件，上传到备库，并做适当的修改调整。--在主库上创建pfile文件，并上传到备库create pfile=‘/tmp/init.ora‘ from spfile;SQL> create pfile=‘/tmp/init.ora‘ from spfile;File created.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[[email protected] dbs]$ scp /tmp/init.ora 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT![email protected]‘s password: init.ora                                                                                                                                                    100% 1430     1.4KB/s   00:00    

--Prepare the final pfile parameter file
[email protected] admin]$ Vi/tmp/init.ora

*.audit_file_dest= '/u01/app/oracle/admin/ora10/adump ' *.background_dump_dest= '/u01/app/oracle/admin/ora10/bdump ' *.compatible= ' 10.2.0.5.0 ' *.core_dump_dest= '/u01/app/oracle/admin/ora10/cdump ' *.db_block_size=8192*.db_create_ file_dest= '/oradata ' *.db_domain= ' *.db_file_multiblock_read_count=16*.db_name= ' ora10 ' *.db_recovery_file_dest= ' /orabak/flash_recovery_area ' *.db_recovery_file_dest_size=2147483648*.db_unique_name= ' ora10dg ' *.dispatchers= ' ( PROTOCOL=TCP) (service=ora10xdb) ' *.fal_client= ' ora10dg ' *.fal_server= ' ora10 ' *.job_queue_processes=10*.log_ Archive_config= ' dg_config= (ora10dg,ora10) ' *.log_archive_dest_1= ' Location=/orabak/arch VALID_FOR= (ALL_LOGFILES, all_roles) db_unique_name=ora10dg ' *.log_archive_dest_2= ' service=ora10 ASYNC lgwr valid_for= (ONLINE_LOGFILES, Primary_role) db_unique_name=ora10 ' *.log_archive_format= ' Arch_%r_%t_%s.arc ' *.open_cursors=300*.pga_aggregate_ Target=96468992*.processes=150*.remote_login_passwordfile= ' EXCLUSIVE ' *.sga_target=290455552*.standby_file_ Management= ' AUTO ' *.undo_management= ' AUTO ' *.undo_tablespace= ' UNDOTBS1 ' *.user_dump_dest= '/u01/app/oracle/admin/ora10/udump ' 

To start the repository to the Nomount state:

SQL> startup nomount pfile=‘/tmp/init.ora‘;ORA-01261: Parameter db_recovery_file_dest destination string cannot be translatedORA-01262: Stat failed on a file destination directoryLinux-x86_64 Error: 2: No such file or directorySQL> exitDisconnected[[email protected] admin]$ mkdir -p /orabak/flash_recovery_area[[email protected] admin]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 4 23:24:25 2017Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL>  startup nomount pfile=‘/tmp/init.ora‘;ORACLE instance started.Total System Global Area  293601280 bytesFixed Size                  2095768 bytesVariable Size             146802024 bytesDatabase Buffers          138412032 bytesRedo Buffers                6291456 bytesSQL> exit

7. Create a Repository control file

In the Dataguard environment, you need to create a control file for the repository in the main library and transfer it to the repository.

--Create a control file
Master library execution, creating a repository control file

alter database create standby controlfile as ‘/tmp/control01.ctlbak‘;[[email protected] dbs]$ scp /tmp/control01.ctlbak 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT![email protected]‘s password: control01.ctlbak                                                                                                                                            100% 6928KB   6.8MB/s   00:00    

8. Restore Repository 8.1 Start the database to the Nomount state on the standby

#创建SPFILESQL>create SPFILE from pfile=‘/tmp/init.ora‘;#启动到nomount状态startup nomount

8.2 Starting the database to mount state

Restore standby controlfile from '/tmp/control01.ctlbak ';
ALTER DATABASE Mount;

You may need to register the backup set manually;
Crosscheck backupset;
Catalog start with '/orabak/rman/';

8.3 Restoring a backup

vi/home/oracle/scripts/restore.sh

rman target / <<EOF! > ora10_restore.logrun {allocate channel d1 type disk;allocate channel d2 type disk;restore database;release channel d1;release channel d2;}exit;EOF!

Nohup SH restore.sh &

vi/home/oracle/scripts/recover.sh

rman target / <<EOF! > ora10_recover.logrun {allocate channel d1 type disk;allocate channel d2 type disk;recover database;release channel d1;release channel d2;}exit;EOF!

Nohup SH recover.sh &

9. Turn on the log app

Standby library to open the log app

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

Switchover test

Switchover, primary and standby role swaps.
Note that the host's Crontab timed task acknowledgement is also migrated correctly.

--源生产库：SELECT SWITCHOVER_STATUS FROM V$DATABASE;alter database commit to switchover to physical standby with session shutdown;--备库（switchover为新生产库）：SELECT SWITCHOVER_STATUS FROM V$DATABASE;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;ALTER DATABASE OPEN;--返回源生产库执行（switchover为新备库）：shutdown immediatestartup mountALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

If you find that the repository does not have real-time recovery, reset the status of the link to switch logs again try:

SQL> alter system set log_archive_dest_state_2=defer;SQL> alter system set log_archive_dest_state_2=enable;SQL> alter system switch logfile;

11. Create a restore point, activate the repository test 11.1 create a Restore dot

1) Set the flashback area and size on the standby library

SQL> alter system set db_recovery_file_dest=‘/orabak/flash_recovery_area‘;SQL> alter system set db_recovery_file_dest_size=1000g;SQL> select current_scn||‘‘ from v$database;

2) Standby library cancels the redo log app and creates a database flash back point

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;--这里注意下，如果你的备库环境是RAC，需要关闭其他节点，然后才可以创建restore point：SQL> drop restore point before_application_test;SQL> CREATE RESTORE POINT before_application_test GUARANTEE FLASHBACK DATABASE;Restore point created.

11.2 Activating the Standby library

1) Stop logs from the main library to the repository

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;

2) Activate the standby library

SQL> alter system set job_queue_processes = 0 scope=both sid=‘*‘;SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;SQL> shutdown immediateSQL> startup

3) Check Dblinks
SELECT * from Dba_db_links;
If there are dblink need to be removed or otherwise disabled;

4) notify the new app IP and start testing your application

Note: In order to prevent the test process has the job or Dblink modify the other database information, generally to modify the job_queue_processes parameter, delete Dblink.
--Parameters
alter system Set job_queue_processes = 0 Scope=both sid= ";
--dblinks
Select from Dba_db_links;

11.3 Flash back Recovery standby Library

1) Restore the Standby library

SQL> shutdown immediateSQL> startup mount;SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_test;SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;SQL> shutdown immediateSQL> startup mountSQL> alter database recover managed standby database using current logfile disconnect;恢复参数job_queue_processes设置值：alter system set job_queue_processes = 10 scope=both sid=‘*‘;恢复参数db_recovery_file_dest_size设置值：alter system set db_recovery_file_dest_size=2g;

2) Restore Log transfer
Recover the log of the main library to the standby library

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

Failover test

failover, the source production library is no longer available, the standby library is forced to activate the main library;

#取消DG应用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;#重启下数据库（建议）shutdown immediate;startup#操作不可逆，确定实际情况需要failoverALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force;SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; #尝试常规切换为主库ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;如果这一步的常规切换失败，提示需要介质恢复，那么： 1）恢复备库 recover standby database until cancel; 2）激活备库 alter database activate standby database;#最后重新启动数据库shutdown immediate;startup

After failover, the DG will be re-built even if the original repository is restored.

Oracle 10GR2 Dataguard Build (non-duplicate mode)