暂无图片
暂无图片
4
暂无图片
暂无图片
2
暂无图片
首页 / Oracle DG PDB数据同步学习

Oracle DG PDB数据同步学习

原创 杨卓 2023-03-28
1920

文档概述

本篇文档对Oracle DG PDB数据同步进行学习测试，模拟pdb新建、新建Pdb数据文件，模拟PDB ADG的数据恢复操作。

学习测试

学习资料

参考

Oracle 21c 新特性 | 基于 PDB 的 ADG
https://cloud.tencent.com/developer/article/2187936
http://t.csdn.cn/sIt7h
参数
enabled_PDBs_on_standby
  参数只在备库设置生效，默认值为*，即不限制pdb的同步，参数主要控制新建的pdb是否同步到备库，
不作用已有pdb的同步
How to Remove One Standby Database from a Data Guard Configuration (Doc ID 2196935.1)
Parameter enabled_pdbs_on_standby and STANDBYS Option With Data Guard Subset Standby 
(Doc ID 2417018.1)	

复制

image.png

验证测试环境主备同步

本次测试环境选择2套19c linux rac 2节点进行测试。

SYS@ora19cfldg1> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
db_name                              string      ora19cf
db_unique_name                       string      ora19cfldg

ora19cf1> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
ora19cf1> show parameter db_name
db_name                              string      ora19cf
db_unique_name                       string      ora19cf


ora19cf1> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CMBPDB                         READ ONLY  NO
         4 ARCHPDB                        READ ONLY  NO
   

--pri test data sync
SYS@ora19cfldg1> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
SYS@ora19cfldg1> insert into a values(1);
1 row created.
SYS@ora19cfldg1> commit;
--adg select 
ora19cf1> select * from a;
        ID
----------
         1

复制

场景一、主库新建一个pdb,ADG是否自动同步主库的数据

pri

create pluggable database pdb_test1 admin user pdbtest identified by "pdbtest" default tablespace pdb_test1 file_name_convert=('+DATADG','+DATADG');
SYS@ora19cfldg1> alter session set container=pdb_test1;
SYS@ora19cfldg1> startup
Warning: PDB altered with errors.
SYS@ora19cfldg1> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDB_TEST1                      READ WRITE YES
   
select * from pdb_plug_in_violations
--这个问题参考mos 2167662.1
---------------------------------------------------------------- ----------
   PDB_TEST1  WARNING             Database option JAVAVM mismatch: PDB installed version NULL. PENDING   Fix the database opt    CDB installed version 19.0.0.0.0.                                     ion in the PDB or the CDB
  PDB_TEST1 ERROR     Sync PDB failed with ORA-959 during '  alt                                                                                               er user  c##lmnruser quota unlimited on USERS container=all'
 PDB_TEST1      ERROR               Sync PDB failed with ORA-959 during 'alter user C##DSGORA quota unlimited on users'

SYS@ora19cfldg1> create tablespace users datafile '+DATADG' size 1m autoextend on maxsize 30g;
SYS@ora19cfldg1> shutdown immediate;
Pluggable Database closed.
SYS@ora19cfldg1> startup
Pluggable Database opened.
SYS@ora19cfldg1> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 PDB_TEST1                      READ WRITE NO
SYS@ora19cfldg1> create table a(id int);

复制

adg test check

ora19cf1> desc a
 Name                               Null?    Type
 -------------------------------------------------------------
 ID                                          NUMBER(38)

复制

–说明新建pdb,adg会自动同步

场景二、备库主动断开某个pdb的数据同步,随后手工启用这个同步

pri
enabled_PDBs_on_standby              string      *

select con_id,dbid,name,open_mode,recovery_status,open_time,total_size,local_undo,
creation_time from v$pdbs;

    CON_ID       DBID NAME                 OPEN_MODE  RECOVERY 
 ------------------------------------------------------------------------------------
         5 2792972738 PDB_TEST1            READ ONLY  ENABLED  


复制

操作流程如下：
禁用某个pdb1的adg的复制;
主库对这个pdb1新建数据文件;
pdb2修改数据,作为对比参照;
观察adg pdb1 的控制文件中是否有这个新增的数据文件;
观察adg pdb2数据复制

1）禁用某个pdb1的adg的复制

alter pluggable database PDB_TEST1 disable recovery;
ORA-01156: recovery or flashback in progress may need access to files

