Plugging non-CDB as a PDB - Manual vs Autoupgrade

This post shows the the comparison between the manual method and using autoupgrade for plugging in a non-CDB as a PDB to CDB in the same host.
Manual Method
1. The non-CDB details are as follows

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test

SQL>  select cdb from v$database;

CDB
---
NO

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/test/datafile/system.260.1030288905
+DATA/test/datafile/sysaux.261.1030288913
+DATA/test/datafile/undotbs1.262.1030288917
+DATA/test/datafile/users.264.1030288923
+DATA/TEST/DATAFILE/audit.284.1063984491
+DATA/TEST/DATAFILE/box.285.1063984495
+DATA/TEST/DATAFILE/lobs.286.1063984499
+DATA/TEST/DATAFILE/indexes.287.1063984501
+DATA/TEST/DATAFILE/repository.288.1063984505
+DATA/TEST/DATAFILE/dbaudit.289.1063984509
+DATA/TEST/DATAFILE/audindexes.290.1063984513
+DATA/TEST/DATAFILE/audlobs.291.1063984515
+DATA/TEST/DATAFILE/catbs.292.1063984519
+DATA/TEST/DATAFILE/caindexes.293.1063984523
+DATA/TEST/DATAFILE/ctbs.294.1063984527
+DATA/TEST/DATAFILE/wtbs.295.1063984529
+DATA/TEST/DATAFILE/exptbs.296.1063984533

2. Claenly shutdown the non-CDB and open in read only mode.

shutdown immediate;
startup mount;
alter database open read only;

3. Create the descriptor file for the non-CDB and shutdown the non-CDB.

SQL> exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/test_non_cdb.xml');

PL/SQL procedure successfully completed.

shutdown immediate

4. CDB details are as follows

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
testcdb

5. Run the compatibility check on the CDB using the non-CDB descriptor file.

set serveroutput on
declare
    compa boolean;
    begin
    compa := dbms_pdb.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>'/home/oracle/test_non_cdb.xml');
 if compa = true then
      dbms_output.put_line('compatible');
 else
    dbms_output.put_line('not compatible');
 end if;
end;
/

compatible

PL/SQL procedure successfully completed.

6. As compatible status is shown run the create PDB statement

CREATE PLUGGABLE DATABASE testpdb1 USING '/home/oracle/test_non_cdb.xml' copy;

Pluggable database created.

7. This creates the PDB in mount state.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 TESTPDB1                       MOUNTED

Connect to the new PDB and check all data files are available

SQL>  alter session set container=testpdb1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
TESTPDB1

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/system.305.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/sysaux.304.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/undotbs1.303.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/users.309.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/audit.308.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/box.307.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/lobs.310.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/indexes.282.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/repository.280.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/dbaudit.281.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/audindexes.283.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/audlobs.297.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/catbs.298.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/caindexes.299.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/ctbs.300.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/wtbs.301.1063985599
+DATA/TESTCDB/9CBA2DF91A8C7012E053F4071FAC36E9/DATAFILE/exptbs.302.1063985599

17 rows selected.

8. As the last step before opening the new PDB run noncdb_to_pdb.sql.

@?/rdbms/admin/noncdb_to_pdb.sql

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 TESTPDB1                       MOUNTED
SQL> alter pluggable database open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         5 TESTPDB1                       READ WRITE NO

Autoupgrade Method
The same could be acheived with few steps using autoupgrade.jar (which is now the recommended tool for database upgrades). The same non-CDB and the CDB is used earlier is used for this case as well.

1. Create a autoupgrade configuration file with both source and target home set to the same versions. In this case both non-CDB and CDB were running out of the same home. Also the other main paramtere is target_pdb_copy_option which is set to "file_name_convert=NONE" since both non-CDB and CDB are using ASM. A new name oculd be specified to be used when the non-CDB is plugged in as a PDB (uatpdb in this case). The full configuration file used is shown below.

global.autoupg_log_dir=/home/oracle/upgr_log

upg1.sid=test
upg1.source_home=/opt/app/oracle/product/19.x.0/dbhome_1
upg1.target_home=/opt/app/oracle/product/19.x.0/dbhome_1
upg1.target_cdb=testcdb
upg1.target_pdb_name=uatpdb
upg1.target_pdb_copy_option=file_name_convert=NONE

2. The version of the autoupgrade is 19.9

