Vishwanath Nayak : October 2017

Current ORACLE_HOME= /u00/app/oracle/product/11.2.0/db_1

Target ORACLE_HOME= /u00/app/oracle/product/12.1.0/database

Download 12c binaries and unzip in new home:

-rwxr-xr-x 1 oracle oinstall 1673544724 Dec 19 10:18 linuxamd64_12102_database_1of2.zip

-rwxr-xr-x 1 oracle oinstall 1014530602 Dec 19 10:20 linuxamd64_12102_database_2of2.zip

Install 12c Database software :

BANK-> cd /u00/app/oracle/product/12.1.0/database/

BANK-> ./runInstaller oracle.install.option=INSTALL_DB_SWONLY \

> ORACLE_BASE=/u00/app/oracle \

> ORACLE_HOME=/u00/app/oracle/product/12.1.0/database \

> UNIX_GROUP_NAME=oinstall \

> oracle.install.db.DBA_GROUP=dba \

> oracle.install.db.OPER_GROUP=oper \

> oracle.install.db.BACKUPDBA_GROUP=dba \

> oracle.install.db.DGDBA_GROUP=dba \

> oracle.install.db.KMDBA_GROUP=dba \

> FROM_LOCATION=../stage/products.xml \

> INVENTORY_LOCATION=/u00/app/oraInventory \

> SELECTED_LANGUAGES=en \

> oracle.install.db.InstallEdition=EE \

> DECLINE_SECURITY_UPDATES=true -silent -waitForCompletion

Sample output:

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 1512 MB Passed

Checking swap space: must be greater than 150 MB. Actual 15297 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-12-19_02-00-46PM. Please wait ...

The installation of Oracle Database 12c was successful.

Please check '/u00/app/oraInventory/logs/silentInstall2016-12-19_02-25-07PM.log' for more details.

As a root user, execute the following script(s):

1. /u00/app/oracle/product/12.1.0/database/root.sh

PRECHECKS:

1. CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)

SQL> select comp_id,status from dba_registry;

COMP_ID STATUS

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

CATALOG VALID

CATPROC VALID

2. Check duplicate objects owned by system and sys

select object_name, object_type

from dba_objects

where object_name||object_type in

(select object_name||object_type

from dba_objects

where owner = 'SYS')

and owner = 'SYSTEM';

OBJECT_NAME OBJECT_TYPE

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

DBMS_REPCAT_AUTH PACKAGE BODY

AQ$_SCHEDULES_PRIMARY INDEX

AQ$_SCHEDULES TABLE

DBMS_REPCAT_AUTH PACKAGE

If you found any other objects other than these four, then investigate or cleanup those objects owned by sys and system.

3. Check the integrity of database.

4. Run utlrp.sql to validate invalid objects

sql>@utlrp.sql;

5. Run preupgrade tool

SQL> @preupgrd.sql;

Sample Output:

SQL> @preupgrd.sql;

Loading Pre-Upgrade Package...

***************************************************************************

Executing Pre-Upgrade Checks in UPGRD...

***************************************************************************

************************************************************

====>> ERRORS FOUND for UPGRD <<====

The following are *** ERROR LEVEL CONDITIONS *** that must be addressed

prior to attempting your upgrade.

Failure to do so will result in a failed upgrade.

You MUST resolve the above errors prior to upgrade

************************************************************

====>> PRE-UPGRADE RESULTS for UPGRD <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/postupgrade_fixups.sql

************************************************************

***************************************************************************

Pre-Upgrade Checks in UPGRD Completed.

***************************************************************************

Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.

SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql

Sample Output:

SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql;

Pre-Upgrade Fixup Script Generated on 2016-12-19 15:15:25 Version: 12.1.0.2 Build: 006

Beginning Pre-Upgrade Fixups...

Executing in container UPGRD

**********************************************************************

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 *********************

If still changes are not reflecting Check the preupgrade_fixups.sql script and do the changes manually.

In my case, these changes recommended where I changed below parameters.

– processes” needs to be increased to at least 300

– ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE

– EXECUTE dbms_preup.purge_recyclebin_fixup;

– Alter system set job_queue_processes= 100

– EXECUTE dbms_stats.gather_dictionary_stats;

6. Dependencies on Network Utility Packages:

SQL> SELECT * FROM DBA_DEPENDENCIES

WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_

INADDR','DBMS_LDAP')

AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); 2 3 4

no rows selected

7. Check the timezone version:

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.

8. Optimizer Statistics

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

09. Ensure That No Files Need Media Recovery Before Upgrading

SQL> SELECT * FROM v$recover_file;

no rows selected

10. Ensure That No Files Are in Backup Mode before Upgrading

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

11. Resolve Outstanding Distributed Transactions Before Upgrading

SQL> SELECT * FROM dba_2pc_pending;

12. Purge the Database Recycle Bin Before Upgrading :

SQL> PURGE DBA_RECYCLEBIN;

13. Synchronize the Standby Database with the Primary Database When Upgrading

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)

