第一步: 搭建19c ADG
Creating Standby from rman backup–19.3
环境：oracle 11.2
主库：db_name ORCLCDB
db_unique_name ORCLCDB
ip地址 172.17.0.2
备库：db_name ORCLCDB
db_unique_name ORCLCDB_DG
ip地址 172.17.0.3
CDB架构：YES
--------------------------
1.确保主库 force logging mode:
alter database force logging;
select force_logging from v$database;
2.确保主库处于归档模式
archive log list
3.拷贝主库密码文件到备库
$ cd ORACLE_HOME/dbs/orapwORCLCDB_DG
4.配置tnsnames.ora并拷贝到备库
ORCLCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCLCDB)
)
)
ORCLCDB_DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCLCDB_DG)
)
)
5.在主库pfile修改之前，拷贝主库的pfile到备库上:
SYS@ORCLCDB> create pfile=’/tmp/pfile.ora’ from spfile;
$ scp /tmp/pfile.ora oracle@172.17.0.3:/tmp
6.主库：增加如下参数
alter system set log_archive_config=‘DG_CONFIG=(ORCLCDB,ORCLCDB_DG)’ scope=both ;
alter system set log_archive_dest_state_2=enable;
alter system set log_archive_dest_2=‘SERVICE=ORCLCDB_DG async lgwr valid_for=(online_logfiles,primary_role) db_unique_name=ORCLCDB_DG’ scope=both ;
alter system set fal_server=ORCLCDB_DG scope=both ;
alter system set fal_client=ORCLCDB scope=both;
alter system set standby_file_management=auto scope=both ;
7.备库：修改pfile
*.audit_file_dest=’/opt/oracle/admin/ORCLCDB/adump’
*.audit_trail=‘db’
*.compatible=‘19.0.0’
*.control_files=’/opt/oracle/oradata/ORCLCDB/control01.ctl’,’/opt/oracle/oradata/ORCLCDB/control02.ctl’
*.db_block_size=8192
*.db_name=‘ORCLCDB’
*.db_unique_name=‘ORCLCDB_DG’
*.db_recovery_file_dest_size=4294967296
*.db_recovery_file_dest=’/opt/oracle/flash_recovery_area’
*.diagnostic_dest=’/opt/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ORCLCDBXDB)’
*.enable_pluggable_database=true
*.local_listener=’’
*.nls_language=‘AMERICAN’
*.nls_territory=‘AMERICA’
*.open_cursors=300
*.pga_aggregate_target=100m
*.processes=640
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.sga_target=1024m
*.undo_tablespace=‘UNDOTBS1’
8.备库启动到nomount mode:
export ORACLE_SID=ORCLCDB_DG
$ sqlplus / as sysdba
SQL> startup nomount pfile=’/tmp/pfile.ora’
SQL> create spfile from pfile=’/tmp/pfile.ora’;
SQL> startup force nomount
–修改参数
alter system set log_archive_config=‘DG_CONFIG=(ORCLCDB,ORCLCDB_DG)’ scope=both ;
alter system set fal_server=ORCLCDB scope=both ;
alter system set fal_client=ORCLCDB_DG scope=both;
alter system set standby_file_management=auto scope=both ;
9.主库备份standby controlfile，拷贝至备库
backup current controlfile for standby format ‘/home/oracle/backup/forStandby_controlfile.bak’;
scp forStandby_controlfile.bak oracle@172.17.0.3:/home/oracle/backup/
10.恢复控制文件：
restore standby controlfile from ‘/home/oracle/backup/forStandby_controlfile.bak’;
11.启数据库到mount,mount之后，数据库已经自动传输日志了
SQL> alter database mount;
主备测试连通性：
sqlplus sys/lhr@ORCLCDB as sysdba
sqlplus sys/lhr@ORCLCDB_DG as sysdba
12.主库做全备,并拷贝至备库
rman target / log=/tmp/backupall_$backtime.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
backup database format ‘/home/oracle/backup/forStandby_%U.bak’;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
}
EOF
scp forStan* oracle@172.17.0.3:/home/oracle/backup/
13.备库恢复数据库
RMAN> catalog start with ‘/home/oracle/backup/’;
RMAN> restore database;
–备库增加standby logfile
alter database add standby logfile thread 1 group 11(’/opt/oracle/oradata/ORCLCDB/redo11.log’) size 200M;
alter database add standby logfile thread 1 group 12(’/opt/oracle/oradata/ORCLCDB/redo12.log’) size 200M;
alter database add standby logfile thread 1 group 13(’/opt/oracle/oradata/ORCLCDB/redo13.log’) size 200M;
alter database add standby logfile thread 1 group 14(’/opt/oracle/oradata/ORCLCDB/redo14.log’) size 200M;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- — ----------
11 1 0 YES UNASSIGNED
12 1 0 YES UNASSIGNED
13 1 0 YES UNASSIGNED
14 1 0 YES UNASSIGNED
14.备库开启应用
SQL> alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database cancel; – 取消日志应用
15.检查Dg
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
------------------ ------------------------ ---------- ----------
ARCH CONNECTED 0 0
DGRD ALLOCATED 0 0
DGRD ALLOCATED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
MRP0 APPLYING_LOG 1 22
RFS IDLE 1 0
RFS IDLE 1 22
RFS IDLE 0 0
RFS IDLE 0 0
11 rows selected.
-----------以上ADG搭建完成---------------------
二、主库PDB相关操作
–场景1： 主库创建新PDB不加STANDBYS参数
create pluggable database testpdb1 ADMIN USER test identified by test
ROLES=(CONNECT) FILE_NAME_CONVERT=(’/opt/oracle/oradata/ORCLCDB/pdbseed’,’/opt/oracle/oradata/ORCLCDB/testpdb1’);
–启动PDB
alter pluggable database testpdb1 open;
–备库检查：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 ORCLPDB1 READ ONLY NO
5 TESTPDB1 MOUNTED
SQL> alter pluggable database TESTPDB1 open;
Pluggable database altered.
–主库创建测试表：
SYS@ORCLCDB> alter session set container=TESTPDB1;
Session altered.
SYS@ORCLCDB> create table test1 as select * from tab;
Table created.
–备库检查：
SQL> alter session set container=TESTPDB1;
Session altered.
SQL> select count(*) from test1;
COUNT(*)
----------
8281
结论：默认情况下，主库创建pdb，备库也会同步创建。
--------------------------------------------------------------------------------------------
场景2：主库创建新PDB加STANDBYS参数（Doc:1916648.1 ）
本说明描述了在创建可插入数据库语句上使用 STANDBYS=NONE 子句，也称为延迟恢复 PDB。
create pluggable database testpdb2 ADMIN USER test identified by test
ROLES=(CONNECT) FILE_NAME_CONVERT=(’/opt/oracle/oradata/ORCLCDB/pdbseed’,’/opt/oracle/oradata/ORCLCDB/testpdb2’) tempfile reuse STANDBYS=NONE;
alter pluggable database testpdb2 open;–启动PDB
–验证主备库datafile
–主库：
SYS@ORCLCDB> alter session set container=testpdb2;
SYS@ORCLCDB> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 /opt/oracle/oradata/ORCLCDB/testpdb2/system01.dbf
24 /opt/oracle/oradata/ORCLCDB/testpdb2/sysaux01.dbf
25 /opt/oracle/oradata/ORCLCDB/testpdb2/undotbs01.dbf
–备库：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB2 MOUNTED
4 ORCLPDB1 READ ONLY NO
5 TESTPDB1 READ ONLY NO
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23 /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00023
24 /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00024
25 /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00025
[oracle@oradg trace]$ ll /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00*
ls: cannot access /opt/oracle/product/19c/dbhome_1/dbs/UNNAMED00*: No such file or directory
–主库 备份新建pdb
rman target /
RMAN> backup format ‘/home/oracle/backup/full_pdb_testpdb2.bak’ pluggable database testpdb2;
scp full_pdb_testpdb2* oracle@172.17.0.3:/home/oracle/backup/
– 备库操作 ：
—RMAN 恢复
cdb处于open read only模式下，备库执行单个pdb恢复（备库端执行）
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY WITH APPL
rman target /
RMAN> catalog start with ‘/home/oracle/backup’;
RMAN>
run{
set newname for datafile 23 to ‘/opt/oracle/oradata/ORCLCDB/testpdb2/system01.dbf’;
set newname for datafile 24 to ‘/opt/oracle/oradata/ORCLCDB/testpdb2/sysaux01.dbf’;
set newname for datafile 25 to ‘/opt/oracle/oradata/ORCLCDB/testpdb2/undotbs01.dbf’;
restore pluggable database testpdb2;
switch datafile all;
}
SQL> shutdown immediate
SQL> startup mount
SQL> SELECT name, open_mode, recovery_status from v$pdbs;
SQL> alter session set container=testpdb2;
SQL> alter pluggable database enable recovery; --恢复完执行新增同步pdb enable recover
SQL> conn / as sysdba
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> alter database open;
SQL> alter pluggable database all open;
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB2 READ ONLY NO
4 ORCLPDB1 READ ONLY NO
5 TESTPDB1 READ ONLY NO
结论：TESTPDB2 定义已经被同步创建，但是实际数据文件并未被创建,通过备份恢复
--------------------------------------------------------------------------------------------------------------------------------
场景3：删除旧的pdb
– 主库删除pdb：TESTPDB1
SQL> alter pluggable database TESTPDB1 close immediate;
SQL> drop pluggable database TESTPDB1 including datafiles;
– 备库检查：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB2 READ ONLY NO
4 ORCLPDB1 READ ONLY NO
结论：主库删除pdb，备库也会同步删除。
场景4：pdb改名
– 主库pdb改名：TESTPDB2–> TESTPDB3
conn / as sysdba
sho pdbs
alter pluggable database TESTPDB2 close immediate;
alter pluggable database TESTPDB2 open restricted;
alter session set container=TESTPDB2;
alter pluggable database TESTPDB2 rename global_name to TESTPDB3;
SQL> conn / as sysdba
alter pluggable database TESTPDB3 close immediate;
alter pluggable database TESTPDB3 open;
sho pdbs
–备库检查：
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB3 MOUNTED
4 ORCLPDB1 READ ONLY NO
SQL> alter pluggable database TESTPDB3 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB3 READ ONLY NO
4 ORCLPDB1 READ ONLY NO
结论：主库修改pdb名称，备库也会同步修改。
