第一步: 搭建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 <
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名称，备库也会同步修改。