FROM v$parameter

WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3

14. Disable cronjob,

Take backup of crontab and comment the same.

15. Disable dbms_schduler jobs:

SQL> set pagesize 2000

SQL> set lines 2000

SQL> set long 99999

SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

Disable the scheduled jobs by using below command

SQL> execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);

16. Verify system and sys default tablespace.(Both should be system tablespace)

SQL> SELECT username, default_tablespace

FROM dba_users

WHERE username in ('SYS','SYSTEM'); 2 3

USERNAME DEFAULT_TABLESPACE

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

SYSTEM SYSTEM

SYS SYSTEM

17. Check whether database has any externally authenticated SSL users

sql>SELECT name FROM sys.user$

WHERE ext_username IS NOT NULL

AND password = 'GLOBAL';

18. Remove EM repository

---Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express . Therefore no repository is needed anymore .

---Run the below script

$emctl stop dbcontrol

SQL> @ ?/rdbms/admin/emremove.sql

19. Review any unnecessary hidden/underscore parameters

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected

20. With this our precheck completes. Now upgrade phase.

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

UPGRADE:

21. stop the listener and shutdown the database.

lsnrctl stop

SQL>shutdown immediate

22.Update the ORACLE_HOME,PATH pointing to 12C Home.

export ORACLE_HOME= /apps/oracle/product/12.1.0.2/database

export PATH=$ORACLE_HOME/bin:$PATH

23. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.

24. start the database in upgrade mode.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> startup UPGRADE

SQL> exit

25.Run catupgrade script from os level with parallel.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Open another window and check the log.

Sample Output:

TESCOBANK-> $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 6

SQL PDB Process Count N = 0

Input Directory d = 0

Phase Logging Table t = 0

Log Dir l = /u00/app/oracle/product/12.1.0/database/diagnostics

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 = /u00/app/oracle

Analyzing file catupgrd.sql

Log files in /u00/app/oracle/product/12.1.0/database/diagnostics

cd /u00/app/oracle/product/12.1.0/database/diagnostics

tail -100f catupgrd0.log

26. Run the Post-Upgrade Status l

---Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @utlu121s.sql

Sample Output:

SQL> @utlu121s.sql

PL/SQL procedure successfully completed.

CATCTL REPORT = /u00/app/oracle/product/12.1.0/database/cfgtoollogs/UPGRD/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

Oracle Database 12.1 Post-Upgrade Status Tool 12-20-2016 08:38:10

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:11:27

JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:03:28

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:58

OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:22

OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00

Oracle OLAP API VALID 12.1.0.2.0 00:00:24

Oracle XDK VALID 12.1.0.2.0 00:00:41

Oracle Text VALID 12.1.0.2.0 00:00:54

Oracle XML Database VALID 12.1.0.2.0 00:02:08

Oracle Database Java Packages VALID 12.1.0.2.0 00:00:14

Oracle Multimedia VALID 12.1.0.2.0 00:02:22

Spatial UPGRADED 12.1.0.2.0 00:05:08

Oracle Application Express VALID 4.2.5.00.08 00:24:24

Final Actions 00:01:33

Post Upgrade 00:01:40

Total Upgrade Time: 00:56:14

27.Run Catuppst.sql

The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process.

Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step.

Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

SQL>@catuppst.sql

Now upgrade is completed. Now Proceed with POST CHECK.

27. UPGRADE DST TIME ZONE:

Download the dst upgrade script from oracle

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=264501110788152&parent=DOCUMENT&sourceId=1509653.1&id=1585343.1&_afrWindowMode=0&_adf.ctrl-state=1wvfm6az4_4

unzip DBMS_DST_scriptsV1.9.zip

Archive: DBMS_DST_scriptsV1.9.zip

creating: DBMS_DST_scriptsV1.9/

inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql

$ cd DBMS_DST_scriptsV1.9

SQL> @countstatsTSTZ.sql

Purge the scheduler jobs

SQL> exec dbms_scheduler.purge_log;

Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically)

SQL> spool upg_tzv_check.log

SQL> @upg_tzv_check.sql;

Run upg_tzv_apply.sql ( It will do the actual dst upgrade)

SQL> @upg_tzv_apply.sql

Once dst upgrade is successful , validate the time_zone(It should be 18)

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

1 row selected.

Once DST upgrade completes, Do the below post checks.

1. SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/postupgrade_fixups.sql;

2. Change the ORACLE_HOME to 12c in listener.ora file.

3. Uncomment the crontab

4.Enable the jobs in dba_scheduler_jobs which we disabled before the upgrade.

5. run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.

12C Database upgrade completed.

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

Moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBE metadata procedure is used for the same purpose.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 5167382528 bytes

Fixed Size 2935128 bytes

Variable Size 1056966312 bytes

Database Buffers 4093640704 bytes

Redo Buffers 13840384 bytes

Database mounted.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME OPEN_MODE

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

UPGRD READ ONLY

2. Connect to the Non-CDB Database and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database

