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
----------
14
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.
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
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
----------
18
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.DESCRIBEmetadata 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
Copyright (c) 1982, 2014, Oracle. All rights reserved.
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
Ref :http://nayakvishwanath.blogspot.com/
for my reference
for my reference
No comments:
Post a Comment