--pdb cancel mrp err
ORA-65040: operation not allowed from within a pluggable database
--conn cdb stop mrp
ora19cf1> alter database recover managed standby database cancel;
--conn pdb 
alter pluggable database pdb_test1 close instances=all;
alter pluggable database PDB_TEST1 disable recovery;
ora19cf1> select 
con_id,dbid,name,open_mode,recovery_status,open_time,total_size,local_undo,
creation_time from v$pdbs;
 DBID        NAME              OPEN_MODE  RECOVERY
 ---------------------------------------------------------
    2792972738 PDB_TEST1            MOUNTED    DISABLED

复制

2）主库对这个pdb1新建数据文件,pdb2修改数据,作为对比参照;

--pri add datafiles 
         5 PDB_TEST1                      READ WRITE NO
SYS@ora19cfldg1> create tablespace test datafile '+DATADG' size 1m autoextend on;
create table test001 tablespace test as select * from dba_objects;
--
alter session set container=archpdb;
         4 ARCHPDB                        READ WRITE NO
create table a(id int);

复制

3）观察adg pdb1 的控制文件中是否有这个新增的数据文件;

--adg check
alter database recover managed standby database disconnect from session;
select process,status from v$managed_standby where process='MRP0';
PROCESS   STATUS
--------- ------------
MRP0      APPLYING_LOG

--pri check
set linesize 200
col dest_name for a40
select dest_name,status,recovery_mode from v$archive_dest_status;
DEST_NAME                                STATUS    RECOVERY_MODE
---------------------------------------- --------- ----------------------------------
LOG_ARCHIVE_DEST_1                       VALID     IDLE
LOG_ARCHIVE_DEST_2                       VALID     MANAGED REAL TIME APPLY WITH QUERY
LOG_ARCHIVE_DEST_3                       VALID     MANAGED REAL TIME APPLY WITH QUERY

--
         5 PDB_TEST1                      MOUNTED
ora19cf1> alter session set container=pdb_test1;


复制

–check pri ,adg vdatafile select file#,STATUS,name,CREATE_BYTES from vdatafile

   457 SYSTEM  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/system.535.1132479147            272629760
   458 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/sysaux.342.1132479147            173015040
   459 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undotbs1.496.1132479147          225443840
   460 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undo_2.495.1132479147            225443840
   461 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/pdb_test1.510.1132479179         104857600
   462 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/users.529.1132479573               1048576
   463 ONLINE  +DATADG/ORA19CFLDG/F7C49942D7AD9657E0534F040E370D68/DATAFILE/test.530.1132485979                1048576

复制

–adg check

   457 SYSOFF  +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/system.327.1132479151                    272629760
   458 RECOVER +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/sysaux.260.1132479153                    173015040
   459 RECOVER +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undotbs1.354.1132479153                  225443840
   460 RECOVER +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undo_2.284.1132479155                    225443840
   461 RECOVER +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/pdb_test1.378.1132479181                 104857600
   462 RECOVER +DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/users.276.1132479573                       1048576
   463 RECOVER /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00463                                                1048576

复制

我们可以发现主备的数据文件数量这块是一致的！因此控制文件种记录的数据是同步的结构信息，虽然数据文件没有同步

4）ADG PDB的恢复

--cdb
alter database recover managed standby database cancel;
--cdb
alter pluggable database PDB_TEST1 enable recovery;
ORA-65046: operation not allowed from outside a pluggable database
--pdb
alter session set container=pdb_test1;
alter pluggable database PDB_TEST1 enable recovery;
ora19cf1> alter pluggable database PDB_TEST1 enable recovery;
alter pluggable database PDB_TEST1 enable recovery
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 463 - see

DBWR trace file
ORA-01111: name for data file 463 is unknown - rename to correct file
ORA-01110: data file 463: '/u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00463'


fal_server                           string      ora19cfldg, ora19cfldg2

--rman

rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST1 to new;
restore pluggable database PDB_TEST1 from service ora19cfldg;
switch datafile all;
}
exit
EOF
connected to target database: ORA19CF (DBID=3781030226)
allocated channel: c1
channel c1: SID=760 instance=ora19cf1 device type=DISK
allocated channel: c2
channel c2: SID=894 instance=ora19cf1 device type=DISK
executing command: SET NEWNAME
Starting restore at 2023-03-26 11:52:41
channel c1: starting datafile backup set restore
channel c1: using network backup set from service ora19cfldg
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00457 to +DATADG
channel c2: starting datafile backup set restore
channel c2: using network backup set from service ora19cfldg
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00458 to +DATADG
channel c1: restore complete, elapsed time: 00:01:40
channel c1: starting datafile backup set restore
channel c1: using network backup set from service ora19cfldg
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00459 to +DATADG
channel c2: restore complete, elapsed time: 00:03:21
channel c2: starting datafile backup set restore
channel c2: using network backup set from service ora19cfldg
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00460 to +DATADG
channel c1: restore complete, elapsed time: 00:01:44
channel c1: starting datafile backup set restore
channel c1: using network backup set from service ora19cfldg
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00461 to +DATADG
channel c2: restore complete, elapsed time: 00:01:43
channel c2: starting datafile backup set restore
channel c2: using network backup set from service ora19cfldg
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00462 to +DATADG
channel c1: restore complete, elapsed time: 00:03:20
channel c1: starting datafile backup set restore
channel c1: using network backup set from service ora19cfldg
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00463 to +DATADG
channel c1: restore complete, elapsed time: 00:00:02
channel c2: restore complete, elapsed time: 00:01:42
Finished restore at 2023-03-26 11:59:30
datafile 457 switched to datafile copy
input datafile copy RECID=2 STAMP=1132487565 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/system.265.1132487565
datafile 458 switched to datafile copy
input datafile copy RECID=3 STAMP=1132487665 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/sysaux.339.1132487665
datafile 459 switched to datafile copy
input datafile copy RECID=4 STAMP=1132487766 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undotbs1.368.1132487765
datafile 460 switched to datafile copy
input datafile copy RECID=5 STAMP=1132487766 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/undo_2.334.1132487765
datafile 461 switched to datafile copy
input datafile copy RECID=6 STAMP=1132487869 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/pdb_test1.267.1132487869
datafile 462 switched to datafile copy
input datafile copy RECID=7 STAMP=1132487969 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/users.365.1132487969
datafile 463 switched to datafile copy
input datafile copy RECID=8 STAMP=1132487969 file name=+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/test.348.1132487969
released channel: c1
released channel: c2

RMAN> 

Recovery Manager complete.

复制

启用PDB的恢复状态

alter session set container=pdb_test1;
alter pluggable database PDB_TEST1 enable recovery;




ora19cf1> alter pluggable database PDB_TEST1 enable recovery;
alter pluggable database PDB_TEST1 enable recovery
*
ERROR at line 1:
ORA-01113: file 463 needs media recovery
ORA-01110: data file 463:
'+DATADG/ORA19CF/F7C49942D7AD9657E0534F040E370D68/DATAFILE/test.348.1132487969'



RMAN> recover datafile 463;

Starting recover at 2023-03-26 14:02:40
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ora19cf1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=758 instance=ora19cf1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=893 instance=ora19cf1 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/26/2023 14:02:49
RMAN-06067: RECOVER DATABASE required with a backup or created control file


ora19cf1> alter session set container=pdb_test1;
ora19cf1> startup
ORA-01147: SYSTEM tablespace file 457 is offline


--
srvctl stop instance -d ora19cf -instance ora19cf1 -f
srvctl start instance -d ora19cf -instance ora19cf1 -startoption mount
--cdb
alter database recover managed standby database cancel;
--pdb
ora19cf1> alter session set container=pdb_test1;
ora19cf1> alter pluggable database PDB_TEST1 enable recovery;

-cdb
ora19cf1> alter database open;

复制

启用pdb的恢复需要ADG实例重启到Mount阶段,才能对这个pdb 禁用恢复调整未启用同步。

恢复测试环境

alter database recover managed standby database disconnect from session;
select process,status from v$managed_standby where process='MRP0';
alter pluggable database pdb_test1 open instances=all;

复制

场景三、创建PDB的时候语法指定NONE

