| Upgrade 10.2.0.5[64Bit] to 11gR2[64Bit] |
|
| Steps |
Details |
|
|
| Install Oracle 11gR2 in new Oracle Home |
make sure there are no Linking-Error
Install Latest Patchset available [11.2.0.2] |
| Take a Cold backup of source Database |
|
| Copy all database file to appropriate folder |
Create directories for the following:Datafile
Control file
Redolog
Diag files
Archive |
| Copy utlu 112i.sql from ORACLE_HOME/rdbms/admin and execute in source database. |
Check the spool file and examine the output of the upgrade$ sqlplus ‘/ as sysdba’
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>
Depending upon the spool file change initialization parameter, deprecated and obsolete parameters |
| Run Check the Integrity of the source databasedbupgdiag.sql |
If script reports any invalid objects, run utlrp.sql to validate invalid objectSQL> @ORACLE_HOME/rdbms/admin/utlrp.sql
After vaklidating invalid object Re-run dbupgdiag.sql and make sure everything is fine. |
| Deprecated CONNECT Role |
This query can be used to identify ehich users and roles are granted the CONNECT PrivlegeSELECT 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’); |
| In case database to be downgraded, create a script for DBLINK |
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#; |
| Check for TIMESTAMP with TIMEZONE datatype |
select TZ_VERSION from registry$database; |
| Check that National Characterset (NLS_NCHAR_CHARACTERSET) |
select value from NLS_DATABASE_PARAMETERS where parameter = ‘NLS_NCHAR_CHARACTERSET’;If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article |
| Check Schemas with Stale Statistics |
$ sqlplus ”/as sysdba”SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; |
| Check for logical corruption |
Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sqlSELECT ‘Analyze cluster “‘||cluster_name||’” validate structure cascade;’
FROM dba_clusters
WHERE owner=’SYS’
UNION
SELECT ‘Analyze table “‘||table_name||’” validate structure cascade;’
FROM dba_tables
WHERE owner=’SYS’
AND partitioned=’NO’
AND (iot_type=’IOT’ OR iot_type is NULL)
UNION
SELECT ‘Analyze table “‘||table_name||’” validate structure cascade into invalid_rows;’
FROM dba_tables
WHERE owner=’SYS’
AND partitioned=’YES’;spool off
This Will create ascript called analyze.sql.
Now execute this
$ sqlplus “/ as sysdba”
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql |
| Ensure that all snapshot refreshes are successfully completed, and that replication is stopped. |
SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times; |
| Ensure that no files need media recovery |
Query v$recover_file;SELECT * FROM v$recover_file;
It should return no rows |
| Ensure that no files are in backup mode |
SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;This should return no rows |
| Resolve outstanding distributed transactions prior to the upgrade. |
SQL> select * from dba_2pc_pending;incase it returns rows you should do the following:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry(”);
SQL> COMMIT; |
| To check if a standby database exists, issue the following query: |
SELECT SUBSTR(value,INSTR(value,’=',INSTR(UPPER(value),’SERVICE’))+1)
FROM v$parameter
WHERE name LIKE ‘log_archive_dest%’ AND UPPER(value) LIKE ‘SERVICE%’;If this query returns a row, then sync the standby database with the Primary database.
1. Make sure all the logs are transported to the standby server after a final log switch in the primary.
2. Start the recovery of the standby database with the NODELAY option. |
| Disable all batch and cron jobs. |
|
Ensure the users sys and system have ‘system’ as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited. |
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 to SYSTEM by using the below command.SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM; |
| Ensure that if the aud$ table exists that it is in the sys schema and in the system tablespace. |
SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name=’AUD$’;If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put is back of the SYSTEM tablespace and it should be owned by SYS . |
| Check whether database has any externally authenticated SSL users. |
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = ‘GLOBAL’;If Exist Do this steps after Upgrade1.Make sure all following Env Variables are pointing to 11g
- ORACLE_BASE
- ORACLE_HOME
- PATH
Check oratab file also.
2. vi /etc/oratab
SID:ORACLE_HOME:Y |
| Check the location of datafile, controlfile and redo logs. Also take a backup of all configuration files like listener.ora tnsnames.ora |
$ cp listener.ora /backup_ora
$ cp tnsnames.ora /backup_oraSQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;. |
| Stop the listener for the database, dbconsole and isqlplus |
$ lsnrctl stop
$ emctl stop dbconsole
$ isqlplusctl stop |
| Shutdown the database. |
$ sqlplus “/as sysdba”
SQL> shutdown immediate;Tak e a cold backup or RMAN backupConnect to RMAN:
rman “target / nocatalog”
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT ‘<db_backup_directory>%U’ TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO ‘<controlfile_backup_directory>’;
}
sql> Shutdown immediate |
| Edit init<sid>.ora file |
Comment out any obsoleted parameters – Preupgrade Check log -Change all deprecated parameters – Preupgrade Check log -Verify that the parameter DB_DOMAIN is set properly
The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST. |
| Check for enough freespace on archive log destination file systems. |
Note that if your database is in archivelog mode, then it is always desirable and advisable to upgrade the database in noarchivelog mode as that will reduce the time taken to upgrade the database. After the upgrade you can again put the database in the archivelog mode. |
| Upgrade Database |
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
Use startup eith upgrade option
SQL> startup UPGRADE |
| Run upgrade script |
SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off |
| Run post upgrade tool, which provides the summary of the upgrade |
$ORACLE_HOME/rdbms/admin/utlu112s.sql$ sqlplus “/as sysdba”
SQL> STARTUP
SQL> @utlu112s.sql
SQL> select comp_name, status, version from dba_registry; |
| Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions |
SQL> @catuppst.sqlrun utlrp to recompile any invalid objects
SQL> @utlrp.sql |
| Get the diagnostic information of the upgraded database |
Download and run dbupgdiag.sqlsql> @dbupgdiag.sql
If it shows any invalid object run utlrp.sql to validate any invalid onjects |
| Post upgrade steps |
Create the listener
$ netca
$ lsnrctl start |
| Upgrade the timezone file |
Please refer the article Note 977512.1 to upgrade the tiemzone |
| Upgrade Statistics Tables Created by the DBMS_STATS Package |
EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘SYS’,'dictstattab’); |
| Upgrade Externally Authenticated SSL Users |
ORACLE_HOME/rdbms/bin/extusrupgrade –dbconnectstring
<hostname:port_no:sid> –dbuser <db admin> –dbuserpassword
<password> -a |
| 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.SQL> create spfile from pfile; |
| Upgrade OCR key for the database |
use srvconfig
srvconfig -upgrade db_name -orahome pre_11g_oracle_homerun srvctl
from old oracle_home
% $ORACLE_HOME/bin/srvctl remove database -d db_nameFrom 11g Oracle_Home:
% $ORACLE_HOME/bin/srvctl add database -d db_name -o <location of 11g home>
% $ORACLE_HOME/bin/srvctl add instance -d db_name -i instance -n node |
| Shut down the database |
sqlplus ‘/ as sysdba’
shutdown |
| Startup database |
sqlplus ‘/as sysdba’
Startup |
|
|
|
|
|
