20200416-Oracle 19C Data Guard基础运维-01部署Physical Standby

配置说明：

原理：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/data-guard-concepts-and-administration.pdf

(1) 主库：开启归档模式
(2) 主库：开启强制写日志功能
(3) 主库：关闭闪回
(4) 主库：配置静态监听 , 配置 tnsnames 文件
(5) 主库：增加 standby logfile 文件
(6) 主库：修改参数文件
(7) 主库 : 拷贝主库文件到备库
(8) 备库：配置静态监听 , 配置 tnsnames.ora 文件
(9) 备库：修改参数文件和口令文件
(10) 备库：根据据参数文件创建相应的目录
(11) 备库： startup nomount
(12) 主库 : 通过 rman duplicate 方式进行备库恢复
(13) 验证是否搭建成功  

(1) 主库：开启归档模式

SQL> sqlplus / as sysdba
SQL> alter system set log_archive_dest_1='location=/arch;
SQL> alter system set log_archive_format = "cjcpdb_%t_%s_%r.arc" scope=spfile;

SQL> shutdown immediate;
SQL> startup mount;

SQL> alter database archivelog;
SQL> alter database open;

(2) 主库：开启强制写日志功能

SQL> select force_logging from v$database;
SQL> alter database force logging;

(3) 主库：关闭闪回

SQL> select flashback_on from v$database;
SQL> alter database flashback off;

(4) 主库：配置静态监听 , 配置 tnsnames 文件

[oracle@cjcos01 ~]$ cd $ORACLE_HOME/network/admin
[oracle@cjcos01 admin]$ vim listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cjcdb)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = cjcdb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@cjcos01 admin]$ vim tnsnames.ora
CJCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cjcdb)
    )
  )

CHENDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = chendb)
    )

CJCPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cjcpdb)
    )
  )

CHENPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cjcpdb)
    )
  )

[oracle@cjcos01 admin]$ lsnrctl stop
[oracle@cjcos01 admin]$ lsnrctl start  

(5) 主库：增加 standby logfile 文件

SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------------------------
/u01/app/oracle/oradata/CJCDB/redo03.log
/u01/app/oracle/oradata/CJCDB/redo02.log
/u01/app/oracle/oradata/CJCDB/redo01.log

SQL> select bytes/1024/1024 from v$log;

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/CJCDB/standby_redo04.log' size 200M;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/CJCDB/standby_redo05.log' size 200M;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/CJCDB/standby_redo06.log' size 200M;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/CJCDB/standby_redo07.log' size 200M;

(6) 主库：修改参数文件

SQL> create pfile from spfile;
[oracle@cjcos01 admin]$ cd $ORACLE_HOME/dbs
[oracle@cjcos01 dbs]$ cp initcjcdb.ora initcjcdb.ora.bak.1

[oracle@cjcos01 dbs]$ vim initcjcdb.ora
*.DB_NAME=cjcdb  
*.DB_UNIQUE_NAME=cjcdb  
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cjcdb,chendb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cjcdb'
*.LOG_ARCHIVE_DEST_2='SERVICE=chendb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chendb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.FAL_SERVER=chendb
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/chenpdb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/chenpdb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/'
*.STANDBY_FILE_MANAGEMENT=AUTO

[oracle@cjcos01 dbs]$ cp spfilecjcdb.ora spfilecjcdb.ora.bak.2
SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup
SQL> show parameter log_archive_dest_2
SQL> alter pluggable database cjcpdb open;

SQL> show pdbs
    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED     READ ONLY  NO
 3 CJCPDB     READ WRITE NO

(7) 主库 : 拷贝主库文件到备库

监听文件，TNS 文件，参数文件、密码文件到备库并改名

[oracle@cjcos01 ~]$ mkdir /home/oracle/dg
[oracle@cjcos01 ~]$ cd $ORACLE_HOME/dbs
[oracle@cjcos01 dbs]$ cp initcjcdb.ora /home/oracle/dg
[oracle@cjcos01 dbs]$ cp orapwcjcdb /home/oracle/dg
[oracle@cjcos01 dbs]$ cd ../network/admin/
[oracle@cjcos01 admin]$ cp listener.ora /home/oracle/dg
[oracle@cjcos01 admin]$ cp tnsnames.ora /home/oracle/dg
[oracle@cjcos01 ~]$ tar -zcvf dg.tar.gz dg/
[oracle@cjcos01 ~]$ scp dg.tar.gz 192.168.31.100:/home/oracle/

(8) 备库：配置静态监听 , 配置 tnsnames.ora 文件