create pluggable database pdb_test2 admin user pdbtest identified by "pdbtest" default tablespace pdb_test1 file_name_convert=('+DATADG','+DATADG') STANDBYS=NONE;
ALTER SESSION SET CONTAINER=PDB_TEST2;
STARTUP
CREATE TABLE A(ID INT);
INSERT INTO A VALUES(1);
COMMIT;
ora19cf1> STARTUP
Warning: PDB altered with errors.
ora19cf1> create tablespace users datafile '+DATADG' size 1m autoextend on maxsize 30g;
Tablespace created.
ora19cf1> shutdown immediate;
Pluggable Database closed.
ora19cf1> startup
Pluggable Database opened.
     FILE# STATUS  CREATE_BYTES NAME
---------- ------- ------------ ------------------------------------------------------------------------------------------
       464 SYSTEM     272629760 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/system.332.1132650825
       465 ONLINE     173015040 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/sysaux.361.1132650825
       466 ONLINE     225443840 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/undotbs1.279.1132650825
       467 ONLINE     225443840 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/undo_2.385.1132650825
       468 ONLINE     104857600 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/pdb_test1.324.1132650985
       469 ONLINE       1048576 +DATADG/ORA19CF/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/users.263.1132651063


SYS@ora19cfldg1> ALTER SESSION SET CONTAINER=PDB_TEST2;
SELECT FILE#,STATUS,CREATE_BYTES,NAME FROM V$DATAFILE;
     FILE# STATUS  CREATE_BYTES NAME
---------- ------- ------------ -----------------------------------------------------------------
       464 SYSOFF     272629760 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00464
       465 RECOVER    173015040 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00465
       466 RECOVER    225443840 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00466
       467 RECOVER    225443840 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00467
       468 RECOVER    104857600 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00468
       469 RECOVER      1048576 /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00469
SYS@ora19cfldg1> host du -sm /u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00464
du: cannot access ‘/u01/app/oracle/product/19.10.0/db_1/dbs/UNNAMED00464’: No such file or directory

--pdb 恢复
rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST2 to new;
restore pluggable database PDB_TEST2 from service ora19cfldg;
switch datafile all;
}
exit
EOF

RMAN-03002: failure of restore command at 03/28/2023 09:20:45
ORA-19846: cannot read header of datafile 464 from remote site
$ ps -ef|grep mrp
oracle    22881      1  0 Mar27 ?        00:00:05 ora_mrp0_ora19cfldg1
dgmgrl /
edit database ora19cfldg set state=apply-off;
$ ps -ef|grep mrp


指定的service名称不对指向DG了,本次测试的场景, 场景12的时候主库是ora19cfldg,场景3的时候ora19cfldg其他人测试做过dg switch 切换,ora19cfldg变成dg
rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST2 to new;
restore pluggable database PDB_TEST2 from service ora19cf;
switch datafile all;
}
exit
EOF


select con_id,dbid,name,open_mode,recovery_status,open_time,total_size,local_undo,creation_time from v$pdbs where name='PDB_TEST2';
    CON_ID       DBID NAME         OPEN_MODE  RECOVERY OPEN_TIME                      TOTAL_SIZE LOCAL_UNDO CREATION_TIME
---------- ---------- ------------ ---------- -------- ------------------------------ ---------- ---------- -------------------
         6 1026458684 PDB_TEST2    MOUNTED    DISABLED                                1012924416          1 2023-03-28 09:13:45
   
alter pluggable database PDB_TEST2 enable recovery;
ORA-01156: recovery or flashback in progress may need access to files

alter session set container=pdb_test2;
alter pluggable database PDB_TEST2 enable recovery;

select process,status from Gv$managed_standby where process='MRP0';
PROCESS   STATUS
--------- ------------
MRP0      APPLYING_LOG
DGMGRL> edit database ora19cfldg set state=apply-off;
alter session set container=pdb_test2;
alter pluggable database PDB_TEST2 enable recovery;

SYS@ora19cfldg1> alter pluggable database PDB_TEST2 enable recovery
*
ERROR at line 1:
ORA-01113: file 469 needs media recovery
ORA-01110: data file 469:
'+DATADG/ORA19CFLDG/F7EC922861BDE9EDE0534D040E374F15/DATAFILE/users.273.11326519

SYS@ora19cfldg1> startup force mount;
select process,status from Gv$managed_standby where process='MRP0';
alter session set container=pdb_test2;
alter pluggable database PDB_TEST2 enable recovery;

conn / as sysdba
alter database open;
DGMGRL> edit database ora19cfldg set state=apply-on;
alter database recover managed standby database disconnect from session;
ORA-01153: an incompatible media recovery is active

复制

