Complete Manual Upgrade to Non-CDB Oracle Database 12c Release 2 (12.1) (Doc ID 1503653.1)
Step 1) Install 12.1.0.2 binaries
--> Create the new Oracle Home directory for 12.1.0.2 software
--> Install Oracle RDBMS software for 12.1.0.2 in a new Oracle Home
Step 2) Database full Backup
--> Take a backup of the database
Env Details
CURRENT ORACLE_HOME : /applications/oracle/11.2.0.3
NEW ORACLE_HOME :/applications/oracle/12.1.0.2
Query details
set pagesize500
col COMP_NAME for a40
set lines 200
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
dba_registry order by comp_name;
col OBJECT_NAME for a30
set lines 200
column object_name format a30
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';
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from
dba_objects where status='INVALID' order by owner,object_type;
Before starting the manual upgrade it is required change the preference for 'concurrent statistics gathering' on the current release if the current setting is not set to 'FALSE'
SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;
When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/
Step 3 : Check for the integrity of the source database.
Check for the integrity of the source database prior to starting the upgrade by downloading and running the dbupgdiag.sql script from the My Oracle Support article below:
Step 4 : Pre-Upgrade Steps
Run the new Pre-Upgrade Information Tool. For example, if you copied preupgrd.sql to the /admin directory of the source Oracle Home:
SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql
4.1 Deprecated CONNECT Role
To identify which users and roles in your database are granted the CONNECT role, use the following query:
SQL> SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
If users or roles require privileges other than CREATE SESSION, then grant the specific required privileges prior to upgrade. The upgrade scripts adjust the privileges for the Oracle-supplied users.
SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='CONNECT'
GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK
4.2 Dependencies on Network Utility Packages
col OWNER for a20
col NAME for a20
col REFERENCED_NAME for a20
col REFERENCED_LINK_NAME for a20
col REFERENCED_OWNER for a30
set lines 200 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');
4.3 Database Links with Passwords from Earlier Releases
SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
4.4 TIMESTAMP WITH TIME ZONE Data Type
The default time zone file shipped with the Oracle 12c Release 1 is version 18.
To see if there is any need for the DST update during the upgrade please check the following reference one :
Note 1665676.1 Actions For DST Updates When Upgrading To Or Applying The 12.1.0.2 Patchset
or
Note 1522719.1 Actions For DST Updates When Upgrading To 12.1.0.1 Base Release
4.5 Optimizer Statistics
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
4.6 Verify That Materialized View Refreshes Have Completed Before Upgrading
SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
4.7 Ensure That No Files Need Media Recovery Before Upgrading
SQL> SELECT * FROM v$recover_file;
4.8 Ensure That No Files Are in Backup Mode Before Upgrading
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
4.9 Resolve Outstanding Distributed Transactions Before Upgrading
SQL> SELECT * FROM dba_2pc_pending;
4.10 Purge the Database Recycle Bin Before Upgrading :
SQL> PURGE DBA_RECYCLEBIN
4.11 Disable all batch and cron jobs
For jobs initiated by Oracle then packages DBMS_JOB, DBMS_SCHEDULER can be used.
For cron jobs (external jobs controlled at the OS level) then this is a task for your Unix administrator.
4.12 Verify SYS and SYSTEM Default tablespace
SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:
SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;
4.13 Check whether database has any externally authenticated SSL users
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';
4.14 Location of datafiles, redo logs and control files
SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;
4.15 Remove Enterprise Manager Database Control repository :
$emctl stop dbcontrol
SQL> @ ?/rdbms/admin/emremove.sql
4.16 Run olspreupgrade.sql :
If OLS(Lable Security) and/or DV ( Database Vault) was already in the database prior to the upgrade then execute the following steps on Source database prior to upgrade
Note : You will get the olspreupgrade.sql script in the Oracle 12c home .
Copy the olspreupgrde.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORACLE_HOME/rdbms/admin and then execute on the source database prior to upgrade.
SQL> @ ?/rdbms/admin/olspreupgrade.sql
- It prepares the move of AUD$ table from SYSTEM to SYS.
- It processes the audit records to minimize downtime.
- It moves records to an Interim temporary table.
4.17 Disable jobs
set pagesize 2000
set lines 2000
set long 99999
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs where STATE='SCHEDULED';
select 'exec DBMS_SCHEDULER.DISABLE '||'('||''''||owner||'.'||job_name||''''||');' from dba_scheduler_jobs where state='SCHEDULED';
4.18 Drop Oracle 12c Release 1 supplied users and roles ,if exist in the source database
There are new Oracle users and roles in Oracle 12.1. If they exist in the source database users or roles with the same names, then they must be dropped before upgrading the database.
Run the preupgrade tool to check for the existence of any users or roles in the source database that use the same names.
Note: If there is a pre-existing user in the database with same name as the 12.1 oracle-supplied users or roles, then move the data of that user to a different schema before dropping the pre-existing user.
Make sure to drop these pre-existing users and/or roles before doing the upgrade. Else, the upgrade will terminate will with "ORA-01722: invalid number" error.
4.19 Review and Remove any unnecessary hidden/underscore parameters
Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade
unless your application vendors and/or Oracle Support state differently.Changes will need to be made in the init.ora or spfile.
To view existing hidden parameters execute the following command while connected AS SYSDBA:
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
4.20 Check the XDB ACLs has start_date and end_date ACE attributes
Before upgrading the database to 12c, please run the below query as SYS:
SQL> select aclid, start_date, end_date from xds_ace where start_date is not null;
If the query returns any row, then please follow Note 1958876.1 Upgrade to 12.1 fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION" to avoid failure in XDB's upgrade
4.21 Check the Mitigation patch has been applied on the source Oracle home ,it disables the Java development in the Database which cause error during upgrade
NOTE : This step is ONLY applicable if you have applied Mitigation Patch on the source database
Please "enable" the Java development in source database .
Connect to the database as a SYSDBA user
SQL> exec dbms_java_dev.enable;
Step 5 : Requirements and recommendations for target database
Copy following configuration files from the $ORACLE_HOME of the database being upgraded to the new Oracle Home for Oracle Database 12c
- Parameter file (spfile or pfile)
- Password file (orapwsid)
Remove or comment out obsolete and deprecated initialization parameters.
Comment out obsoleted parameters and change all deprecated parameters (Deprecated and Desupported Parameters).
The SEC_CASE_SENSITIVE_LOGON parameter is deprecated in 12.1, Please refer the Behaviour Change for complete information
The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.
Step 6: Stop the listener for the database.
lsnrctl stop
Step 7: Stop other executables such as dbconsole, isqlplus, etc.
$ emctl stop dbconsole
$ isqlplusctl stop
Step 8: Shutdown the database.
$ sqlplus "/as sysdba"
SQL> shutdown immediate;
Step 9: Make sure the following environment variables point to the Oracle 12c Release 1 (12.1)
directories
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH and SHLIB_PATH
$ export ORACLE_HOME=/applications/oracle/12.1.0.2
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=/applications/oracle
Step 10: Update the oratab entry to set the new ORACLE_HOME pointing to ORCL and disable automatic startup.
Sample : cat /etc/oratab
#P01RMS01:/applications/oracle/12.1.0.2:N
Step 11 : Upgrading Database to 12cR1
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 12cR1 Oracle Home.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE pfile='/home/oracle/P01RMS01_upgrade/initP01RMS01.ora';
SQL> exit
11.1 Run -catupgrd.sql
Run the catctl.pl script from the new Oracle home.
In this release, the new Upgrade Utility, catctl.pl, replaces catupgrd.sql.
To run catctl.pl on Linux:
Example: Where parallelism is 6 ( n=6)
$] cd $ORACLE_HOME/rdbms/admin
$] $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -l $ORACLE_HOME/diagnostics catupgrd.sql-----after this DB will down
11.2 Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.
$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu121s.sql
11.3 Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
11.4 Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
11.5 Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
Step 12: Post Upgrade Steps
12.1 Environment Variables and oratab file
Make sure the following environment variables point to the Oracle 12c Release 1 (12.1) directories
- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH and SHLIB_PATH
Ensure that your oratab file and any client scripts that set the value of ORACLE_HOME point to the new Oracle home that is created for the new Oracle Database 12c release,
12.2 Initialization parameter file
Edit init.ora
If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
Migrate your initialization parameter file to a server parameter file.
Create a server parameter file with an initialization parameter file.
SQL> create spfile from pfile
12.3 Password File
a) If the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to either exclusive or shared, create a password file with ORAPWD.
b) Refer the following document to avoid ORA-28017 during post upgrade.
ORA-28017: The password file is in the legacy format (Doc ID 2112456.1)
12.4 COMPATIBLE Initialization Parameter
The COMPATIBLE initialization parameter controls the compatibility level of your database.
SQL> SHUTDOWN IMMEDIATE
b. Edit the initialization parameter file to set or change the value of the COMPATIBLE initialization parameter.
For example, to set the COMPATIBLE initialization parameter to for Oracle Database release 12.1, enter the following in the initialization parameter file:
COMPATIBLE = 12.1.0
c. Start the instance using STARTUP.
12.5 Change passwords for Oracle-Supplied Accounts.
You can view the status of all accounts by issuing the following SQL statement:
SQL> SELECT username, account_status FROM dba_users ORDER BY username;
To lock and expire passwords, issue the following SQL statement:
SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
12.6 Upgrade the Recovery Catalog After Upgrading Oracle Database
You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command
12.7 Upgrade the Time Zone File Version After Upgrading Oracle Database
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)
12.8 Upgrade Statistics Tables Created by the DBMS_STATS Package
If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');
In the example, 'SYS' is the owner of the statistics table and 'dictstattab' is the name of the statistics table.
Execute this procedure for each statistics table.
12.9 Upgrade Externally Authenticated SSL Users
If you are upgrading from 10.2.0.x (or higher), then you are not required to run this command.
12.10 Install Oracle Text Supplied Knowledge Bases After Upgrading Oracle Database
12.11 Update Your Oracle Application Express Configuration
12.12 Configure Fine-Grained Access to External Network Services
12.13 Enable Oracle Database Vault
12.14 Identify Invalid Objects With the utluiobj Script
12.15 Enable all batch and cron jobs diable at step 4.14
No comments:
Post a Comment