[oracle@cjcos02 ~]$ tar -zxvf dg.tar.gz  
[oracle@cjcos02 dg]$ cd $ORACLE_HOME/network/admin
[oracle@cjcos02 admin]$ mv listener.ora listener.ora.bak
[oracle@cjcos02 admin]$ mv tnsnames.ora tnsnames.ora.bak
[oracle@cjcos02 admin]$ cp /home/oracle/dg/listener.ora .
[oracle@cjcos02 admin]$ cp /home/oracle/dg/tnsnames.ora .
[oracle@cjcos02 admin]$ vim listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = chendb)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = chendb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cjcos02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

[oracle@cjcos02 admin]$ lsnrctl start

(9) 备库：修改参数文件和口令文件

[oracle@cjcos02 admin]$ cd $ORACLE_HOME/dbs
[oracle@cjcos02 dbs]$ cp /home/oracle/dg/initcjcdb.ora .
[oracle@cjcos02 dbs]$ cp /home/oracle/dg/orapwcjcdb .
[oracle@cjcos02 dbs]$ mv orapwcjcdb orapwchendb
[oracle@cjcos02 dbs]$ mv initcjcdb.ora initchendb.ora

[oracle@cjcos02 dbs]$ vim initchendb.ora
:%s/cjcdb/AAA/g  
:%s/chendb/cjcdb/g  
:%s/AAA/chendb/g

------
*.DB_NAME=cjcdb
*.DB_UNIQUE_NAME=chendb
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(chendb,cjcdb)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/flash VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chendb'
*.LOG_ARCHIVE_DEST_2='SERVICE=cjcdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cjcdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
*.FAL_SERVER=cjcdb
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/','/u01/app/oracle/oradata/chendb/chenpdb/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/CJCDB/','/u01/app/oracle/oradata/chendb/','/u01/app/oracle/oradata/CJCDB/cjcpdb/','/u01/app/oracle/oradata/chendb/chenpdb/'
*.STANDBY_FILE_MANAGEMENT=AUTO

(10) 备库：根据据参数文件创建相应的目录

[root@cjcos02 ~]# mkdir /arch
[root@cjcos02 ~]# chown oracle.oinstall /arch
[oracle@jch ~]$ cd /u01/app/oracle/admin/
[oracle@cjcos02 admin]# mkdir chendb/{adump,dpdump,pfile} -p
[oracle@cjcos02 chendb]# cd /u01/app/oracle/oradata/
[oracle@cjcos02 oradata]# mkdir chendb/{chenpdb,cjcpdb,pdbseed} -p

(11) 备库： startup nomount

[oracle@cjcos02 ~]$ export ORACLE_SID=chendb
[oracle@cjcos02 ~]$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount

(12) 主库 : 通过 rman duplicate 方式进行备库恢复

[oracle@cjcos01 ~]$ export ORACLE_SID=cjcdb
[oracle@cjcos01 ~]$ rman target / auxiliary sys/oracle@chendb
RMAN> duplicate target database for standby from active database;

(13) 验证是否搭建成功

主库：

SQL> col dest_name for a25
SQL> select dest_name,status from v$archive_dest_status;
DEST_NAME     STATUS
------------------------- ---------
LOG_ARCHIVE_DEST_1   VALID
LOG_ARCHIVE_DEST_2   VALID

SQL> archive log list;
SQL> alter system switch logfile;

备库：

SQL> archive log list;
SQL> select process, pid, status, client_process from v$managed_standby;
PROCESS   PID      STATUS CLIENT_P
--------- ------------------------ ------------ --------
ARCH   11867      CONNECTED ARCH
DGRD   11869      ALLOCATED N/A
DGRD   11871      ALLOCATED N/A
ARCH   11873      CONNECTED ARCH
ARCH   11875      CONNECTED ARCH
ARCH   11877      CONNECTED ARCH
RFS       12558      IDLE     Archival
RFS       12560      IDLE     LGWR
RFS       12565      IDLE     UNKNOWN
9 rows selected.

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE,SWITCHOVER_STATUS FROM V$DATABASE;
PROTECTION_MODE      PROTECTION_LEVEL   ROLE      SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY RECOVERY NEEDED

测试主库 CDB$ROOT 数据同步

备库：

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

主库：

SQL> create table test1 as select level as id from dual connect by level<=3;  

备库：

SQL> select * from test1;
ID
----------
 1
 2
 3

测试 PDB 数据同步

主库：

SQL> conn cjc/cjc@cjcpdb
Connected.

SQL> create table tt1 as select level as id from dual connect by level<=3;
Table created.

备库：

SQL> conn cjc/cjc@chenpdb
Connected.

SQL> select * from tt1;
ID
----------
 1
 2
 3

欢迎关注我的微信公众号"IT小Chen"