小结:
启用pdb recovery需要数据库实例dg 在mount阶段执行,并且mrp进程不能启动;

alter pluggable database pdb_test2 close instances=all;
drop pluggable database pdb_test2 including datafiles;

复制

场景四、创建PDB的时候语法指定NONE,并且新建表空间,新增数据文件

--pri
create pluggable database pdb_test3 admin user pdbtest identified by "pdbtest" default tablespace pdb_test1 file_name_convert=('+DATADG','+DATADG') STANDBYS=NONE;
ALTER SESSION SET CONTAINER=PDB_TEST3;
startup
create tablespace users datafile '+DATADG' size 1m autoextend on maxsize 30g;
alter pluggable database pdb_test3 close instances=all;
alter pluggable database pdb_test3 open instances=all;
create tablespace test002 datafile '+DATADG' size 1m autoextend on maxsize 30g;
alter tablespace test002 add datafile '+DATADG' size 1m autoextend on maxsize 30g;
STARTUP
CREATE TABLE A(ID INT) tablespace test002;
INSERT INTO A VALUES(1);
COMMIT;
     FILE# STATUS  CREATE_BYTES NAME
---------- ------- ------------ ------------------------------------------------------------------------------------------
       470 SYSTEM     272629760 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/system.258.1132653427
       471 ONLINE     173015040 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/sysaux.332.1132653427
       472 ONLINE     225443840 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/undotbs1.361.1132653427
       473 ONLINE     225443840 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/undo_2.279.1132653427
       474 ONLINE     104857600 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/pdb_test1.324.1132653433
       475 ONLINE       1048576 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/users.263.1132653489
       476 ONLINE       1048576 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/test002.382.1132653537
       477 ONLINE       1048576 +DATADG/ORA19CF/F7ED228A6256CB33E0534D040E37512B/DATAFILE/test002.398.1132653539


复制

–adg

ALTER SESSION SET CONTAINER=PDB_TEST3;
SELECT FILE#,STATUS,CREATE_BYTES,NAME FROM V$DATAFILE;


复制

如何更新ADG的控制文件信息呢

RMAN> restore standby controlfile from service ora19cf;
RMAN-06496: must use the TO clause when the database is mounted or open
SYS@ora19cfldg1> startup force mount;
$ srvctl stop database -d ora19cfldg
SYS@ora19cfldg1> startup nomount;
--需要启动nomount阶段
RMAN> restore standby controlfile from service ora19cf;
RMAN> sql 'alter database mount';
RMAN> catalog start with '+DATADG/ORA19CFLDG';
switch database to copy;
RMAN-06571: datafile 470 does not have recoverable copy

--
Errors in file /diaglog/diag/rdbms/ora19cfldg/ora19cfldg1/trace/ora19cfldg1_lgwr_122850.trc:
ORA-00313: open failed for members of log group 22 of thread 1
ORA-00312: online log 22 thread 1: '+FRADG/ORA19CF/ONLINELOG/group_22.2345.1100543975'
ORA-17503: ksfdopn:2 Failed to open file +FRADG/ORA19CF/ONLINELOG/group_22.2345.1100543975
ORA-15012: ASM file '+FRADG/ORA19CF/ONLINELOG/group_22.2345.1100543975' does not exist
--
alter database CLEAR logfile group 12 ;
alter database drop standby logfile group 12 ;
alter database add standby logfile thread 1 group 12 ('+FRADG','+FRADG') size 512M ;

复制

如何恢复单个数据文件呢

rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST3 to '+datadg/ORA19CFLDG/pdb_test3_data/%f';
restore datafile 470,471,472,473,474,475,476,477 from service ora19cf;
switch datafile all;
}
exit
EOF

channel c1: restoring datafile 00470 to +datadg/ORA19CFLDG/pdb_test3_data/470
channel c2: restoring datafile 00471 to +datadg/ORA19CFLDG/pdb_test3_data/471

rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST3 to '+datadg/ORA19CFLDG/pdb_test3_data/%U';
restore datafile 470,471 from service ora19cf;
switch datafile all;
}
exit
EOF

channel c2: restoring datafile 00471 to +datadg/ORA19CFLDG/pdb_test3_data/data_D-ORA19CF_TS-SYSAUX_FNO-471
channel c2: restoring datafile 00471 to +datadg/ORA19CFLDG/pdb_test3_data/data_D-ORA19CF_TS-SYSAUX_FNO-471

