pdb snapshot copy 需要硬件支持源pdb 必须read only

jnrjian

已于 2024-09-08 02:25:49 修改

阅读量910

点赞数 21

文章标签：数据库 oracle dba

于 2024-09-08 02:19:06 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/142006486

版权

SQL> create pluggable database PDBCLONE1 from PDB snapshot copy create_file_dest='+data1';
create pluggable database PDBCLONE1 from PDB snapshot copy create_file_dest='+data1'
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135

SQL> create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy;
create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135

SQL> create pluggable database PDBCLONE1 from PDB create_file_dest='+data1';

Pluggable database created.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE YES
9 PDBCLONE1 MOUNTED
SQL> alter pluggable database PDBCLONE1 open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE YES
9 PDBCLONE1 READ WRITE NO
SQL> alter pluggable database PDBCLONE1 close abort;

Pluggable database altered.

SQL> drop pluggable database PDBCLONE1 including datafiles;

Pluggable database dropped.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE YES
SQL> create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy;
create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135
ORA-17517: Database cloning using storage snapshot failed on file
8:+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135

SQL>

SYMPTOMS

On 12c database, create pdb from snapshot copy throws ora-65169/ora-17525 errors :

create pluggable database pclone1 from pclone snapshot copy file_name_convert=('/oradata/testclone/datafile/o1_mf_system_c5bocwh0_.dbf','/oradata/testclone2/system01.dbf','/oradata/testclone/datafile/o1_mf_sysaux_c5bocwh3_.dbf','/oradata/testclone2/sysaux01.dbf','/oradata/testclone/datafile/o1_mf_users_c5bocwh5_.dbf','/oradata/testclone2/users01.dbf','/oradata/testclone/datafile/o1_mf_temp_c5bocwh5_.dbf','/oradata/testclone2/temp01.dbf')
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
/oradata/testclone/datafile/o1_mf_syst
em_c5bocwh0_.dbf
ORA-17525: Database clone using storage snapshot not supported on file
/oradata/testclone/datafile/o1_mf_syst
em_c5bocwh0_.dbf

CAUSE

1. The storage does not support file snapshots.
2. clonedb was not set .

SOLUTION

The ORA-17525 error is thrown because the storage does not support file snapshots. Use a compatible storage.

Or)

Set clonedb=TRUE

SQL> alter system set clonedb=true scope=spfile;

Then restart database

SQL> show parameters clonedb

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
clonedb_dir string
SQL> alter system set clonedb=true;
alter system set clonedb=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set clonedb=true scope =spfile;

System altered.

SQL> startup force
;
ORACLE instance started.

Total System Global Area 2382361320 bytes
Fixed Size 9167592 bytes
Variable Size 956301312 bytes
Database Buffers 1409286144 bytes
Redo Buffers 7606272 bytes
Database mounted.
Database opened.
SQL> 1* alter system set clonedb=true scope =spfile
SQL>
SQL>
SQL>
SQL> create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy;
create pluggable database PDBCLONE1 from PDB create_file_dest='+data1' snapshot copy
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

SQL> alter pluggable database pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb open read only;

Pluggable database altered.

SQL> show parameters clonedb

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE
clonedb_dir string
SQL>

GOAL

A PDB cannot be opened in read-write mode if we have previously created snapshot copy clones using that PDB even after the clones are dropped. This document explains how to ensure that all clones based on that PDB have been dropped and how to make the PDB read-write again.

When a snapshot copy PDB is created from a source PDB using create snapshot copy pdb, the permissions of datafiles of the source PDB are changed to read only mode.

Permissions before create snapshot copy pdb

-rw-r----- 1 crsusr dba 5251072 Jun 21 02:22
<GUID>/datafile/o1_mf_users_flpt5v4k_.dbf

Permissions after create snapshot copy pdb

-r--r----- 1 crsusr dba 5251072 Jun 21 02:22
<GUID>/datafile/o1_mf_users_flpt5v4k_.dbf

These datafile permissions are set to prevent accidental modification of the source PDB datafiles. Any modification of these datafiles will prevent snapshot copy PDBs depending on these datafiles from working correctly and consistently.
If the user tries to open such a source PDB in read-write mode, it triggers one of the following errors:

