DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Multitenant Pills: Partial PDB cloning (and cleanup)

Posted on May 22, 2020 by Ludovico

When consolidating to multitenant, there are several consolidation patterns.

Big, complex databases usually have special requirements for which it might be a good choice to go to single-tenant (a single PDB in one CDB)
Small, relatively easy databases are the best candidate for consolidation to multitenant
Schema consolidated databases require special attention, but in general there are several advantages to convert individual schemas (or group of schemas) to individual PDBs

For the latter, there are some techniques to convert a schema in a PDB.

export/import (obviously), with eventually Golden Gate to do it online
Transportable tablespaces (if the schemas follow strict 1-to-1 tablespace separation
partial PDB cloning

We will focus on the last one for this blog post.

Situation

Here we have a PDB with some schemas, each of them has a dedicated tablespace, but accidentally, two of them have also some objects on a common tablespace.

This happens frequently when all the users have quota on the default database tablespace and they do not have necessarily a personal default tablespace.

connect / as sysdba
CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdbadmin IDENTIFIED BY manager;
ALTER PLUGGABLE DATABASE PDB1OPEN;
ALTER SESSION SET CONTAINER=PDB1;

CREATE TABLESPACE ABC DATAFILE SIZE 50M;
CREATE TABLESPACE DEF DATAFILE SIZE 50M;
CREATE TABLESPACE GHI DATAFILE SIZE 50M;

CREATE USER ABC IDENTIFIED BY ABC DEFAULT TABLESPACE ABC QUOTA UNLIMITED ON ABC;
CREATE USER DEF IDENTIFIED BY DEF DEFAULT TABLESPACE DEF QUOTA UNLIMITED ON DEF;
CREATE USER GHI IDENTIFIED BY GHI DEFAULT TABLESPACE GHI QUOTA UNLIMITED ON GHI;

ALTER USER ABC QUOTA UNLIMITED ON DATA;
ALTER USER DEF QUOTA UNLIMITED ON DATA;
ALTER USER GHI QUOTA UNLIMITED ON DATA;

GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE TO ABC, DEF, GHI;


CONNECT abc/abc@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));
INSERT INTO T1 VALUES ('TOTO');
COMMIT;

CREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;
INSERT INTO T2 VALUES ('TOTO');
COMMIT;


CONNECT def/def@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));
INSERT INTO T1 VALUES ('TOTO');
COMMIT;

CREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;
INSERT INTO T2 VALUES ('TOTO');
COMMIT;


CONNECT ghi/ghi@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));
INSERT INTO T1 VALUES ('TOTO');
COMMIT;

connect / as sysdba

CREATE PLUGGABLE DATABASE PDB1 ADMIN USER pdbadmin IDENTIFIED BY manager;

ALTER PLUGGABLE DATABASE PDB1OPEN;

ALTER SESSION SET CONTAINER=PDB1;

CREATE TABLESPACE ABC DATAFILE SIZE 50M;

CREATE TABLESPACE DEF DATAFILE SIZE 50M;

CREATE TABLESPACE GHI DATAFILE SIZE 50M;

CREATE USER ABC IDENTIFIED BY ABC DEFAULT TABLESPACE ABC QUOTA UNLIMITED ON ABC;

CREATE USER DEF IDENTIFIED BY DEF DEFAULT TABLESPACE DEF QUOTA UNLIMITED ON DEF;

CREATE USER GHI IDENTIFIED BY GHI DEFAULT TABLESPACE GHI QUOTA UNLIMITED ON GHI;

ALTER USER ABC QUOTA UNLIMITED ON DATA;

ALTER USER DEF QUOTA UNLIMITED ON DATA;

ALTER USER GHI QUOTA UNLIMITED ON DATA;

GRANT CREATE SESSION, ALTER SESSION, CREATE TABLE TO ABC, DEF, GHI;

CONNECT abc/abc@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));

INSERT INTO T1 VALUES ('TOTO');

COMMIT;

CREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;

INSERT INTO T2 VALUES ('TOTO');

COMMIT;

CONNECT def/def@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));

INSERT INTO T1 VALUES ('TOTO');

COMMIT;

CREATE TABLE T2 (A VARCHAR2(50)) TABLESPACE DATA;

INSERT INTO T2 VALUES ('TOTO');

COMMIT;

CONNECT ghi/ghi@srcsrv:1521/pdb1

CREATE TABLE T1 (A VARCHAR2(50));

INSERT INTO T1 VALUES ('TOTO');

COMMIT;

This is the typical situation where transportable tablespaces become hard to achieve without some upfront segment movement, as tablespaces are not self-contained.

Thankfully, Oracle Multitenant allows us to clone a PDB from a remote one and specify only a subset of tablespaces.

CREATE PLUGGABLE DATABASE ABC FROM PDB1.ORCL1_PDB1_tempclone 
  USER_TABLESPACES=('ABC','DATA');

1 2	CREATE PLUGGABLE DATABASE ABC FROM PDB1.ORCL1_PDB1_tempclone USER_TABLESPACES=('ABC','DATA');

Here is a full example script with some checks and fancy parameters:

--------------------------
-- Script: clone_pdb.sql
-- Purpose: (partial) clone a PDB from remote PDB
-- Author: Ludovico Caldara (Trivadis)
--
-- Execute as: DBA in CDB$ROOT
--
-- Requirements: The remote user for the DBlink must have SELECT privileges on a few fixed views:
-- --
-- create user c##remote_user identified by manager container=all;
-- grant create session, sysoper, create pluggable database to c##remote_user container=all;
-- -- granting extra privileges to get file names and total size
-- grant select on sys.v_$pdbs to c##remote_user container=all;
-- grant select on sys.v_$datafile to c##remote_user container=all;
-- grant select on sys.v_$database to c##remote_user container=all;
-- alter user c##remote_user set container_data=all for v_$pdbs container=current;
-- alter user c##remote_user set container_data=all for v_$datafile container=current;
-- alter user c##remote_user set container_data=all for v_$database container=current;
--
--
--
-- Script variables:
define SourceCDB = 'ORCL1'
define SourcePDB = 'PDB1'
define SourceUser = 'C##REMOTE_USER'
define SourcePassword = 'manager'
define SourceAlias = 'srcsrv:1521/PDB1'
define TargetCDB = 'ORCL2'
define TargetPDB = 'ABC'
define UserTBSPClause = 'USER_TABLESPACES=(''ABC'',''DATA'')'
-- in case of non-OMF
define FileNameConvertClause = 'FILE_NAME_CONVERT=(''/&SourceCDB/&SourcePDB/'',''/&TargetCDB/&TargetPDB/'')'

