主库上克隆一个可读写的pdb,从库恢复过程

标签：主库 database app alter SQL oracle 从库 pdb u01

环境:
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 上就能顺利创建.

 

标签：主库,database,app,alter,SQL,oracle,从库,pdb,u01
From： https://www.cnblogs.com/hxlasky/p/17099267.html