Oracle-DataGuard

Oracle容灾物理DataGuard

工作前准备

数据库版本	windows环境，数据库版本与生产库保持一致
数据同步方式	物理DG
实例名	生产库dsemr，备库dsemrdg

一、oracle主服务器检查和操作

1. 1检查主服务器是否开启归档日志

        打开dos窗口:

Administrator> sqlplus / as sysdba

SQL>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination D:\app\Administrator\oradata\archlog

Oldest online log sequence 17238

Next log sequence to archive 17240

Current log sequence 17240

SQL> 

如此 Automatic archival 状态为 Enabled 即为已开启,否则需要建立相应目录,并重启数据库开启归档。

1.2开启oracle归档日志

    另外打开一个dos窗口，并建立归档日志文件夹    

Administrator>d:

Administrator>cd D:\app\Administrator\oradata\

Administrator>md archlog

Administrator>sqlplus / as sysdba

SQL>alter system set log_archive_dest_1='location= D:\app\Administrator\oradata\archlog';

SQL>shutdown immediate; #立即关闭  

SQL>startup mount; #加载控制文件，让实例和数据库相关联  

SQL>alter database archivelog; # 归档模式

SQL>alter database open; #根据控制文件找到并打开数据文件和日志文件，从而打开数据库

SQL> select status from v$instance; #查看数据库是否启动

SQL> alter database force logging;  #主库开启归档并改为强制日志模式

二、安装备库，添加监听、备库实例（dsemrdg）

三、主备库listener 、tnsnames中添加dsemr、dsemrdg，并相互ping通

 3.1 备库添加静态监听,添加红色字体部分（根据本机配置修修改路径）

编辑D:\app\Administrator\product\12.1.0\dbhome_1\network\admin\listener.ora 

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools..

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

    (SID_DESC =

      (GLOBAL_DBNAME = dsemrdg)

      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)

      (SID_NAME = dsemrdg)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

ADR_BASE_LISTENER = D:\app\Administrator  # ADR_BASE_LISTENER 默认监听日志配置路径

DIAG_ADR_ENABLED_LISTENER=OFF 

     3.2 添加主库tns连接

        编辑：D:\app\Administrator\product\12.1.0\dbhome_1\network\admin\nsnames.ora

末行添加：

dsemr =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dsemr)

    )

  )

     3.3 主库配置tnsname

编辑：D:\app\Administrator\product\12.1.0\dbhome_1\network\admin\nsnames.ora

末行添加：

dsemrdg =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dsemrdg)

    )

  )

     3.4 主备库PING通测试

         1：主库ping备库

            打开dos窗口，并执行tnsping命令

Administrator>tnsping dsemrdg

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.7)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dsemrdg)))

OK (0 msec)

3.5 备库PING通测试

    1：备库ping主库

    打开dos窗口，并执行tnsping命令

Administrator>tnsping dsemr

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dsemr)))

OK (0 msec) 

四、主库rman备份与控制文件建立

4.1 rman备份主库

      另外打开一个dos窗口，并进入rman管理

Administrator>rman target /

