526互联

使用了enabled_PDBs_on_standby参数后,主库新增pdb后从库的操作

发布时间 2023-04-18 14:20:15作者: slnngk

环境:
OS:Centos 7
DB:12.2.0.1

拓扑:1主1从的 dataguard

 

1.从库查看参数enabled_PDBs_on_standby

SQL> show parameters enabled_PDBs_on_standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby              string      PDB1, PDB2

 

我这里只让同步pdb1和pdb2

 

2.在主库上创建一个pdb4

create pluggable database pdb4 admin user hxl identified by oracle file_name_convert = ('/u01/app/oracle/oradata/slnngk/pdbseed', '/u01/app/oracle/oradata/slnngk/pdb4');

 

3.查看从库的pdb情况

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO
         4 PDB2                           READ ONLY  NO
         7 PDB4                           MOUNTED

尝试打开
SQL> alter pluggable database pdb4 open;
alter pluggable database pdb4 open
*
ERROR at line 1:
ORA-01111: name for data file 26 is unknown - rename to correct file

 

4.查看从库的数据文件

set linesize 200;
column con_id format 99;
column name format a64;
column file# format 99;
SQL> select con_id,file#,name from v$datafile where con_id=7;

CON_ID FILE# NAME
------ ----- ----------------------------------------------------------------
     7    26 /u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00026
     7    27 /u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00027
     7    28 /u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00028

发现主库上创建的pdb对应的数据文件传到备库后就以unname的形式表现

在主库上查询这些文件对应的数据文件名

SQL> select con_id,file#,name from v$datafile where con_id=7;

CON_ID FILE# NAME
------ ----- ----------------------------------------------------------------
     7    26 /u01/app/oracle/oradata/slnngk/pdb4/system01.dbf
     7    27 /u01/app/oracle/oradata/slnngk/pdb4/sysaux01.dbf
     7    28 /u01/app/oracle/oradata/slnngk/pdb4/undotbs01.dbf

 

5.在从库上改名

创建目录
su - oracle
mkdir -p /u01/app/oracle/oradata/slavea/pdb4

alter session set container=cdb$root;
alter system set standby_file_management=manual;
alter database recover managed standby database cancel;
alter session set container=pdb4;
alter database create datafile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00026' as '/u01/app/oracle/oradata/slavea/pdb4/system01.dbf';
alter database create datafile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00027' as '/u01/app/oracle/oradata/slavea/pdb4/sysaux01.dbf';
alter database create datafile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/UNNAMED00028' as '/u01/app/oracle/oradata/slavea/pdb4/undotbs01.dbf';
alter session set container=cdb$root;
alter system set standby_file_management=auto;
alter database recover managed standby database using current logfile disconnect from session;

 

6.从库尝试打开pdb4

SQL> alter pluggable database pdb4 open;
alter pluggable database pdb4 open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 26 is offline

需要进行恢复

 

7.恢复pdb4
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=pdb4;
SQL>alter pluggable database disable recovery;
查看状态
SQL>select name, recovery_status from v$pdbs;


##tnsslnngk 是连接到主库的tns--doing
RMAN>
run{
restore pluggable database pdb4 from service tnsslnngk;
}

 

8.从新应用日志

SQL> alter session set container=cdb$root;
SQL> shutdown immediate;

SQL> startup mount;
SQL> alter session set container=pdb4;
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;

 

9.打开pdb

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
alter pluggable database pdb4 open;