Oracle 19C Data Guard 基础运维 -01 部署 Physical Standby
配置说明：
|
|
主库 |
备库 |
|
IP |
192.168.31.90 |
192.168.31.100 |
|
DB |
Oracle 19.3.0.0.0 |
Oracle 19.3.0.0.0 |
|
OS |
RedHat7.5 |
RedHat7.5 |
|
HostName |
cjcos01 |
cjcos02 |
|
DBName |
cjcdb |
cjcdb |
|
DB_UNIQUE_NAME |
cjcdb |
chendb |
|
SERVICE_NAME |
cjcdb |
chendb |
|
PDBName |
cjcpdb |
cjcpdb |
原理：
(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"，共同学习，共同成长！！！