SET ECHO ON SERVEROUTPUT ON

-- Temporary database link name. We try to drop it if it exists but nevermind if it gives error
WHENEVER SQLERROR CONTINUE
DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;

-- The other tasks shouls succeed, otherwise exit
WHENEVER SQLERROR EXIT 1
CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';

-- in this PL/SQL block we make sure that we do not copy if
-- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY
DECLARE
        l_log_mode sys.v_$database.log_mode%TYPE;
        l_local_undo database_properties.property_value%TYPE;
        l_open_mode sys.v_$pdbs.open_mode%TYPE;

BEGIN
        BEGIN
                SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';
        EXCEPTION
                WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');
        END;

        -- Check if NOARCHIVELOG and not READ ONLY
        SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;
        IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN
                raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');
        END IF;

        -- Check if not LOCAL_UNDO_ENABLED  and not READ ONLY
        SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE  property_name = 'LOCAL_UNDO_ENABLED';
        IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN
                raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');
        END IF;
END;
/

-- let's add some intelligence... Parallel degree depending on PDB size
COLUMN parallel new_value parallel
SELECT CASE
        WHEN ROUND(total_size/POWER(1024,3)) <= 1 THEN 1
        WHEN ROUND(total_size/POWER(1024,3)) >= 5 THEN 4
        ELSE 2 END AS parallel
FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';

-- Real clone PDB
CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause;

ALTER PLUGGABLE DATABASE &TargetPDB OPEN;
ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;
-- drop temporary db link: leaving it there exposes some risks
DROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone

SET ECHO OFF SERVEROUTPUT OFF

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

-- Script: clone_pdb.sql

-- Purpose: (partial) clone a PDB from remote PDB

-- Author: Ludovico Caldara (Trivadis)

-- Execute as: DBA in CDB$ROOT

-- Requirements: The remote user for the DBlink must have SELECT privileges on a few fixed views:

-- --

-- create user c##remote_user identified by manager container=all;

-- grant create session, sysoper, create pluggable database to c##remote_user container=all;

-- -- granting extra privileges to get file names and total size

-- grant select on sys.v_$pdbs to c##remote_user container=all;

-- grant select on sys.v_$datafile to c##remote_user container=all;

-- grant select on sys.v_$database to c##remote_user container=all;

-- alter user c##remote_user set container_data=all for v_$pdbs container=current;

-- alter user c##remote_user set container_data=all for v_$datafile container=current;

-- alter user c##remote_user set container_data=all for v_$database container=current;

-- Script variables:

define SourceCDB = 'ORCL1'

define SourcePDB = 'PDB1'

define SourceUser = 'C##REMOTE_USER'

define SourcePassword = 'manager'

define SourceAlias = 'srcsrv:1521/PDB1'

define TargetCDB = 'ORCL2'

define TargetPDB = 'ABC'

define UserTBSPClause = 'USER_TABLESPACES=(''ABC'',''DATA'')'

-- in case of non-OMF

define FileNameConvertClause = 'FILE_NAME_CONVERT=(''/&SourceCDB/&SourcePDB/'',''/&TargetCDB/&TargetPDB/'')'

SET ECHO ON SERVEROUTPUT ON

-- Temporary database link name. We try to drop it if it exists but nevermind if it gives error

WHENEVER SQLERROR CONTINUE

DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;

-- The other tasks shouls succeed, otherwise exit

WHENEVER SQLERROR EXIT 1

CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';

-- in this PL/SQL block we make sure that we do not copy if

-- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY

DECLARE

l_log_mode sys.v_$database.log_mode%TYPE;

l_local_undo database_properties.property_value%TYPE;

l_open_mode sys.v_$pdbs.open_mode%TYPE;

BEGIN

SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';

EXCEPTION

WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');

END;

-- Check if NOARCHIVELOG and not READ ONLY

SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;

IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN

raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');

END IF;

-- Check if not LOCAL_UNDO_ENABLED and not READ ONLY

SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';

IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN

raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');

END IF;

END;

-- let's add some intelligence... Parallel degree depending on PDB size

COLUMN parallel new_value parallel

SELECT CASE

WHEN ROUND(total_size/POWER(1024,3)) <= 1 THEN 1

WHEN ROUND(total_size/POWER(1024,3)) >= 5 THEN 4

ELSE 2 END AS parallel

FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';

-- Real clone PDB

CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause;

ALTER PLUGGABLE DATABASE &TargetPDB OPEN;

ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;

-- drop temporary db link: leaving it there exposes some risks

DROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone

SET ECHO OFF SERVEROUTPUT OFF

This is an example output:

SQL> @/tmp/clone
SQL>
SQL> -- Temporary database link name. We try to drop it if it exists but nevermind if it gives error
SQL> WHENEVER SQLERROR CONTINUE
SQL> DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;
old   1: DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone
new   1: DROP DATABASE LINK ORCL1_PDB1_tempclone

Database link dropped.

SQL>
SQL> -- The other tasks shouls succeed, otherwise exit
SQL> WHENEVER SQLERROR EXIT 1
SQL> CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';
old   1: CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias'
new   1: CREATE DATABASE LINK ORCL1_PDB1_tempclone connect to C##REMOTE_USER identified by manager using 'srcsrv:1521/PDB1'

Database link created.

