主库上克隆一个可读写的pdb,从库恢复过程
环境:
Os:Centos 7
db:19.3.0.0
主从:dataguard 1主1从
1.主库上克隆一个可读写的pdb
SQL>create pluggable database pdb01 from pdb
file_name_convert = ('/u01/app/oracle/oradata/SLNNGK/pdb/', '/u01/app/oracle/oradata/SLNNGK/pdb01/')
path_prefix= '/u01/app/oracle/oradata/SLNNGK/pdb';
打开新添加的pdb
SQL>alter pluggable database pdb01 open;
查看数据文件
SQL>alter session set container=pdb;
SQL>Set linesize 1000;
SQL>Column file_name format a64;
SQL>Column file_id format 99;
SQL>Column tablespace_name format a10;
SQL>Select file_name,file_id,tablespace_name From dba_data_files;
SQL> Select file_name,file_id,tablespace_name From dba_data_files;
FILE_NAME FILE_ID TABLESPACE
---------------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/SLNNGK/pdb01/system01.dbf 14 SYSTEM
/u01/app/oracle/oradata/SLNNGK/pdb01/sysaux01.dbf 15 SYSAUX
/u01/app/oracle/oradata/SLNNGK/pdb01/undotbs01.dbf 16 UNDOTBS1
/u01/app/oracle/oradata/SLNNGK/pdb01/users01.dbf 17 USERS
/u01/app/oracle/oradata/SLNNGK/pdb01/tps_pdb01.dbf 18 TPS_PDB
2.从库打开新增的pdb
SQL> alter session set container=pdb01;
SQL> alter pluggable database pdb01 open;
从日志文件看报如下的错误:
ORA-01111: name for data file 14 is unknown - rename to correct file
查看数据字典发现没有按照设想的转换过来
SQL> select file#,name from v$datafile;
FILE# NAME
------------------------------------------------------------------
14 /u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00014
15 /u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00015
16 /u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00016
17 /u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00017
18 /u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00018
3.从库创建新的pdb目录
su - oracle
mkdir -p /u01/app/oracle/oradata/slavea/pdb01
4.从库手工修改数据文件
[oracle@slavea dbs]$ sqlplus /nolog
SQL> connect / as sysdba
Connected.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter session set container=pdb01;
Session altered.
SQL>alter system set standby_file_management=manual;
SQL>alter database create datafile '/u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00014' as '/u01/app/oracle/oradata/slavea/pdb01/system01.dbf';
SQL>alter database create datafile '/u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00015' as '/u01/app/oracle/oradata/slavea/pdb01/sysaux01.dbf';
SQL>alter database create datafile '/u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00016' as '/u01/app/oracle/oradata/slavea/pdb01/undotbs01.dbf';
SQL>alter database create datafile '/u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00017' as '/u01/app/oracle/oradata/slavea/pdb01/users01.dbf';
SQL>alter database create datafile '/u01/app/oracle/product/19.3.0.0/db_1/dbs/UNNAMED00018' as '/u01/app/oracle/oradata/slavea/pdb01/tps_pdb01.dbf';
SQL>alter session set container=cdb$root;
SQL>alter system set standby_file_management=auto;
5.重启动从库
接着上面的步骤尝试应用日志
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
提示报错误,下面重启动数据库
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 3992977296 bytes
Fixed Size 9142160 bytes
Variable Size 771751936 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 PDB01 MOUNTED
尝试打开新添加的pdb
SQL> alter pluggable database all open;
alter pluggable database all open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 14 is offline
发现报错误
6.从库恢复处理
SQL>alter session set container=cdb$root;
SQL>alter system set standby_file_management=auto;
SQL>alter database recover managed standby database cancel;
SQL>alter session set container=pdb01;
SQL>alter pluggable database disable recovery; ##禁用自恢复
12C之后的新特新通过网络服务进行恢复,tnsslnngk是tns服务名
RMAN> run{
set newname for datafile 14 to '/u01/app/oracle/oradata/slavea/pdb01/system01.dbf';
set newname for datafile 15 to '/u01/app/oracle/oradata/slavea/pdb01/sysaux01.dbf';
set newname for datafile 16 to '/u01/app/oracle/oradata/slavea/pdb01/undotbs01.dbf';
set newname for datafile 17 to '/u01/app/oracle/oradata/slavea/pdb01/users01.dbf';
set newname for datafile 18 to '/u01/app/oracle/oradata/slavea/pdb01/tps_pdb01.dbf ';
restore pluggable database pdb01 from service tnsslnngk ;
switch datafile all;
}
重启数据库
SQL> alter session set container=cdb$root;
SQL>shutdown immediate;
SQL> startup mount;
启用pdb自恢复
SQL> alter session set container=pdb01;
SQL> alter pluggable database enable recovery;
重新应用日志
SQL> alter session set container=cdb$root;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
打开数据库
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB MOUNTED
4 PDB01 MOUNTED
SQL> alter pluggable database all open;
SQL> alter database recover managed standby database using current logfile disconnect from session;
经过分析发现,dataguard 环境下,只有source pdb 是 READ ONLY,那么在primary 上执行创建pdb时,在 standby 上就能顺利创建.
