Create a PDB by using a non-CDB in different version and different characterset
1. Create noncdb : 11.2.0.4 with character set AL32UTF8
2. Upgrade character set from AL32UTF8 to WE8MSWIN1252
3. Upgrade noncdb from 11.2.0.4 to 12.1.0.2
–Verify the target db characterset
-bash-4.1$ . oraenv
ORACLE_SID = [oracle] ? orcl12102cdb
The Oracle base has been set to /refresh/home/app/12.1.0.2/oracle
-bash-4.1$
-bash-4.1$ sql
SQL> startup
ORACLE instance started.
Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL> select * from database_properties where property_name=’NLS_CHARACTERSET’;
PROPERTY_NAME PROPERTY_VALUEDESCRIPTION
——————————————————————————–
NLS_CHARACTERSET WE8MSWIN1252 Character set
#1 Create noncdb in database version 11.2.0.4 with characterset al32utf8
-bash-4.1$ ./dbca -silent -createdatabase -gdbname ncdb11g -sid ncdb11g
-templatename General_Purpose.dbc -syspassword oracle_4U
-systempassword oracle_4U -emconfiguration none -totalmemory 1024
-databasetype oltp -characterset al32utf8
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/refresh/home/app/11.2.0.4/oracle/cfgtoollogs/dbca/ncdb11g/ncdb11g.log” for further details.
-bash-4.1$ . oraenv
ORACLE_SID = [orcl11204] ? ncdb11g
The Oracle base remains unchanged with value /refresh/home/app/11.2.0.4/oracle
-bash-4.1$ sql
SQL> select * from database_properties where property_name=’NLS_CHARACTERSET’;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
——————————————————————————–
NLS_CHARACTERSET AL32UTF8 Character set
#2. Upgrade character set from AL32UTF8 to WE8MSWIN1252
SQL> exit
-bash-4.1$ csscan
Character Set Scanner v2.2 : Release 11.2.0.4.0 – Production on Thu Jul 25 12:26:42 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
-bash-4.1$
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 25 12:27:19 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/csminst.sql
-bash-4.1$ csscan
Character Set Scanner v2.2 : Release 11.2.0.4.0 – Production on Thu Jul 25 12:27:39 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1
Current database character set is AL32UTF8.
Enter new database character set name: > WE8MSWIN1252
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..): 1 >
Enumerating tables to scan…
. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 1 scanning SYS.PARAMETER$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.METHOD$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.TYPE$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.ATTRIBUTE$[AAAAIGAABAAAA2IAAA]
. process 1 scanning SYS.ARGUMENT$[AAAADfAABAAAAXYAAA]
………………..
. process 1 scanning CTXSYS.DR$DBO
. process 1 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 1 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$ERRCODE
Creating Database Scan Summary Report…
Creating Individual Exception Report…
Scanner terminated successfully.
-bash-4.1$ echo $ORACLE_SID
ncdb11g
-bash-4.1$ sql
SQL> select * from database_properties where property_name=’NLS_CHARACTERSET’;
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
——————————————————————————–
NLS_CHARACTERSET AL32UTF8 Character set
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2257520 bytes
Variable Size 272633232 bytes
Database Buffers 520093696 bytes
Redo Buffers 6717440 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/csalter.plb
3 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER(‘&conf’) ‘Y’) then
new 6: if (UPPER(‘Y’) ‘Y’) then
Checking data validity…
begin converting system objects
1 row in table SYS.WRI$_ADV_OBJECTS is converted
1 row in table SYS.RADM_FPTM_LOB$ is converted
130 rows in table SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS are converted
9994 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
12 rows in table APEX_030200.WWV_FLOW_BUTTON_TEMPLATES are converted
1 row in table SYS.RULE$ is converted
7416 rows in table APEX_030200.WWV_FLOW_PAGE_PLUGS are converted
179 rows in table SYS.METASTYLESHEET are converted
21 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
5 rows in table MDSYS.SDO_XML_SCHEMAS are converted
64 rows in table APEX_030200.WWV_FLOW_TEMPLATES are converted
1490 rows in table SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS are converted
5 rows in table SYS.WRI$_ADV_DIRECTIVE_META are converted
166 rows in table APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES are converted
1754 rows in table APEX_030200.WWV_FLOW_STEPS are converted
2238 rows in table APEX_030200.WWV_FLOW_STEP_PROCESSING are converted
39 rows in table APEX_030200.WWV_FLOW_SHORTCUTS are converted
11 rows in table APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS are converted
54 rows in table APEX_030200.WWV_FLOW_ROW_TEMPLATES are converted
5 rows in table APEX_030200.WWV_FLOW_FLASH_CHART_SERIES are converted
10 rows in table APEX_030200.WWV_FLOW_BANNER are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
30 rows in table APEX_030200.WWV_FLOW_WORKSHEETS are converted
44 rows in table APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR are converted
27 rows in table SYS.WRI$_REPT_FILES are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
105 rows in table APEX_030200.WWV_FLOW_LIST_TEMPLATES are converted
31 rows in table SYSMAN.MGMT_IP_SQL_STATEMENTS are converted
45 rows in table APEX_030200.WWV_FLOW_PROCESSING are converted
176 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
PL/SQL procedure successfully completed.
Alter the database character set…
CSALTER operation completed, please restart database
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2257520 bytes
Variable Size 272633232 bytes
Database Buffers 520093696 bytes
Redo Buffers 6717440 bytes
Database mounted.
Database opened.
SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;
PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET WE8MSWIN1252
#3. Upgrade noncdb from 11.2.0.4 to 12.1.0.2
-bash-4.1$ echo $ORACLE_SID
ncdb11g
-bash-4.1$
-bash-4.1$ sql
SQL> @$NEW_ORACLE_HOME/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package…
***************************************************************************
Executing Pre-Upgrade Checks in NCDB11G…
***************************************************************************
************************************************************
====>> ERRORS FOUND for NCDB11G <> PRE-UPGRADE RESULTS for NCDB11G < sql SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL> @$ORACLE_BASE/cfgtoollogs/ncdb11g/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2019-07-25 12:58:36 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups…
Executing in container NCDB11G
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: –> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: –> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
– Stop EM Database Control:
$> emctl stop dbconsole
– Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
**********************************************************************
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: –> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************
**********************************************************************
Check Tag: APEX_UPGRADE_MSG
Check Summary: Check that APEX will need to be upgraded.
Fix Summary: Oracle Application Express can be manually upgraded prior to database upgrade.
**********************************************************************
Fixup Returned Information:
INFORMATION: –> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
*************************************************
************* Fixup Summary ************
4 fixup routines generated INFORMATIONAL messages that should be reviewed.
**************** Pre-Upgrade Fixup Script Complete *********************
-bash-4.1$ . oraenv
ORACLE_SID = [ncdb11g] ? orcl12102cdb
The Oracle base has been changed from /refresh/home/app/11.2.0.4/oracle to /refresh/home/app/12.1.0.2/oracle
-bash-4.1$ echo $ORACLE_HOME
/refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2
-bash-4.1$
-bash-4.1$ export ORACLE_SID=ncdb11g
-bash-4.1$ sql
SQL> startup upgrade pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
-bash-4.1$ cd $NEW_ORACLE_HOME/rdbms/admin/
-bash-4.1$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 0
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = 0
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /refresh/home/app/12.1.0.2/oracle
Analyzing file catupgrd.sql
Log files in /refresh/home/app/12.1.0.2/oracle/product/12.1.0.2/dbhome2/rdbms/admin
catcon: ALL catcon-related output will be written to catupgrd_catcon_14530.lst
catcon: See catupgrd*.log files for output generated by scripts
catcon: See catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
SQL Process Count = 0
New SQL Process Count = 4
——————————————————
Phases [0-73]
Serial Phase #: 0 Files: 1
Time: 163s
Serial Phase #: 1 Files: 5 Time: 59s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 17s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 27s
Serial Phase #: 6 Files: 1 Time: 16s
Serial Phase #: 7 Files: 4 Time: 11s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 58s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 19s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 14s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 27s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 2s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 36s
Restart Phase #:20 Files: 1 Time: 0s
Serial Phase #:21 Files: 3 Time: 9s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 23 Time: 123s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 60s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 1s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 29s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 1s
Serial Phase #:34 Files: 1 Time: 5s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 1s
Serial Phase #:37 Files: 4 Time: 62s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13
Oracle Database 12.1 Post-Upgrade Status Tool 07-25-2019 14:51:06
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:14:02
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:02:13
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:23
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:23
Oracle OLAP API VALID 12.1.0.2.0 00:00:26
Oracle XDK VALID 12.1.0.2.0 00:01:06
Oracle Text VALID 12.1.0.2.0 00:01:02
Oracle XML Database VALID 12.1.0.2.0 00:06:56
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:18
Oracle Multimedia VALID 12.1.0.2.0 00:03:08
Spatial UPGRADED 12.1.0.2.0 00:12:54
Oracle Application Express VALID 4.2.5.00.08 00:32:31
Final Actions 00:01:36
Post Upgrade 00:01:50
Total Upgrade Time: 01:20:44
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
Grand Total Upgrade Time: [0d:1h:22m:57s]
#After upgrade upgrade the timezone file from 14 to 18.
-bash-4.1$ cd $ORACLE_HOME/oracore/zoneinfo
-bash-4.1$ head -2 readme.txt
Current Structure version: 3
Current Content Version :18
-bash-4.1$
-bash-4.1$ sql
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(18);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%’
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
——————————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 18
DST_UPGRADE_STATE PREPARE
SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.dst$affected_tables;
COUNT(*)
———-
0
SQL> select count(*) from sys.dst$error_table;
COUNT(*)
———-
0
SQL> exec DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE ‘DST_%’
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
——————————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_UPGRADE(18);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;
2 3 4
PROPERTY_NAME VALUE
——————————
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE UPGRADE
SQL> col OWNER format a30
col TABLE_NAME format a30
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;
OWNER TABLE_NAME UPG
—————————— —————————— —
SYS XS$ROLE_GRANT NO
SYS AQ$_AQ_PROP_TABLE_L NO
SYS AQ$_ALERT_QT_L NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_L NO
SYS SCHEDULER$_FILEWATCHER_RESEND NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_L NO
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES
SYS PDB_ALERT$ NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_S NO
SYS AQ$_ALERT_QT_S NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
………..
DBSNMP MGMT_DB_FEATURE_LOG NO
WMSYS WM$WORKSPACES_TABLE$ NO
79 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
SQL>
SQL> startup pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> truncate table sys.dst$error_table;
Table truncated.
SQL> truncate table sys.dst$trigger_table;
Table truncated.
SQL>
SQL>
SQL> alter session set “_with_subquery”=materialize;
Session altered.
SQL>
SQL> alter session set “_simple_view_merging”=TRUE;
Session altered.
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> VAR numfail number
SQL>
SQL> BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => ‘SYS.DST$ERROR_TABLE’,
log_triggers_table => ‘SYS.DST$TRIGGER_TABLE’,
error_on_overlap_time => TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);
END;
/ 2 3 4 5 6 7 8 9 10 11
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_S”
Number of failures: 0
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> BEGIN
DBMS_DST.END_UPGRADE(:numfail);
DBMS_OUTPUT.PUT_LINE(‘Failures:’|| :numfail);
END;
/ 2 3 4 5
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
SQL>
SQL>
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
——————– ———- ———-
timezlrg_18.dat 18 0
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
——————————
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
1 row updated.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
18
#Unplug non cdb(ncdb11g) (12.1.0.2)
SQL> SQL> startup open read only pfile=/refresh/home/app/11.2.0.4/oracle/product/11.2.0.4/dbhome_4/dbs/initncdb11g.ora
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 2929552 bytes
Variable Size 318770288 bytes
Database Buffers 478150656 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> EXEC DBMS_PDB.DESCRIBE(‘/tmp/ncdb11g.xml’);
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
#Plugging non cdb(ncdb11g) into cdb(orcl12102cdb)
-bash-4.1$ . oraenv
ORACLE_SID = [ncdb11g] ? orcl12102cdb
The Oracle base remains unchanged with value /refresh/home/app/12.1.0.2/oracle
-bash-4.1$ sql
SQL> startup
ORACLE instance started.
Total System Global Area 3087007744 bytes
Fixed Size 2929160 bytes
Variable Size 754978296 bytes
Database Buffers 2315255808 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL>
SQL> alter pluggable database pdb1 open ;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE PDB1 SAVE STATE;
Pluggable database altered.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY(‘/tmp/ncdb11g.xml’) then
3 dbms_output.put_line(‘compatibility’);
4 else
5 dbms_output.put_line(‘no compatibility’);
6 end if;
7 END;
8 /
compatibility
PL/SQL procedure successfully completed.
SQL> CREATE PLUGGABLE DATABASE PDB2 USING ‘/tmp/ncdb11g.xml’ move
file_name_convert=(‘/refresh/home/app/11.2.0.4/oracle/oradata/ncdb11g’,
‘/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2’);
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter session set container=pdb2;
Session altered.
SQL>@?/rdbms/admin/noncdb_to_pdb.sql
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 PDB2 MOUNTED
SQL> startup
Pluggable Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 PDB2 READ WRITE NO
SQL>
SQL> select name from v$datafile;
NAME
——————————————————————————–
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/undotbs01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/system01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/sysaux01.dbf
/refresh/home/app/12.1.0.2/oracle/oradata/orcl12102cdb/pdb2/users01.dbf
4 rows selected.
=====
No responses yet