SQL>
SQL> -- in this PL/SQL block we make sure that we do not copy if
SQL> -- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY
SQL> DECLARE
  2          l_log_mode sys.v_$database.log_mode%TYPE;
  3          l_local_undo database_properties.property_value%TYPE;
  4          l_open_mode sys.v_$pdbs.open_mode%TYPE;
  5
  6  BEGIN
  7          BEGIN
  8                  SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';
  9          EXCEPTION
 10                  WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');
 11          END;
 12
 13          -- Check if NOARCHIVELOG and not READ ONLY
 14          SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;
 15          IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN
 16                  raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');
 17          END IF;
 18
 19          -- Check if not LOCAL_UNDO_ENABLED  and not READ ONLY
 20          SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE  property_name = 'LOCAL_UNDO_ENABLED';
 21          IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN
 22                  raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');
 23          END IF;
 24  END;
 25  /
old   8:                SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';
new   8:                SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE name='PDB1';
old  14:        SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;
new  14:        SELECT log_mode INTO l_log_mode FROM sys.v_$database@ORCL1_PDB1_tempclone ;
old  20:        SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE  property_name = 'LOCAL_UNDO_ENABLED';
new  20:        SELECT property_value INTO l_local_undo FROM database_properties@ORCL1_PDB1_tempclone WHERE  property_name = 'LOCAL_UNDO_ENABLED';

PL/SQL procedure successfully completed.

SQL>
SQL> -- let's add some intelligence... Parallel degree depending on PDB size
SQL> COLUMN parallel new_value parallel
SQL> SELECT CASE
  2          WHEN ROUND(total_size/POWER(1024,3)) <= 1 THEN 1
  3          WHEN ROUND(total_size/POWER(1024,3)) >= 5 THEN 4
  4          ELSE 2 END AS parallel
  5  FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';
old   5: FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB'
new   5: FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE NAME='PDB1'

  PARALLEL
----------
         2

SQL>
SQL> -- Real clone PDB
SQL> CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause;
old   1: CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause
new   1: CREATE PLUGGABLE DATABASE ABC FROM PDB1@ORCL1_PDB1_tempclone USER_TABLESPACES=('ABC','DATA') PARALLEL          2 FILE_NAME_CONVERT=('/ORCL1/PDB1/','/ORCL2/ABC/')

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE &TargetPDB OPEN;
old   1: ALTER PLUGGABLE DATABASE &TargetPDB OPEN;
new   1: ALTER PLUGGABLE DATABASE ABC OPEN;

Pluggable database altered.

SQL>ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE; 
old   1: ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;
new   1: ALTER PLUGGABLE DATABASE ABC SAVE STATE;

Pluggable database altered.


SQL>
SQL> -- drop temporary db link: leaving it there exposes some risks
SQL> DROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone
  2
SQL> SET ECHO OFF SERVEROUTPUT OFF

SQL> @/tmp/clone

SQL>

SQL> -- Temporary database link name. We try to drop it if it exists but nevermind if it gives error

SQL> WHENEVER SQLERROR CONTINUE

SQL> DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone;

old 1: DROP DATABASE LINK &SourceCDB._&SourcePDB._tempclone

new 1: DROP DATABASE LINK ORCL1_PDB1_tempclone

Database link dropped.

SQL>

SQL> -- The other tasks shouls succeed, otherwise exit

SQL> WHENEVER SQLERROR EXIT 1

SQL> CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias';

old 1: CREATE DATABASE LINK &SourceCDB._&SourcePDB._tempclone connect to &SourceUser identified by &SourcePassword using '&SourceAlias'

new 1: CREATE DATABASE LINK ORCL1_PDB1_tempclone connect to C##REMOTE_USER identified by manager using 'srcsrv:1521/PDB1'

Database link created.

SQL>

SQL> -- in this PL/SQL block we make sure that we do not copy if

SQL> -- the source is NOARCHIVELOG or not LOCAL UNDO and PDB is not READ ONLY

SQL> DECLARE

2 l_log_mode sys.v_$database.log_mode%TYPE;

3 l_local_undo database_properties.property_value%TYPE;

4 l_open_mode sys.v_$pdbs.open_mode%TYPE;

6 BEGIN

7 BEGIN

8 SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';

9 EXCEPTION

10 WHEN no_data_found THEN raise_application_error(-20104,'Pluggable database not found.');

11 END;

13 -- Check if NOARCHIVELOG and not READ ONLY

14 SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;

15 IF l_log_mode = 'NOARCHIVELOG' AND l_open_mode <> 'READ ONLY' THEN

16 raise_application_error(-20102,'The database is in NOARCHIVELOG mode and the PDB is not READ ONLY.');

17 END IF;

19 -- Check if not LOCAL_UNDO_ENABLED and not READ ONLY

20 SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';

21 IF l_local_undo <> 'TRUE' AND l_open_mode <> 'READ ONLY' THEN

22 raise_application_error(-20103,'The LOCAL UNDO is not enabled for the database and the PDB is not READ ONLY.');

23 END IF;

24 END;

25 /

old 8: SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE name='&SourcePDB';

new 8: SELECT open_mode INTO l_open_mode FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE name='PDB1';

old 14: SELECT log_mode INTO l_log_mode FROM sys.v_$database@&SourceCDB._&SourcePDB._tempclone ;

new 14: SELECT log_mode INTO l_log_mode FROM sys.v_$database@ORCL1_PDB1_tempclone ;

old 20: SELECT property_value INTO l_local_undo FROM database_properties@&SourceCDB._&SourcePDB._tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';

new 20: SELECT property_value INTO l_local_undo FROM database_properties@ORCL1_PDB1_tempclone WHERE property_name = 'LOCAL_UNDO_ENABLED';

PL/SQL procedure successfully completed.

SQL>

SQL> -- let's add some intelligence... Parallel degree depending on PDB size

SQL> COLUMN parallel new_value parallel

SQL> SELECT CASE

2 WHEN ROUND(total_size/POWER(1024,3)) <= 1 THEN 1

3 WHEN ROUND(total_size/POWER(1024,3)) >= 5 THEN 4

4 ELSE 2 END AS parallel

5 FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB';

old 5: FROM sys.v_$pdbs@&SourceCDB._&SourcePDB._tempclone WHERE NAME='&SourcePDB'

new 5: FROM sys.v_$pdbs@ORCL1_PDB1_tempclone WHERE NAME='PDB1'

PARALLEL

----------

SQL>