java -jar autoupgrade.jar -version
build.hash bf4ccd4
build.version 19.9.2
build.date 2020/08/31 13:47:51
build.max_target_version 19
build.supported_target_versions 12.2,18,19
build.type production

3. Run the autoupgrade with deploy option. This processes pluggin the non-CDB as with the copy option in the manual method.

java -jar autoupgrade.jar -config noncdb_pdb.cfg -mode deploy

4. End of the run the PDB is created and in read/write mode

SQL> show pdbs
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB2                       MOUNTED
         4 DEVPDB                         READ WRITE NO
         5 UATPDB                         READ WRITE NO

5. The data files of the PDB are copied into directory under $asm_diskgroup/cdb_sid/pdb_guid format.

+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/system.345.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/sysaux.344.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/undotbs1.343.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/users.329.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/audit.330.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/box.331.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/lobs.332.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/indexes.333.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/repository.334.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/dbaudit.335.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/audindexes.336.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/audlobs.337.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/catbs.338.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/caindexes.339.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/ctbs.340.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/wtbs.341.1064159817
+DATA/TESTCDB/BAFE73D2E50633B2E053060C1FAC8FBD/DATAFILE/exptbs.342.1064159817

17 rows selected.

The comparision shows that autoupgrade method require few steps and less manaul intervention.

Related Posts
Plugging non-CDB as a PDB into a CDB in the Same Host
Plugging in non-CDB to CDB and reusing the non-CDB Datafiles in Standby

Far Sync Instance Stats Not Shown on Data Guard Performance Page on Cloud Control 13.4

Enterprise Manager Cloud control has a separate performance page for viewing data gaurd performance. This gives one page view of redo genration, apply rate and transport lags times. However, in a recent deployment it was observed that no stats related to far sync instances was shown on this page. Below is the screenshot of the data guard performance page (not all standby instances are shown).

The data guard performance page is not available out of the box. One has to run the verify configuration under data guard administration tag twice (yes, twice. refere 1484028.1) to get the views (redo generation, apply rate and trasport lag times) populated with graphs. However, no matter how many times verify configuration was run the far sync instance views were not populated.
It's understandable far sync instances will not have any stats for apply lag or apply rate as they don't do any applying. But at least transport lag which is relevant to far sync instances could be populated. This is shown with dgmgrl.

DGMGRL> show far_sync fs1

Far Sync Instance - fs1

Transport Lag: 0 seconds (computed 0 seconds ago)
Instance(s):
fs1

Far Sync Instance Status:
SUCCESS

During the verify configuration run the EM (assumed) add a new database property called "dbDisplayName" to the DG configuration. This could be observed on the data guard broker log file.

EDIT DATABASE db3 SET PROPERTY dbDisplayName = db1_host_name
Property "dbDisplayName" set to "db1_host_name" for member "db1"
EDIT DATABASE db3 SET PROPERTY dbDisplayName = db1_host_name completed successfully

This property value is used by the EM interally. The "show database" comamnd on dgmgrl will also show this as the "Enterprise Manager Name".

DGMGRL> show database db1

Database - db1

Enterprise Manager Name: db1_host_name
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
db1

Database Status:
SUCCESS 

However, no such command was executed for far sync (i.e edit far_sync fs1 SET PROPERTY dbDisplayName = fs1_host_name).
On the emoms.log could see the following, possibly due to the missing dbDisplayName value on the far sync instance.

2020-08-26 09:20:49,225 [Thread-624744] WARN em.dataguard getDBTargetInfo.1183 - StandbyTargetService.getDBTargetInfo : no match for db_unique_name = fs1, sid = fs1
2020-08-26 09:21:12,020 [EMUI_09_19_58_/console/database/dataguard] WARN em.dataguard getDBTargetInfo.1183 - StandbyTargetService.getDBTargetInfo : no match for db_unique_name = fs1, sid = fs1

The error message shown on the page which states "1. Error: Unable to obtain Agent location for target pptbxfs1. You may not currently be logged in." is ruled out as sys login is used for far sync instead of dbsnmp on the EM. Far sync are in mount mode and need a user that can connect to a mounted instance (refer 1526818.1).
After a SR was rasied, following a 4 month investigation which involved demoing the observed behavior oracle support concluded "this is expected behavior for 'Farsync instance' details not shown in OEM because there is no views are available in Farsync instance like other instance to capture details from farsync to OMS to display in OEM".
This means transport lag stats related to far sync instances will not be visible on the data guard performance page until additional development is done on the OEM.