19.3 RAC主库 到19.3单实例备库dataguard 搭建

19.3 RAC主库 到19.3单实例dataguard 搭建

系统环境：

准备：

检查主库的force logging

Select inst_id,force_logging from gv$database

Alter database force logging;(修改完立刻生效)

 

检查数据库的归档模式

Archive log list;

改归档模式

Srvctl stop database -d orcl -o immediate

Srvctl status database -d orcl

Srvctl stop database -d orcl -o mount

Alter database archivelog;

Alter database open;

Archive log list;

 

修改主库的注册信息

Srvctl config database -d orcl -a

Srvctl remove database -d orcl -f

Srvctl add db -d orclpri -c RAC -o $OrACLE_HOME -p $SPFILE -r primary

Srvctl add instance -d orclpri -i orcl1 -n rac01

Srvctl add instance -d orclpri -i orcl2 -n rac02

Srvctl status db -d orclpri

Srvctl start db -d orclpri

Srvctl config db -d orclpri -a

 

主库添加standby redo log

Select * from v$log;

Select * from v$standby_log;

 

添加redolog

Alter database add standby logfile thread 1 group 5 size 200M,group 6 size 200M,group 7 size 200M;

Alter database add standby logfile thread 2 group 8 size 200M,group 9 size 200M,group 10 size 200M;

Set linesize 1000

Col member for a70

Select * from v$logfile;

 

修改主库参数文件(修改spfile之前一定要备份)

Alter system set db_unique_name=’orclpri’ scope=spfile sid=’*’;

Alter system set log_archive_config=’DG_CONFIG=(orclpri,orclphy)’ sid=’*’;

Alter system set log_archive_dest_1=’LOCATION=use_DB_RECOVERY_FILE_DEST db_unique_name=orclpri valid_for=(ALL_LOGFILES,ALL_ROLES)’ scope=spfile sid=’*’;

Alter system set log_archive_dest_2=’SERVICE=tns_orclphy LGWR ASYNC db_unique_name=orclphy valid_for(ONLINE_LOGFILES,PRIMARY_ROLE)’ sid=’*’;

Alter system set db_file_name_comvert=’+data’,’+data’ scope=spfile sid=’*’;

Alter system set log_file_name_comvert=’+data’,’+data’ scope=spfile sid=’*’;

Alter system set log_archive_dest_state_1=enable sid=’*’;

Alter system set log_archive_dest_state_2=enable sid=’*’;

Alter system set log_archive_max_processes=4 sid=’*’;

Alter system set remote_login_passwordfile=’EXCLUSIVE’ scope=spfile sid=’*’;

Alter system set standby_file_management=’AUTO’ sid=’*’;

Alter system set fal_server=’orclphy’ sid=’*’;

Alter system set fal_client=’orclpri’ sid=’*’;

 

配置主库和备库的监听

主库：未做修改，两个节点保持一致，监听文件在grid-home目录下

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

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

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

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent - Disabled by Agent because REMOTE_REGISTRATION_ADDRESS is set

 

备库

在grid用户下，可以设置针对DG的监听器，也可以使用默认的listener监听器。

#Backup file is  /oracle/app/grid/crsdata/orclphy/output/listener.ora.bak.orclphy.grid line added by Agent

# listener.ora Network Configuration File: /oracle/app/12.2.0/grid/product/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

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

    )

  )

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = pdb)

(ORACLE_HOME = /oracle/app/oracle/product/12.2.0/db_1)

(SID_NAME = orclphy)

)

(SID_DESC =

(GLOBAL_DBNAME = orclphy)

(ORACLE_HOME = /oracle/app/oracle/product/12.2.0/db_1)

(SID_NAME = orclphy)

)

)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent

 

配置主库和备库的tnsname.ora

Vi $ORACLE_HOME/network/admin/tnsnames.ora

Vi $GRID_HOME/network/admin/tnsnames.ora

注意：tns文件是在oracle用户下配置，但是也需要将oracle用户下的tns文件的内容同步更新的grid用户下，内容如下

orclpri =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclpri)

)

)

 

orclphy =

(DESCRIPTION =

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

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orclphy)

)

)

 

在节点cehsi

Tnsping orcipri

Tbsping orclphy

配置主库的密码文件，三个节点的密码文件密码保持一致

Orapwd file=orapwrac01 password=ziqiyuan format=12 force=y

Orapwd file=orapwrac02 password=ziqiyuan format=12 force=y

Orapwd file=orapworclphy password=ziqiyuan format=12 force=y

 

启动备份库到nomount状态

export ORACLE_SID=orclphy

echo ‘db_name=orcl’ >$ORACLE_HOME/dbs/initorclphy.ora

Sqlplus / as sysdba

Startup nomount pfile=$ORACLE_HOME/dbs/initorclphy.ora

Rman target database sys/ziqiyuan@orclpri auxiliary database sys/ziqiyuan@orclphy

 

duplicate target database for standby nofilenamecheck from active database dorecover spfile

set db_unique_name='orclphy'

set log_archive_dest_1='LOCATION=+data valid_for=(all_logfiles,all_roles) db_unique_name=orclphy'

set log_archive_dest_2='service=tns_orclpri async lgwr register valid_for=(online_logfile,primary_role) db_unique_name=orclpri'

set fal_server='tns_orclpri'

set fal_client='tns_orclphy'

set control_files='+data','+data'

set db_file_name_convert='+data','+data'

set memory_target='2500m'

set audit_file_dest='/oracle/app/oracle/admin/orcl/adump'

set db_create_file_dest = '+data'

set cluster_database='false'

set log_file_name_convert='+data','+data'

set standby_file_management='AUTO';

复制完成后修改pfile参数 将集群参数删除(thread,undotablespace,sid)

 

srvctl add database -db orclphy -dbname orcl -o /oracle/app/oracle/product/12.2.0/db_1 -p '/oracle/app/oracle/product/12.2.0/db_1/dbs/spfileorclphy.ora' -r physical_standby

启动备库

执行实时同步脚本

SQL>Alter database recover managed standby database using current logfile disconnect;

 

主库操作：

SQL> alter session set container=pdb;

Session altered.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> select * from haha;

        ID

----------

         9

        19

         9

SQL> insert into haha values(1);

1 row created.

SQL> commit;

Commit complete.

 

备库查看同步情况

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDB                            MOUNTED

SQL> alter session set container=pdb;

Session altered.

SQL> alter database open;

Database altered.

SQL> select * from haha;

        ID

----------

         9

         1

        19

         9