SQL> -- Real clone PDB

SQL> CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause;

old 1: CREATE PLUGGABLE DATABASE &TargetPDB FROM &SourcePDB.@&SourceCDB._&SourcePDB._tempclone &UserTBSPClause PARALLEL &parallel &FileNameConvertClause

new 1: CREATE PLUGGABLE DATABASE ABC FROM PDB1@ORCL1_PDB1_tempclone USER_TABLESPACES=('ABC','DATA') PARALLEL 2 FILE_NAME_CONVERT=('/ORCL1/PDB1/','/ORCL2/ABC/')

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE &TargetPDB OPEN;

old 1: ALTER PLUGGABLE DATABASE &TargetPDB OPEN;

new 1: ALTER PLUGGABLE DATABASE ABC OPEN;

Pluggable database altered.

SQL>ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;

old 1: ALTER PLUGGABLE DATABASE &TargetPDB SAVE STATE;

new 1: ALTER PLUGGABLE DATABASE ABC SAVE STATE;

Pluggable database altered.

SQL>

SQL> -- drop temporary db link: leaving it there exposes some risks

SQL> DROP DATABASE LINK &SourcePDB.@&SourceCDB._&SourcePDB._tempclone

SQL> SET ECHO OFF SERVEROUTPUT OFF

If the clone process succeeds, at the end we should have the new ABC pluggable database with ABC and DATA tablespaces only.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
DATA
ABC

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

DATA

ABC

Yeah!

Any Cleanup needed?

What happened to the users? Actually, they are all still there:

SQL> select username from dba_users where oracle_maintained='N';

USERNAME
--------------------------------------------------------------------------------
PDBADMIN
C##REMOTE_CLONE
ABC
DEF
GHI

5 rows selected.

SQL> select username from dba_users where oracle_maintained='N';

USERNAME

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

PDBADMIN

C##REMOTE_CLONE

ABC

DEF

GHI

5 rows selected.

And the segments in the two skipped tablespaces are not there:

SQL> select owner, segment_name, tablespace_name from dba_segments 
2> where owner in ('ABC','DEF','GHI');

OWNER                SEGMENT_NAME         TABLESPACE_NAME
-------------------- -------------------- ------------------------------
ABC                  T2                   DATA
DEF                  T2                   DATA
ABC                  T1                   ABC

SQL> select owner, segment_name, tablespace_name from dba_segments

2> where owner in ('ABC','DEF','GHI');

OWNER SEGMENT_NAME TABLESPACE_NAME

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

ABC T2 DATA

DEF T2 DATA

ABC T1 ABC

So the table definitions are also gone?

SQL> select owner, table_name, tablespace_name from dba_tables
  2    where owner in ('ABC','DEF','GHI');

OWNER                TABLE_NAME           TABLESPACE_NAME
-------------------- -------------------- ------------------------------
ABC                  T2                   DATA
ABC                  T1                   ABC
DEF                  T2                   DATA
DEF                  T1                   DEF
GHI                  T1                   GHI

SQL> select owner, table_name, tablespace_name from dba_tables

2 where owner in ('ABC','DEF','GHI');

OWNER TABLE_NAME TABLESPACE_NAME

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

ABC T2 DATA

ABC T1 ABC

DEF T2 DATA

DEF T1 DEF

GHI T1 GHI

Not at all! The tables are still there and reference to tablespaces that do not exist anymore. Possible?

Actually, the tablespaces definition are still there if we look at v$tablespace:

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
DATA
ABC
DEF
GHI

8 rows selected.

SQL> select name from v$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

TEMP

DATA

ABC

DEF

GHI

8 rows selected.

If we give a look at the DBA_TABLESPACES view definition, there are a few interesting filters:

create or replace view DBA_TABLESPACES
...
where ts.online$ != 3
and bitand(flags,2048) != 2048
and bitand(ts.flags, 16777216) != 16777216
...
/

create or replace view DBA_TABLESPACES

...

where ts.online$ != 3

and bitand(flags,2048) != 2048

and bitand(ts.flags, 16777216) != 16777216

...

What is their meaning?

 online$     /* status (see KTT.H): */
             /* 1 = ONLINE, 2 = OFFLINE, 3 = INVALID */

  flags      /* various flags: see ktt3.h */
             /* 0x800 = this actually describes a group */ -- 2048
             /* 0x1000000 = bad transport of ts in pdb */ -- 16777216

online$ /* status (see KTT.H): */

/* 1 = ONLINE, 2 = OFFLINE, 3 = INVALID */

flags /* various flags: see ktt3.h */

/* 0x800 = this actually describes a group */ -- 2048

/* 0x1000000 = bad transport of ts in pdb */ -- 16777216

So the first WHERE clause skips all the INVALID TABLESPACES (when you drop a tablespace it is still stored in ts$ with this state), the second skips the definition of TEMPORARY TABLESPACE GROUPS, the third one is actually our candidate.

Indeed, this is what we get from ts$ for these tablespaces:

SQL> select name, decode(online$,1,'ONLINE',2,'OFFLINE',3,'INVALID',4,'READ ONLY','UNDEFINED') as STATUS, bitand(flags,2048), bitand(flags,16777216) from ts$;

NAME                           STATUS    BITAND(FLAGS,2048) BITAND(FLAGS,16777216)
------------------------------ --------- ------------------ ----------------------
SYSTEM                         ONLINE                     0                      0
SYSAUX                         ONLINE                     0                      0
UNDOTBS1                       ONLINE                     0                      0
TEMP                           ONLINE                     0                      0
DATA                           ONLINE                     0                      0
ABC                            ONLINE                     0                      0
DEF                            OFFLINE                    0               16777216
GHI                            OFFLINE                    0               16777216

8 rows selected.

SQL> select name, decode(online$,1,'ONLINE',2,'OFFLINE',3,'INVALID',4,'READ ONLY','UNDEFINED') as STATUS, bitand(flags,2048), bitand(flags,16777216) from ts$;

NAME STATUS BITAND(FLAGS,2048) BITAND(FLAGS,16777216)

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

SYSTEM ONLINE 0 0

SYSAUX ONLINE 0 0

