第一步: 搭建19c ADG
Creating Standby from rman backup–19.3
環(huán)境：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.主庫：增加如下參數(shù)
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
–修改參數(shù)
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.啟數(shù)據(jù)庫到mount,mount之后，數(shù)據(jù)庫已經(jīng)自動傳輸日志了
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.備庫恢復數(shù)據(jù)庫
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： 主庫創(chuàng)建新PDB不加STANDBYS參數(shù)
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.
–主庫創(chuàng)建測試表：
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
結論：默認情況下，主庫創(chuàng)建pdb，備庫也會同步創(chuàng)建。
--------------------------------------------------------------------------------------------
場景2：主庫創(chuàng)建新PDB加STANDBYS參數(shù)（Doc:1916648.1 ）
本說明描述了在創(chuàng)建可插入數(shù)據(jù)庫語句上使用 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模式下，備庫執(zhí)行單個pdb恢復（備庫端執(zhí)行）
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; --恢復完執(zhí)行新增同步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 定義已經(jīng)被同步創(chuàng)建，但是實際數(shù)據(jù)文件并未被創(chuàng)建,通過備份恢復
--------------------------------------------------------------------------------------------------------------------------------
場景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名稱，備庫也會同步修改。
