Scribd Logo
Upload

Welcome to Scribd!

  • 51 views

    Practice 23 - Switching Over A Refreshable Clone PDB

    Uploaded by

    Logis M
    This document describes creating a refreshable clone PDB named PDB1R in ORADB2 by cloning PDB1 in ORADB. It then attempts to switch over to PDB1R but fails due to a bug in Oracle 19c that prevents switchover of refreshable PDBs from working properly. The source and target PDBs cannot be opened after the failed switchover.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Practice 23 - Switching Over A Refreshable Clone PDB

    Uploaded by

    Logis M
    51 views7 pages
    This document describes creating a refreshable clone PDB named PDB1R in ORADB2 by cloning PDB1 in ORADB. It then attempts to switch over to PDB1R but fails due to a bug in Oracle 19c that prevents switchover of refreshable PDBs from working properly. The source and target PDBs cannot be opened after the failed switchover.

    Original Title

    Practice+23+-+Switching+Over+a+Refreshable+Clone+PDB

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document describes creating a refreshable clone PDB named PDB1R in ORADB2 by cloning PDB1 in ORADB. It then attempts to switch over to PDB1R but fails due to a bug in Oracle 19c that prevents switchover of refreshable PDBs from working properly. The source and target PDBs cannot be opened after the failed switchover.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    51 views7 pages

    Practice 23 - Switching Over A Refreshable Clone PDB

    Uploaded by

    Logis M
    This document describes creating a refreshable clone PDB named PDB1R in ORADB2 by cloning PDB1 in ORADB. It then attempts to switch over to PDB1R but fails due to a bug in Oracle 19c that prevents switchover of refreshable PDBs from working properly. The source and target PDBs cannot be opened after the failed switchover.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 7

    Practice 23 Switching Over a Refreshable Clone PDB P a g e |1

    Practice 23: Switching Over a Refreshable Clone PDB

    Practice Overview
    In this practice, you will create a refreshable PDB in ORADB2, named PDB1R, by cloning PDB1 that is
    located in ORADB. You will then switchover to PDB1R.

    Practice Assumptions
     You have the ORADB (in srv1) and ORADB2 (in srv2) databases up and running.

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |2

    Creating a Refreshable PDB

    In the following steps, you will create a refreshable PDB called PDB1R in the machine srv2 by cloning
    PDB1 in that exists in ORADB1.

    PDB1 in ORADB could be referred to in this practice as the source PDB, and PDB3R in ORADB2 could be
    referred to as the target PDB.

    1. In Oracle VirtualBox, take a snapshot for srv1 and srv2.

    Caution: Do not proceed with the practice before taking a snapshot for the srv1 and srv2 first.

    2. Open two Putty sessions two srv1 and srv2 as oracle

    3. In srv1 and srv2, invoke SQL*Plus, login to oradb as sysdba.


    sqlplus / as sysdba

    4. In srv1 and srv2, Submit the following query to retrieve the value of the undocumented parameter
    _exadata_feature_on.
    This parameter must be turned on to use the “PDB Switchover” functionality. It is not needed for
    creating refreshable PDBs.
    set linesize 180
    col PARAMETER for a25
    col DEFAULTV for a5
    col SESSION_V for a15
    col INSTANCE_V for a15

    SELECT a.ksppinm PARAMETER, b.KSPPSTDF DEFAULTV,


    b.ksppstvl SESSION_V,
    c.ksppstvl INSTANCE_V
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
    WHERE a.indx = b.indx
    AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/'
    AND a.ksppinm = '_exadata_feature_on'
    /

    5. If the parameter is not turned on, set it to TRUE and restart oradb instance:
    ALTER SYSTEM SET "_exadata_feature_on"=TRUE SCOPE=SPFILE;
    shutdown immediate
    startup

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |3

    6. In srv1 and srv2, verify that the local undo is enabled in the CDBs.
    If the local undo is not enabled, we must open the source PDB in ready-only mode before switchover.
    col PROPERTY_NAME for a30
    col PROPERTY_VALUE for a30

    SELECT PROPERTY_NAME, PROPERTY_VALUE


    FROM DATABASE_PROPERTIES
    WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

    7. In srv1, create a common user and grant the privileges required to create a pluggable database from
    pdb1.
    We could use the prebuilt user SYSTEM but it is recommended to create a user dedicated to creating
    refreshable databases.
    CREATE USER c##ruser IDENTIFIED BY abc##1234;
    GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO
    c##ruser CONTAINER=ALL;
    GRANT CREATE PLUGGABLE DATABASE TO c##ruser CONTAINER=ALL;
    GRANT SYSOPER TO c##ruser CONTAINER=ALL;

    8. In srv2, connect to the root of ORADB database as sysdba then create a database link to ORADB
    using c##ruser user.
    This database link will be used to create the refreshable PDB in oradb2.
    -- verify the db link isn't there:
    col DB_LINK for a15
    col USERNAME for a15
    SELECT DB_LINK, USERNAME FROM DBA_DB_LINKS WHERE DB_LINK='ORADB';

    -- create the db link:


    CREATE DATABASE LINK oradb CONNECT TO c##ruser IDENTIFIED BY abc##1234 USING
    'ORADB';

    -- test it:
    SELECT SYSDATE FROM DUAL@ORADB;

    9. In srv2, create a refreshable PDB by cloning PDB1 and set its refresh mode to MANUAL.
    ALTER SESSION SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata';
    CREATE PLUGGABLE DATABASE pdb1r FROM pdb1@oradb REFRESH MODE MANUAL;
    ALTER PLUGGABLE DATABASE pdb1r OPEN READ ONLY;
    SELECT REFRESH_MODE FROM DBA_PDBS WHERE PDB_NAME='PDB1R';

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |4

    Switching Over the Refreshable PDB

    In the following steps, you will switchover the roles between PDB1 and PDB1R and then switch back.

    10. In oradb2 (srv2), create a common user with the same username and password as the one created
    in oradb.
    If the source PDB and clone PDB are in separate CDBs, then the user specified in the database link
    must have the same name and password in the source PDB and clone
    PDB. This user will be used for switchover.
    conn / as sysdba
    CREATE USER c##ruser IDENTIFIED BY abc##1234;
    GRANT CREATE SESSION, RESOURCE, CREATE ANY TABLE, UNLIMITED TABLESPACE TO
    c##ruser CONTAINER=ALL;
    GRANT CREATE PLUGGABLE DATABASE TO c##ruser CONTAINER=ALL;
    GRANT SYSOPER TO c##ruser CONTAINER=ALL;

    11. In srv2, retrieve the directory that hosts the PDB1R datafiles. Take a note of it into a temporary text
    file.
    SELECT F.NAME FNAME FROM V$DATAFILE F, CDB_PDBS P
    WHERE P.PDB_NAME='PDB1R' AND F.CON_ID=P.CON_ID;

    12. In srv1, display the contents of tnsnames.ora file and make sure it contains a connection
    descriptor to oradb2. If there is no connection descriptor for oradb2, add it into the file.
    host cat $TNS_ADMIN/tnsnames.ora

    13. In srv1, create a database link to oradb2 using c##ruser user.


    This database link will be used to switchover to the refreshable PDB
    conn / as sysdba

    -- verify the db link isn't there:


    col DB_LINK for a15
    col USERNAME for a15
    SELECT DB_LINK, USERNAME FROM CDB_DB_LINKS WHERE DB_LINK LIKE 'ORADB2%';

    -- create the db link:


    CREATE DATABASE LINK oradb2 CONNECT TO c##ruser IDENTIFIED BY abc##1234 USING
    'ORADB2';

    -- test it:
    SELECT SYSDATE FROM DUAL@ORADB2;

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |5

    Now we are ready to switchover to PDB1R. To monitor this process, create two monitoring sessions to the
    alertlog file of each database. The monitoring sessions in srv1 and srv2 would execute the following
    command respectively:
    tail -f /u01/app/oracle/diag/rdbms/oradb/oradb/trace/alert_oradb.log
    tail -f /u01/app/oracle/diag/rdbms/oradb2/oradb2/trace/alert_oradb2.log

    14. In srv1, close PDB1, switch the roles between it and PDB1R.
    conn / as sysdba
    ALTER PLUGGABLE DATABASE pdb1 REFRESH MODE MANUAL FROM pdb1r@oradb2 SWITCHOVER;

    The switchover command fails and returns the same error that we faced in the last two practices:
    ORA-65016: FILE_NAME_CONVERT must be specified
    You can check the monitoring sessions to see details on the returned error.

    15. Let’s try executing the command with the FILE_NAME_CONVERT option.
    Replace the <pdb1r-dir> with the directory of PDB1 datafiles noted earlier.
    host mkdir /u01/app/oracle/oradata/ORADB/pdb1r

    ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM pdb1r@oradb2 SWITCHOVER


    FILE_NAME_CONVERT=('<pdb1r-dir>','/u01/app/oracle/oradata/ORADB/pdb1r');

    The switchover functionality has a bug in the Oracle 19c on-premises databases. I reported this issue to
    Oracle support and should still waiting for their effective response on it.

    Let’s examine the status of the PDBs after this error:

    16. In srv1, check the OPEN_MODE of PDB1. Try opening it.


    The source PDB is not open and cannot be opened.
    SELECT OPEN_MODE FROM V$PDBS WHERE NAME='PDB1';
    ALTER PLUGGABLE DATABASE PDB1 OPEN;

    17. In srv2, check the OPEN_MODE of PDB1R. Try opening it in read only mode.
    The refreshable PDB is not open and cannot be opened.
    SELECT OPEN_MODE FROM V$PDBS WHERE NAME='PDB1R';
    ALTER PLUGGABLE DATABASE PDB1R OPEN READ ONLY;

    Cleanup

    18. Shutdown srv1 and srv2 and restore them from the snapshots taken in the beginning of the
    practice.

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |6

    Oracle Database Multitenant Administration, a course by Ahmed Baraka


    Practice 23 Switching Over a Refreshable Clone PDB P a g e |7

    Summary
    o With switchover to a refreshable PDB, we are supposed to be able to switch the roles between the
    source PDB and its associated refreshable PDB.

    o Switchover to refreshable PDB in on-premises Oracle 19c EE has a bug that prevents it from
    achieving its functionality.

    Oracle Database Multitenant Administration, a course by Ahmed Baraka

    You might also like