UNDOTBS1 ONLINE 0 0

TEMP ONLINE 0 0

DATA ONLINE 0 0

ABC ONLINE 0 0

DEF OFFLINE 0 16777216

GHI OFFLINE 0 16777216

8 rows selected.

So the two tablespaces are filtered out because of this new multitenant flag.

If we try to drop the tablespaces, it succeeds:

SQL> drop tablespace def including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace ghi including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace def including contents and datafiles;

Tablespace dropped.

SQL> drop tablespace ghi including contents and datafiles;

Tablespace dropped.

But the user GHI, who has no objects anymore, is still there.

SQL> select username from dba_users
  2  where username  in ('ABC','DEF','GHI') ;

USERNAME
-------------------------
GHI
DEF
ABC

SQL> select username from dba_users

2 where username in ('ABC','DEF','GHI') ;

USERNAME

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

GHI

DEF

ABC

So we need to drop it explicitly.

SQL> drop user ghi cascade;

User dropped.

SQL>

SQL> drop user ghi cascade;

User dropped.

SQL>

Automate the cleanup

This is an example PL/SQL that is aimed to automate the cleanup.

Actually:

Users that had segments in one of the excluded tablespaces but do not have any segments left are just LOCKED (for security reasons, you can guess why).
Tablespaces that meet the “excluded PDB” criteria, are just dropped

set serveroutput on
DECLARE

        -- get the users that had tables in the non-cloned TBSP that do not have any other segments...
        CURSOR c_users IS
                SELECT DISTINCT owner as username FROM dba_tables t
                WHERE t.tablespace_name IN (
                        SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216
                ) AND NOT EXISTS (
                        SELECT 1 FROM dba_segments s WHERE s.owner=t.owner
                );

        -- get the list of non-cloned TBSP
        CURSOR c_tbsps IS
                SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216;

        r_users c_users%ROWTYPE;
        r_tbsps c_tbsps%ROWTYPE;

        e_drop_def_tbsp EXCEPTION;
        PRAGMA EXCEPTION_INIT (e_drop_def_tbsp, -12919);


BEGIN
        -- check if currently in a PDB
        IF sys_context('userenv','con_name') = 'CDB$ROOT' THEN
                raise_application_error(-20101,'The current container is CDB$ROOT. It must be a PDB.');
        END IF;

        -- loop all candidate users and lock them out
        OPEN c_users;
        LOOP
                        FETCH  c_users  INTO r_users;
                        EXIT WHEN c_users%NOTFOUND;
                        dbms_output.put_line ('ALTER USER '||r_users.username||' ACCOUNT LOCK');
                        execute immediate 'ALTER USER '||r_users.username||' ACCOUNT LOCK';
                        dbms_output.put_line ('User '||r_users.username||' locked.');
        END LOOP;

        -- loop all candidate tbsps and drop them
        OPEN c_tbsps;
        LOOP
                FETCH  c_tbsps  INTO r_tbsps;
                EXIT WHEN c_tbsps%NOTFOUND;
                dbms_output.put_line ('DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES');
                BEGIN
                        execute immediate 'DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES';
                        dbms_output.put_line ('Tablespace '||r_tbsps.name||' dropped.');
                EXCEPTION
                        WHEN e_drop_def_tbsp THEN
                                dbms_output.put_line ('Cannot drop default tablespace '||r_tbsps.name||'. Please change the default tablespace and drop it afterwards.');
                END;
        END LOOP;
END;
/

set serveroutput on

DECLARE

-- get the users that had tables in the non-cloned TBSP that do not have any other segments...

CURSOR c_users IS

SELECT DISTINCT owner as username FROM dba_tables t

WHERE t.tablespace_name IN (

SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216

) AND NOT EXISTS (

SELECT 1 FROM dba_segments s WHERE s.owner=t.owner

);

-- get the list of non-cloned TBSP

CURSOR c_tbsps IS

SELECT name FROM sys.ts$ WHERE online$=2 AND bitand(flags, 16777216)=16777216;

r_users c_users%ROWTYPE;

r_tbsps c_tbsps%ROWTYPE;

e_drop_def_tbsp EXCEPTION;

PRAGMA EXCEPTION_INIT (e_drop_def_tbsp, -12919);

BEGIN

-- check if currently in a PDB

IF sys_context('userenv','con_name') = 'CDB$ROOT' THEN

raise_application_error(-20101,'The current container is CDB$ROOT. It must be a PDB.');

END IF;

-- loop all candidate users and lock them out

OPEN c_users;

LOOP

FETCH c_users INTO r_users;

EXIT WHEN c_users%NOTFOUND;

dbms_output.put_line ('ALTER USER '||r_users.username||' ACCOUNT LOCK');

execute immediate 'ALTER USER '||r_users.username||' ACCOUNT LOCK';

dbms_output.put_line ('User '||r_users.username||' locked.');

END LOOP;

-- loop all candidate tbsps and drop them

OPEN c_tbsps;

LOOP

FETCH c_tbsps INTO r_tbsps;

EXIT WHEN c_tbsps%NOTFOUND;

dbms_output.put_line ('DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES');

BEGIN

execute immediate 'DROP TABLESPACE '||r_tbsps.name||' INCLUDING CONTENTS AND DATAFILES';

dbms_output.put_line ('Tablespace '||r_tbsps.name||' dropped.');

EXCEPTION

WHEN e_drop_def_tbsp THEN

dbms_output.put_line ('Cannot drop default tablespace '||r_tbsps.name||'. Please change the default tablespace and drop it afterwards.');

END;

END LOOP;

END;

This is the output for the clone procedure we have just seen:

SQL> @cleanup_partial_pdb.sql
ALTER USER GHI ACCOUNT LOCK
User GHI locked.
DROP TABLESPACE DEF INCLUDING CONTENTS AND DATAFILES
Tablespace DEF dropped.
DROP TABLESPACE GHI INCLUDING CONTENTS AND DATAFILES
Tablespace GHI dropped.

PL/SQL procedure successfully completed.

SQL> @cleanup_partial_pdb.sql

ALTER USER GHI ACCOUNT LOCK

User GHI locked.