ORA-00604: error occurred at recursive SQL level 1
ORA-01114: IO error writing block to file 16 (block # 1)
ORA-01110: data file 16:
'<ORACLE_BASE>/oradata/CDB01/<GUID>/datafile/o1_mf_users_flpt5v4k_.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3

ORA-17528: A read-only file or a file opened read-only cannot be written to:
<ORACLE_BASE>/oradata/CDB01/<GUID>/datafile/o1_mf_users_flpt5v4k_.dbf

ORA-65009: cannot open pluggable database in read-write mode

Even if the user removes all the dependent snapshot PDBs the source cannot be opened read only.

SOLUTION

Searching for the dependent PDBs is two parts –

1. Ensure there are no snapshot copy PDBs depending on the source PDB datafiles in the current CDB

a) Open all the pdbs in read-only mode.

SQL> alter pluggable database all open read only;

b) Note the con_id for the source PDB.

SQL> select CON_ID from v$pdbs where NAME like '<CDB1_PDB1>';

c) Check if there are any PDBs that depend on CDB1_PDB1.

SQL> select NAME from v$pdbs where SNAPSHOT_PARENT_CON_ID=<CON_ID>;

If there are no rows returned for the last query, that means there are no PDBs in the current CDB that are dependent on the source PDB.

2. Ensure there are no snapshot copy PDBs depending on the source PDB datafiles in the other CDBs

a) Get the GUID for the source PDB. We will compare this in other CDBs to check if any PDB depends on the source PDB.

SQL> select GUID from v$PDBs where NAME like '<CDB1_PDB1>';

b) Compare the GUID from step ‘a’ with column ‘CLONED_FROM_PDB_GUID’ column of ‘DBA_PDB_HISTORY’ table in all the CDBs where you think a snapshot might exist.

SQL> select PDB_NAME from dba_pdb_history where CLONED_FROM_PDB_GUID like '<CDB1_PDB1_GUID>';

If there are no rows returned for the above query in any of the CDBs that means no PDB in the CDBs depend on the source PDB. If there are, the present status for those dependent PDBs needs to be checked before deciding about changing source PDB permission.

Changing the permission of the source PDB

SQL> exec dbms_dnfs.restore_datafile_permissions('<cdb1_pdb1>');

This will revert back the permission of the source PDB (in this case <CDB1_PDB1>) and it can then be opened read write.

Opening the Source PDB

SQL> alter pluggable database '<cdb1_pdb1>' open;

--------------

GOAL

Need some good documentation on how to do the snapshot copy. The basic documentation at https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/cloning-a-pdb.html#GUID-0284ED92-A3F4-4D10-A8C7-B47FABA5A774 does not seem be complete or at least doesn't seem to explain the prerequisites well enough

SOLUTION

We can not use snapshot copy on ASM without use of ACFS. For example, if the source database has to create the file destination of: +DATA datafiles then a path like the following needs to be used:

+DATA/..GUID /datafile.dbf

If an attempt is made to do a snapshot copy of this PDB, it will not work, no matter if CLONEDB=TRUE or CLONEDB=FALSE, create dest for snapshot PDB, and/or permissions in ASM for the datafiles.

When the SOURCE on an ACFS filesystem is used, we are able to do a snapshot copy of the PDB.

The following is from the document

Cloning a PDB or Non-CDB

The behavior of the CREATE PLUGGABLE DATABASE ... FROM ... SNAPSHOT COPY command depends on the following rules:

If the file system supports storage-managed snapshots, then the snapshot copy PDB is based on a storage-level copy of the underlying file system. The snapshot copy PDB contains sparse files. The copy-on-write technology means that only modified blocks require additional storage on disk.

If the file system does not support storage snapshots, then the algorithm is as follows:

If the storage system uses Oracle Exadata sparse disk groups, then Oracle Database creates a snapshot copy PDB. However, the source PDB must remain read/only for the lifetime of the snapshot copy PDB.

If the storage system does not use Oracle Exadata sparse disk groups, then the behavior is as follows:

If CLONEDB=true, then the underlying file system for the source PDB files can be any local file system, network file system (NFS), or a clustered file system such as Oracle ACFS. If using a network file system, Direct NFS should be enabled for the CDB. The file system should support sparse files. Most UNIX systems meet these requirements.

When CLONEDB=true, the open mode of the source PDB has the following effects:

If the source PDB is open in read-only mode, then Oracle Database creates a snapshot copy PDB using copy-on-write technology. The snapshot copy PDB contains sparse files, not full copies.

If the source PDB is not open in read-write mode, then Oracle Database issues an error.

If CLONEDB=false, then Oracle Database issues an error.

CONCLUSION
=============
If CLONEDB=true and the source DB is in read only, then the create pluggable db snapshot copy should succeed on ACFS and any file system supports "sparse files" most unix file systems support that .