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

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"，共同学习，共同成长！！！