DROP TABLESPACE DEF INCLUDING CONTENTS AND DATAFILES

Tablespace DEF dropped.

DROP TABLESPACE GHI INCLUDING CONTENTS AND DATAFILES

Tablespace GHI dropped.

PL/SQL procedure successfully completed.

The PL/SQL block can be quite slow depending on the segments and tablespaces, so it might be a good idea to have a custom script instead of this automated one.

What about user DEF?

The automated procedure has not locked the account DEF. Why?

Actually, the user DEF still has some segments in the DATA tablespace. Hence, the procedure cannot be sure what was the original intention: copy the user ABC ? The clone procedure allows only to specify the tablespaces, so this is the only possible result.

Promo: If you need to migrate to Multitenant and you need consulting/training, just contact me, I can help you 🙂

Multitenant Pills: Pushing your PDB to the Cloud in one step?

Posted on May 18, 2020 by Ludovico

The Oracle Multitenant architecture introduces some nice opportunities, including local and remote cloning (see this example on ORACLE_BASE blog).

However, the only available cloning procedure use the PULL method to get the data from the remote source PDB.

This can be a limitation, especially in environments where the cloning environment does not have direct access to the production, or where the clones must be done in the Cloud with no direct access to the production VLAN on-premises.

So, one common approach is to clone/detach locally, put the PDB files in the Object Store and then attach them in the cloud.

Another approach is to use SSH tunnels. If you follow my blog you can see it is something that I use every now and then to do stuff from on-premises to the cloud.

How to set it up?

Actually, it is super-easy: just prepare a script in the cloud that will do the create pluggable database, then trigger it from on-premises.

This is an example:

SRC_PDB=$1
TGT_PDB=$2
ALIAS=$3

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID=ORCL

$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
        create database link onprem_temp
          connect to C##ONPREM identified by manager using '$ALIAS';
        create pluggable database $2 from $1@onprem_temp ;
        drop database link onprem_temp;
        alter pluggable database $2 open;
        alter pluggable database $2 save state;
        exit
EOF

SRC_PDB=$1

TGT_PDB=$2

ALIAS=$3

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export ORACLE_SID=ORCL

$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF

create database link onprem_temp

connect to C##ONPREM identified by manager using '$ALIAS';

create pluggable database $2 from $1@onprem_temp ;

drop database link onprem_temp;

alter pluggable database $2 open;

alter pluggable database $2 save state;

exit

EOF

It takes as parameters: the name of the source PDB, the name of the target PDB and the SQL*Net descriptor to create the temporary database link from the cloud CDB to the on-premises CDB.

The user C##ONPREM must obviously exist on-premises with the following privileges:

grant create session, sysoper, create pluggable database to C##ONPREM container=all;

1	grant create session, sysoper, create pluggable database to C##ONPREM container=all;

The cloud database should use OMF so you don’t have to take care about file name conversions.

At this point, if you have set up correctly the SSH keys to connect to the cloud server, it is just a matter of running the script remotely using the proper SSH tunnel. Once the remote port binding established, the cloud server can contact the on-premises listener port using localhost:remote_bind:

oracle@onpremsrv:~$ ssh opc@cloudsrv -R 1522:onpremsrv:1521 \
 "sudo -u oracle /home/oracle/clone_db.sh PDBSRC PDBDEST localhost:1522/ONPREMCDB"

Database link created.

Pluggable database created.


Database link dropped.

oracle@onpremsrv:~$

oracle@onpremsrv:~$ ssh opc@cloudsrv -R 1522:onpremsrv:1521 \

"sudo -u oracle /home/oracle/clone_db.sh PDBSRC PDBDEST localhost:1522/ONPREMCDB"

Database link created.

Pluggable database created.

Database link dropped.

oracle@onpremsrv:~$

Of course the timing depends a lot on the size of the database and your connection to the Cloud.

I have tested this procedure with Oracle Database 19.7 on OCI compute instances and on DBaaS VM instance, it works without any additional work. Of course, it does not work for Autonomous Database 🙂

—

Ludovico

Cloning a PDB with ASM and Data Guard (no ADG) without network transfer

Posted on December 16, 2014 by Ludovico

Ok, if you’re reading this post, you may want to read also the previous one that explains something more about the problem.

Briefly said, if you have a CDB running on ASM in a MAA architecture and you do not have Active Data Guard, when you clone a PDB you have to “copy” the datafiles somehow on the standby. The only solution offered by Oracle (in a MOS Note, not in the documentation) is to restore the PDB from the primary to the standby site, thus transferring it over the network. But if you have a huge PDB this is a bad solution because it impacts your network connectivity. (Note: ending up with a huge PDB IMHO can only be caused by bad consolidation. I do not recommend to consolidate huge databases on Multitenant).

So I’ve worked out another solution, that still has many defects and is almost not viable, but it’s technically interesting because it permits to discover a little more about Multitenant and Data Guard.

The three options