SQL> BEGIN

DBMS_PDB.DESCRIBE(

pdb_descr_file => '/u02/backup/ora12c.xml');

END;

PL/SQL procedure successfully completed.

3. Shutdown the Non CDB Database, and login to CDB Database as SYS user

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

If the upgraded database is the only database on the current system, then you need to create a Container Database (CDB) first

Once the CDB is created you can proceed with the next step.

• Create a container database

DBCA SILENT installation:

TESCOBANK-> /u00/app/oracle/product/12.1.0/database/bin/dbca -silent \

> -createDatabase \

> -templateName General_Purpose.dbc \

> -gdbName CONUPGRD \

> -sid CONUPGRD1 \

> -createAsContainerDatabase true \

> -numberOfPdbs 2 \

> -pdbName pdbupgrd \

> -pdbadminUsername pdba \

> -pdbadminPassword oracle12c \

> -SysPassword oracle12c \

> -SystemPassword oracle12c \

> -emConfiguration NONE \

> -datafileDestination /u02/oradata \

> -storageType FS \

> -characterSet AL32UTF8 \

> -memoryPercentage 40

Copying database files

1% complete

2% complete

27% complete

Creating and starting Oracle instance

29% complete

32% complete

33% complete

34% complete

38% complete

42% complete

43% complete

45% complete

Completing Database Creation

48% complete

51% complete

53% complete

62% complete

70% complete

72% complete

Creating Pluggable Databases

78% complete

83% complete

100% complete

Look at the log file "/u00/app/oracle/cfgtoollogs/dbca/CONUPGRD/CONUPGRD.log" for further details.

TESCOBANK-> vi "/u00/app/oracle/cfgtoollogs/dbca/CONUPGRD/CONUPGRD.log"

TESCOBANK-> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 20 11:42:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CONUPGRD READ WRITE

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBUPGRD1 READ WRITE NO

4 PDBUPGRD2 READ WRITE NO

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBUPGRD1 READ WRITE NO

4 PDBUPGRD2 READ WRITE NO

SQL> select pdb_id, con_uid, pdb_name from dba_pdbs order by 1;

PDB_ID CON_UID

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

PDB_NAME

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

2 2721201891

PDB$SEED

3 105835700

PDBUPGRD1

4 2418664579

PDBUPGRD2

SQL> set pages 1000 lines 1000

SQL> select name from v$datafile;

CON_ID NAME

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

1 /u02/oradata/CONUPGRD/users01.dbf

1 /u02/oradata/CONUPGRD/sysaux01.dbf

1 /u02/oradata/CONUPGRD/undotbs01.dbf

1 /u02/oradata/CONUPGRD/system01.dbf

2 /u02/oradata/CONUPGRD/pdbseed/system01.dbf

2 /u02/oradata/CONUPGRD/pdbseed/sysaux01.dbf

3 /u02/oradata/CONUPGRD/pdbupgrd1/system01.dbf

3 /u02/oradata/CONUPGRD/pdbupgrd1/sysaux01.dbf

3 /u02/oradata/CONUPGRD/pdbupgrd1/pdbupgrd1_users01.dbf

4 /u02/oradata/CONUPGRD/pdbupgrd2/system01.dbf

4 /u02/oradata/CONUPGRD/pdbupgrd2/sysaux01.dbf

4 /u02/oradata/CONUPGRD/pdbupgrd2/pdbupgrd2_users01.dbf

On CDB , Create the PDB Database by mapping the XML file that Is generated in step 2

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME OPEN_MODE

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

CONUPGRD READ WRITE

SQL> CREATE PLUGGABLE DATABASE ORA12C USING '/u02/backup/ora12c.xml'

COPY

FILE_NAME_CONVERT = ('/u00/app/oracle/oradata/UPGRD/','/u02/oradata/datafile/CONUPGRD'); 2 3

Pluggable database created.

From the parameter FILE_NAME_CONVERT, "/u00/app/oracle/oradata/UPGRD/" is the source Database files location (Non CDB), and "/u02/oradata/datafile/CONUPGRD" is the target database files location (CDB/PDB).

When we execute the above command, Oracle copies files from Non CDB's location to CDBs and plugs the database into Container, and creates it.

Following query gives the status of the new PDB.

5. Connect to the new PDB that has been created on CDB Database as SYS, and execute the "noncdb_to_pdb.sql" script.

SQL> sho pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDBUPGRD1 READ WRITE NO

4 PDBUPGRD2 READ WRITE NO

5 ORA12C MOUNTED

SQL> ALTER SESSION SET CONTAINER=ORA12C;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> ALTER PLUGGABLE DATABASE ORA12C OPEN;

Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE

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

ORA12C READ WRITE

1 row selected.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

CONUPGRD READ WRITE

Vishwanath Nayak

Thursday, October 26, 2017

NoSQL Data Modeling Techniques

Upgrade Database from 11g to 12c manually and then moving a Non Container Database (Non CDB) to Container Database (CDB as PDB):