run {

allocate channel c1 type disk maxpiecesize=20g;

allocate channel c2 type disk maxpiecesize=20g;

allocate channel c3 type disk maxpiecesize=20g;

allocate channel c4 type disk maxpiecesize=20g;

backup as compressed backupset database format 'E:\app\oracleadmin\oradata\rman\db_%d_%s_%p_%u_%T.dbf';

sql "alter system archive log current";

crosscheck backup;

delete noprompt obsolete;

delete noprompt expired backup;

backup as compressed backupset archivelog all format 'E:\app\oracleadmin\oradata\rman\arc_%d_%u_%T.arc' delete all input;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

5.主库上为备库建立控制文件

SQL>alter database create standby controlfile as 'E:\app\oracleadmin\oradata\rman\STANDBY.CTL' reuse;

6.主库建立pfile

SQL>create pfile from spfile;

7.拷贝主库的参数文件

7.1 拷贝主库的参数文件initdsemr.ora、口令文件PWDdsamr.ora、rman备份、standby控制文件standby.ctl，到备库的对应位置，拷贝到windows下oracle的D:\app\Administrator\product\11.2.0\dbhome_1\database

7.2 备库操作拷贝文件，改名

参数文件 : D:\app\Administrator\product\12.1.0\dbhome_1\database\initdsemr.ora 改为initdsemrdg.ora

口令文件 : D:\app\Administrator\product\12.1.0\dbhome_1\database\PWDdsemr.ora 改为 PWDdsemrdg.ora

8.在备库文本参数文件修改或添加以下内容：

//*.control_files='/oradata/test/control01.ctl' ##此文件既是上面步骤拷贝的standby控制文件standby.ctl

orcl.__db_cache_size=3372220416

orcl.__java_pool_size=33554432

orcl.__large_pool_size=50331648

orcl.__oracle_base='D:\app\Administrator' #ORACLE_BASE set from environment

orcl.__pga_aggregate_target=1056964608

orcl.__sga_target=4194304000

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=704643072

orcl.__streams_pool_size=0

*.audit_file_dest='D:\app\Administrator\admin\dsemrdg\adump'

*.audit_trail='NONE'

*.compatible='12.1.0.0.0'

*.control_files='D:\app\Administrator\oradata\dsemrdg\control01.ctl' #,' D:\app\Administrator\fast_recovery_area\dsemrdg\control02.ctl'#

*.db_block_size=8192

*.db_domain=''

*.db_name='dsemr'

#*.db_recovery_file_dest='D:\app\Administrator\fast_recovery_area'

#*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='D:\app\Administrator'

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

*.log_archive_dest_1='location=D:\app\Administrator\oradata\archlog\'

*.open_cursors=300

*.pga_aggregate_target=1048576000

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=4194304000

*.sga_target=4194304000

*.undo_tablespace='UNDOTBS1'

db_unique_name=dsemrdg

log_archive_config='dg_config=(dsemr,dsemrdg)'

log_archive_dest_1='location=D:\app\Administrator\oradata\archlog\valid_for=(ALL_LOGFILES,all_roles) db_unique_name=dsemrdg'

log_archive_dest_2='service=dsemr lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=dsemr'

log_archive_max_processes=6

fal_server=dsemr

fal_client=dsemrdg

standby_file_management=auto

保存退出

9.在服务中停止windwos下oracleserviceorcldg服务

拷贝D:\app\Administrator\oradata\rman\standby.ctl 到 D:\app\Administrator\oradata\dsemrdg\下 ，改名并覆盖control01.ctl；

修改D:\app\Administrator\product\12.1.0\dbhome_1\database\SPFILEORCLDG.ORA 为.bak文件停用原来配置。

修改好后，重启windwos下oracleserviceorcldg服务

10.将数据库启动到mount状态：

SQL>shutdown immediate;

SQL>startup mount pfile='D:\app\Administrator\product\12.1.0\dbhome_1\database\INITdsemrdg.ORA';

11.备库建立spfile

SQL> create spfile from pfile=' D:\app\Administrator\product\12.1.0\dbhome_1\database\INITdsemrdg.ORA';

12.rman恢复(备库)

另开一个dos窗口进入sqlplus 生成rman恢复脚本

Administrator>sqlplus / as sysdba

SQL>set head off

SQL>set lines 100

SQL>set pages 500

SQL>col name for a60

SQL>set feedback off

SQL>select 'set newname for datafile '||file#||' to '||''''||name||''''||';' from v$datafile;

SQL>select 'set newname for tempfile '||file#||' to '||''''||name||''''||';' from v$tempfile;

执行结果拷贝到文档编辑rman恢复命令，注意路径修改dsemr需要修改成备库的dsemrdg

set newname for datafile 1 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMR\SYSTEM01.DBF';

set newname for datafile 2 to 'D:\APP\ADMINISTRATOR\PRODUCT\12.1.0\DBHOME_1\DATABASE\TS_DS_EMR';

set newname for datafile 3 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMR\SYSAUX01.DBF';

set newname for datafile 5 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMR\UNDOTBS01.DBF';

set newname for datafile 6 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMR\USERS01.DBF';

set newname for tempfile 1 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMR\TEMP01.DBF';

将上面的输出路径复制到下面 ,并修改D:\APP\ADMINISTRATOR\ORADATA\DSEMR\为D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\：

另外打开一个dos窗口，并进入rman

Administrator>rman target /

RMAN>list backup summary；    #查看一下需要恢复的文件加载次数，如果没有执行下面命令，如果有，直接开始恢复。

RMAN> catalog start with 'd:\app\Administrator\oradata\rman';

run {

allocate channel c1 type disk maxpiecesize=20g;

allocate channel c2 type disk maxpiecesize=20g;

allocate channel c3 type disk maxpiecesize=20g;

allocate channel c4 type disk maxpiecesize=20g;

set newname for datafile 1 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\DATAFILE\SYSTEM01.DBF';

set newname for datafile 2 to 'D:\APP\ADMINISTRATOR\PRODUCT\12.1.0\DBHOME_1\DATABASE\TS_DS_EMR';

set newname for datafile 3 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\DATAFILE\SYSAUX01.DBF';

set newname for datafile 5 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\DATAFILE\UNDOTBS01.DBF';

set newname for datafile 6 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\DATAFILE\USERS01.DBF';

set newname for tempfile 1 to 'D:\APP\ADMINISTRATOR\ORADATA\DSEMRDG\DATAFILE\TEMP01.DBF';

restore database;

switch datafile all;

recover database;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

}

13. 修改主库参数，此项目在主库上执行。

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(dsemr,dsemrdg)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\APP\ADMINISTRATOR\ORADATA\archlog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dsemr';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=dsemrdg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dsemrdg';

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

alter system set FAL_SERVER=dsemrdg;

alter system set FAL_CLIENT=dsemr;

alter system set standby_file_management=auto;

create pfile from spfile;

14.备库添加redo文件

查看主库D:\APP\ADMINISTRATOR\ORADATA\dsemr\redo*.log文件大小，并修改size 50m reuse大小。

SQL>alter database add standby logfile 'D:\app\Administrator\oradata\dsemrdg\std_redo01.log' size 50m reuse;

SQL>alter database add standby logfile 'D:\app\Administrator\oradata\dsemrdg\std_redo02.log' size 50m reuse;

SQL>alter database add standby logfile 'D:\app\Administrator\oradata\dsemrdg\std_redo03.log' size 50m reuse;

SQL>alter database add standby logfile 'D:\app\Administrator\oradata\dsemrdg\std_redo04.log' size 50m reuse;

15. 查看主备库归档是否异常

SQL>col dest_name for a30

SQL>col error for a20

SQL>select dest_name,status,error,target,process from v$archive_dest where rownum<6;

16. 备库open

SQL> alter database open;         

执行归档恢复。

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

使用当前日志文件从会话断开连接更改数据库恢复托管备用数据库；

Database altered.

# 备库重启后，执行上面命令同步恢复数据

17. 主备执行此命令，查看是否一致

SQL> select sequence#,applied from v$archived_log order by sequence#;

 SEQUENCE# APPLIED

---------- ---------

        16 YES

        17 YES

        18 YES

18.使用工具打开主备库，检查一下数据是否同步成功，没有问题则退出所有dos窗口，完成此次dg搭建。

19.查看没有被dg还原的归档日志

select l."RECID",l."NAME",l."ARCHIVED",l."APPLIED",l."NEXT_TIME",l."COMPLETION_TIME"  
from v$archived_log l where applied='NO'  Order By Sequence#