srvctl stop database -d ora19cfldg
SYS@ora19cfldg1> startup nomount;
alter database mount;
RMAN> catalog start with '+DATADG/ORA19CFLDG';
switch database to copy;


rman target /<<EOF
run{
 allocate channel c1 device type disk;
 allocate channel c2 device type disk;
set newname for pluggable database PDB_TEST3 to '+datadg/ORA19CFLDG/pdb_test3_data/%f';
restore datafile 470,471 from service ora19cf;
switch datafile all;
}
exit
EOF

alter pluggable database pdb_test3 close instances=all;
drop pluggable database pdb_test3 including datafiles;

复制

结论及建议

1.创建pdb的时候如果指定了参数standbys=none,则adg并不会同步新建的pdb的数据文件到adg备库，只会同步控制文件中数据文件的条目信息,后续adg pdb的数据文件需要单独重新恢复；
2.PDB的恢复模式从禁用模式调整为启用,需要cdb层面的数据库实例是mount阶段,Mrp恢复进程处于关闭状态才能进行调整操作;
3.ADG 控制文件从主库获取之后，数据文件、日志文件的目录大概率是需要重新调整的，建议慎重操作！

墨力计划
最后修改时间：2023-03-28 15:47:45
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

杨卓
暂无图片
2年前
评论
暂无图片 0
补充+浪峰哥的恢复单个文件的Mos 注意看我恢复测试单个数据文件的pdb文件有使用set newname可能破坏原有的数据文件目录,我的测试是想换个目录看看咋整，但是如果是生产环境建议使用back ,restore即可无需使用set newname该路径！！！ 注意使用根据情况需要调整！
2年前
暂无图片 点赞
评论
筱悦星辰
暂无图片
2年前
评论
暂无图片 1
与其总是让自己深陷在计较的纷扰与纠结中，不如把心放宽一些、把眼光放长远一些。心小了，所有的小事就大了；而心大了，所有的大事就小了。
2年前
暂无图片 1
评论

相关阅读

【MySQL 30周年庆】MySQL 8.0 OCP考试限时免费！教你免费领考券
墨天轮小教习
2951次阅读
2025-04-25 18:53:11
2025年4月国产数据库大事记：4个千万级中标项目诞生！2024年达梦净利3.6亿、金仓净利8006.6万……
墨天轮编辑部
1914次阅读
2025-04-30 17:39:54
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
1642次阅读
2025-04-21 16:58:09
数据库国产化替代深化：DBA的机遇与挑战
代晓磊
1216次阅读
2025-04-27 16:53:22
2025 DBA 薪资观察：做 DBA 还香吗？
墨天轮编辑部
1052次阅读
2025-04-24 15:53:21
【专家有话说第六期】数据库考证到底有用么？国产时代DBA如何构建真实竞争力
墨天轮编辑部
881次阅读
2025-05-06 17:50:06
MySQL 30 周年庆！MySQL 8.4 认证免费考！这次是认真的。。。
严少安
851次阅读
2025-04-25 15:30:58
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
691次阅读
2025-04-18 14:18:38
2025年4月国产数据库中标情况一览：4个千万元级项目，GaussDB与OceanBase大放异彩！
通讯员
690次阅读
2025-04-30 15:24:06
探索SQL的无尽可能 | 墨天轮SQL挑战赛第三期，五一邀你挑战！
墨天轮编辑部
683次阅读
2025-04-29 14:20:05
目录
  • 文档概述
  • 学习测试
    • 学习资料
    • 验证测试环境主备同步
    • 场景一、主库新建一个pdb,ADG是否自动同步主库的数据
    • 场景二、备库主动断开某个pdb的数据同步,随后手工启用这个同步
      • 1）禁用某个pdb1的adg的复制
      • 2）主库对这个pdb1新建数据文件,pdb2修改数据,作为对比参照;
      • 3）观察adg pdb1 的控制文件中是否有这个新增的数据文件;
      • 4）ADG PDB的恢复
    • 场景三、创建PDB的时候语法指定NONE
    • 场景四、创建PDB的时候语法指定NONE,并且新建表空间,新增数据文件
    • 如何更新ADG的控制文件信息呢
    • 如何恢复单个数据文件呢
  • 结论及建议