At the primary site, the process is always the same: Oracle copies the datafiles of the source, and it modifies the headers so that they can be used by the new PDB (so it changes CON_ID, DBID, FILE#, and so on).

On the standby site, by opposite, it changes depending on the option you choose:

Option 1: Active Data Guard

If you have ADG, the ADG itself will take care of copying the datafile on the standby site, from the source standby pdb to the destination standby pdb. Once the copy is done, the MRP0 will continue the recovery. The modification of the header block of the destination PDB is done by the MRP0 immediately after the copy (at least this is what I understand).

ADG_PDB_copy

Option 2: No Active Data Guard, but STANDBYS=none

In this case, the copy on the standby site doesn’t happen, and the recovery process just add the entry of the new datafiles in the controlfile, with status OFFLINE and name UNKNOWNxxx. However, the source file cannot be copied anymore, because the MRP0 process will expect to have a copy of the destination datafile, not the source datafile. Also, any tentative of restore of the datafile 28 (in this example) will give an error because it does not belong to the destination PDB. So the only chance is to restore the destination PDB from the primary.

Option 3: No Active Data Guard, no STANDBYS=none

This is the case that I want to explain actually. Without the flag STANDBYS=none, the MRP0 process will expect to change the header of the new datafile, but because the file does not exist yet, the recovery process dies.
We can then copy it manually from the source standby pdb, and restart the recovery process, that will change the header. This process needs to be repeated for each datafile. (that’s why it’s not a viable solution, right now).

Let’s try it together:

The Environment

Primary

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBATL                         CDBATL_2
CDBATL                         CDBATL_1

08:13:08 SYS@CDBATL_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME INSTANCE_NAME

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

CDBATL CDBATL_2

CDBATL CDBATL_1

Standby

07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME                 INSTANCE_NAME
------------------------------ ----------------
CDBGVA                         CDBGVA_1
CDBGVA                         CDBGVA_2

07:35:56 SYS@CDBGVA_2> select db_unique_name, instance_name from v$database, gv$instance;

DB_UNIQUE_NAME INSTANCE_NAME

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

CDBGVA CDBGVA_1

CDBGVA CDBGVA_2

The current user PDB (any resemblance to real people is purely coincidental 😉 #haveUSeenMaaz):

08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';

OPEN_MODE  NAME
---------- ------------------------------
OPEN       MAAZ
OPEN       MAAZ

08:14:31 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ';

OPEN_MODE NAME

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

OPEN MAAZ

Cloning the PDB on the primary

First, make sure that the source PDB is open read-only

08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;

Pluggable database altered.

08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;

OPEN_MODE  NAME
---------- ------------------------------
READ ONLY  MAAZ
READ ONLY  MAAZ

08:45:54 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:46:20 SYS@CDBATL_2> alter pluggable database maaz open read only instances=all;

Pluggable database altered.

08:46:32 SYS@CDBATL_2> select open_mode, name from gv$pdbs where name='MAAZ' ;

OPEN_MODE NAME

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

READ ONLY MAAZ

Then, clone the PDB on the primary without the clause STANDBYS=NONE:

08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;

Pluggable database created.

08:46:41 SYS@CDBATL_2> create pluggable database LUDO from MAAZ;

Pluggable database created.

Review the clone on the Standby

At this point, on the standby the alert log show that the SYSTEM datafile is missing, and the recovery process stops.

Mon Dec 15 17:46:11 2014
Recovery created pluggable database LUDO
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
Recovery was unable to create the file as:
'+DATA'
MRP0: Background Media Recovery terminated with error 1274
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
Managed Standby Recovery not using Real Time Apply
Mon Dec 15 17:46:11 2014
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.34).
Datafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.
Mon Dec 15 17:46:11 2014
Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'
Mon Dec 15 17:46:11 2014
MRP0: Background Media Recovery process shutdown (CDBGVA_2)

Mon Dec 15 17:46:11 2014

Recovery created pluggable database LUDO

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01565: error in identifying file '+DATA'

ORA-17503: ksfdopn:2 Failed to open file +DATA

ORA-15045: ASM file name '+DATA' is not in reference form

Recovery was unable to create the file as:

'+DATA'

MRP0: Background Media Recovery terminated with error 1274

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'

Mon Dec 15 17:46:11 2014

Managed Standby Recovery not using Real Time Apply

Mon Dec 15 17:46:11 2014

Recovery interrupted!

Recovery stopped due to failure in applying recovery marker (opcode 17.34).

Datafiles are recovered to a consistent state at change 10433175 but controlfile could be ahead of datafiles.

Mon Dec 15 17:46:11 2014

Errors in file /u01/app/oracle/diag/rdbms/cdbgva/CDBGVA_2/trace/CDBGVA_2_mrp0_16464.trc:

ORA-01274: cannot add data file that was originally created as '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765'

Mon Dec 15 17:46:11 2014

MRP0: Background Media Recovery process shutdown (CDBGVA_2)

One remarkable thing, is that in the standby controlfile, ONLY THE SYSTEM DATAFILE exists:

18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';

    CON_ID
----------
         4

18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765

18:02:50 SYS@CDBGVA_2> select con_id from v$pdbs where name='LUDO';

CON_ID

----------

18:03:10 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765

We need to fix the datafiles one by one, but most of the steps can be done once for all the datafiles.

Copy the source PDB from the standby

What do we need to do? Well, the recovery process is stopped, so we can safely copy the datafiles of the source PDB from the standby site because they have not moved yet. (meanwhile, we can put the primary source PDB back in read-write mode).

-- on primary
08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;

Pluggable database altered.

-- on primary

08:58:07 SYS@CDBATL_2> alter pluggable database maaz close immediate instances=all;

Pluggable database altered.

08:58:15 SYS@CDBATL_2> alter pluggable database maaz open read write instances=all;

Pluggable database altered.

Copy the datafiles:

## on the standby:
RMAN> backup as copy pluggable database MAAZ;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00029 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.463.857404625
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00028 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.283.857404623
output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866396771.865.866397053 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

## on the standby:

RMAN> backup as copy pluggable database MAAZ;

Starting backup at 15-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=58 instance=CDBGVA_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00029 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.463.857404625

output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 tag=TAG20141215T175041 RECID=54 STAMP=866397046

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00028 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.283.857404623

output file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 tag=TAG20141215T175041 RECID=55 STAMP=866397051

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14

piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866396771.865.866397053 comment=NONE

Finished Control File and SPFILE Autobackup at 15-DEC-14

Do the magic

Now there’s the interesting part: we need to assign the datafile copies of the maaz PDB to LUDO.

Sadly, the OMF will create the copies on the bad location (it’s a copy, to they are created on the same location as the source PDB).

We cannot try to uncatalog and recatalog the copies, because they will ALWAYS be affected to the source PDB. Neither we can use RMAN because it will never associate the datafile copies to the new PDB. We need to rename the files manually.

RMAN> list datafilecopy all;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
55 28 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.86639709
 Tag: TAG20141215T175041

54 29 A 15-DEC-14 10295232 14-DEC-14
 Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.86639703
 Tag: TAG20141215T175041


RMAN> select name, guid from v$pdbs;

NAME       GUID
---------- --------------------------------
PDB$SEED   FFBCECBB503D606BE043334EA8C019B7
MAAZ       0243BF7B39D4440AE053334EA8C0E471
LUDO       0A4A0048D5321597E053334EA8C0E40A

RMAN> list datafilecopy all;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

55 28 A 15-DEC-14 10295232 14-DEC-14

Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.86639709

Tag: TAG20141215T175041

54 29 A 15-DEC-14 10295232 14-DEC-14

Name: +DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.86639703

Tag: TAG20141215T175041

RMAN> select name, guid from v$pdbs;

NAME GUID

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

PDB$SEED FFBCECBB503D606BE043334EA8C019B7

MAAZ 0243BF7B39D4440AE053334EA8C0E471

LUDO 0A4A0048D5321597E053334EA8C0E40A

It’s better to uncatalog the datafile copies before, so we keep the catalog clean:

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 RECID=55 STAMP=866397051
Uncataloged 1 objects


RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043' uncatalog;

uncataloged datafile copy
datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 RECID=54 STAMP=866397046
Uncataloged 1 objects

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049' uncatalog;

uncataloged datafile copy

datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049 RECID=55 STAMP=866397051

Uncataloged 1 objects

RMAN> change datafilecopy '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043' uncatalog;

uncataloged datafile copy

datafile copy file name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043 RECID=54 STAMP=866397046

Uncataloged 1 objects

Then, because we cannot rename files on a standby database with standby file management set to AUTO, we need to put it temporarily to MANUAL.

10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';
alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049'
*
ERROR at line 1:
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

10:24:21 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049'

ERROR at line 1:

ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';

NAME                                                         ISPDB
------------------------------------------------------------ -----
standby_archive_dest                                         FALSE
standby_file_management                                      FALSE

10:27:49 SYS@CDBGVA_2> select name, ispdb_modifiable from v$parameter where name like 'standby%';

NAME ISPDB

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

standby_archive_dest FALSE

standby_file_management FALSE

standby_file_management is not PDB modifiable, so we need to do it for the whole CDB.

10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

Database altered.

10:31:42 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:05:04 SYS@CDBGVA_2> alter database rename file '+DATA/CDBATL/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.825.866396765' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049';

Database altered.

then we need to set back the standby_file_management=auto or the recover will not start:

10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto;
System altered.

1 2	10:34:24 SYS@CDBGVA_2> alter system set standby_file_management=auto; System altered.

We can now restart the recovery.

The recovery process will:
– change the new datafile by modifying the header for the new PDB
– create the entry for the second datafile in the controlfile
– crash again because the datafile is missing

18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'
ORA-01157: cannot identify/lock data file 61 - see DBWR trace file
ORA-01111: name for data file 61 is unknown - rename to correct file
ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'


18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
---------------------------------------------------------------------------
+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061

18:11:30 SYS@CDBGVA_2> alter database recover managed standby database;

alter database recover managed standby database

ERROR at line 1:

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 61 is unknown - rename to correct file

ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'

ORA-01157: cannot identify/lock data file 61 - see DBWR trace file

ORA-01111: name for data file 61 is unknown - rename to correct file

ORA-01110: data file 61: '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061'

18:11:33 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049

/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061

We already have the SYSAUX datafile, right? So we can alter the name again:

18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:14:29 SYS@CDBGVA_2> alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043';

Database altered.

18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;

System altered.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

18:14:21 SYS@CDBGVA_2> alter system set standby_file_management=manual;

System altered.

18:14:29 SYS@CDBGVA_2> alter database rename file '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/UNNAMED00061' to '+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043';

Database altered.

18:14:31 SYS@CDBGVA_2> alter system set standby_file_management=auto;

System altered.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

This time all the datafiles have been copied (no user datafile for this example) and the recovery process will continue!! 🙂 so we can hit ^C and start it in background.

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-16043: Redo apply has been canceled.
ORA-01013: user requested cancel of current operation

 

18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

18:18:19 SYS@CDBGVA_2>

18:14:35 SYS@CDBGVA_2> alter database recover managed standby database;

alter database recover managed standby database

ERROR at line 1:

ORA-16043: Redo apply has been canceled.

ORA-01013: user requested cancel of current operation

18:18:10 SYS@CDBGVA_2> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

18:18:19 SYS@CDBGVA_2>

The Data Guard configuration reflects the success of this operation.

Do we miss anything?

Of course, we do!! The datafile names of the new PDB reside in the wrong ASM path. We need to fix them!

18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;

Database altered.

RMAN> backup as copy pluggable database ludo;

Starting backup at 15-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00061 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00060 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049
output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14
piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866398689.868.866398945 comment=NONE
Finished Control File and SPFILE Autobackup at 15-DEC-14

RMAN> switch pluggable database ludo to copy;

using target database control file instead of recovery catalog
datafile 60 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941"
datafile 61 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933"

18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941
+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933

18:23:07 SYS@CDBGVA_2> alter database recover managed standby database cancel;

Database altered.

RMAN> backup as copy pluggable database ludo;

Starting backup at 15-DEC-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=60 instance=CDBGVA_2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00061 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/sysaux.863.866397043

output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933 tag=TAG20141215T182213 RECID=56 STAMP=866398937

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00060 name=+DATA/CDBGVA/0243BF7B39D4440AE053334EA8C0E471/DATAFILE/system.864.866397049

output file name=+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941 tag=TAG20141215T182213 RECID=57 STAMP=866398943

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 15-DEC-14

Starting Control File and SPFILE Autobackup at 15-DEC-14

piece handle=+DATA/CDBGVA/AUTOBACKUP/2014_12_15/s_866398689.868.866398945 comment=NONE

Finished Control File and SPFILE Autobackup at 15-DEC-14

RMAN> switch pluggable database ludo to copy;

using target database control file instead of recovery catalog

datafile 60 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941"

datafile 61 switched to datafile copy "+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933"

18:23:54 SYS@CDBGVA_2> select name from v$datafile where con_id=4;

NAME

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

+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/system.867.866398941

+DATA/CDBGVA/0A4A0048D5321597E053334EA8C0E40A/DATAFILE/sysaux.866.866398933

I know there’s no practical use of this procedure, but it helps a lot in understanding how Multitenant has been implemented.

I expect some improvements in 12.2!!

Cheers

—

Ludo