Aggiornare database Release 11gR2 a Release 12c

Aggiornare database Release 11gR2 a Release 12c
Questo articolo mostra la procedura manuale di upgrade da Oracle 11.2.0.3 a Oracle 12c
Iniziamo da 11.2.0.3
SQL> select instance_name,version,statup_time,status from v$instance;
INSTANCE_NAME VERSION STARTUP_T STATUS
————- —————————- ———-
DB11 11.2.0.3.0 28-JAN-14 OPEN
Qui sotto le versioni di Oracle minime da cui si può effettuare l’upgrade diretto a Oracle 12c Release 1 (12.1)
Source Database Target Database
10.2.0.5 12.1.x
11.1.0.7 12.1.x
11.2.0.2 or higher 12.1.x
Le seguenti versioni, invece, necessitano di un upgrade indiretto:
Source Database Intermediate Upgrade Path for Target Database Target Database
11.2.0.1 —-> 11.2.0.2 or higher —-> 12.1.x
11.1.0.6 —-> 11.1.0.7 or 11.2.0.2 or higher —-> 12.1.x
10.2.0.4(or earlier)—-> 10.2.0.5 or later direct upgrade version —-> 12.1.x
10.1.0.5(or earlier)—-> 10.2.0.5 or later direct upgrade version —-> 12.1.x
9.2.0.8 (or earlier)—-> 9.2.0.8 –> 11.2.0.2 or higher —-> 12.1.x
• Eseguire lo script di Pre-upgrade per acquisire informazioni sullo stato del database.
SQL> @preupgrd.sql
Loading Pre-Upgrade Package…
WARNING: Failed to open preupgrade.log for write access
script will generate terminal output only
WARNING: Failed to open preupgrade_fixups.sql for write access
script will not generate fixup scripts.
Executing Pre-Upgrade Checks…
Oracle Database Pre-Upgrade Information Tool 01-28-2014 15:56:49
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
Database Name: DB11
Version: 11.2.0.3.0
Compatible: 11.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[Renamed Parameters]
“audit_trail” old value was “DB_EXTENDED”;
–> new name is “audit_trail”, new value is “DB,EXTENDED”
[Changes required in Oracle Database init.ora or spfile]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]
**********************************************************************
[Component List]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
ERROR: –> SYSTEM tablespace is not large enough for the upgrade.
currently allocated size: 512 MB
minimum required size: 587 MB
increase current size by: 75 MB
tablespace is NOT AUTOEXTEND ENABLED.
–> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 500 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
–> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
[make adjustments in the current environment]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: –> Process Count may be too low
Database has a maximum process count of 50 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.
INFORMATION: –> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.3.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
**********************************************************************
[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 ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
1 ERROR exist that must be addressed prior to performing your upgrade.
1 WARNING that Oracle suggests are addressed to improve database performance.
1 INFORMATIONAL message that should be reviewed prior to your upgrade.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 977512.1
***********************************
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
*** Scripts/Logs are not being Generated ***preupgrade.log
************************************************************
====>> USER ACTION REQUIRED <<==== ************************************************************ 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 ************************************************************ Proseguiamo, come indicato dallo script, con I successive passi di pre-upgrade: • Raccogliere le statistiche del dizionario SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
• Aumentare la dimensione della tablespace System
SQL> alter database datafile ‘/app/oradata/DB11/system/system01.dbf’ resize 1024M;
Database altered.
• Aumentare il numero di processi nell’init file (in questo caso il file spfile non è usato)
In questo caso non è configurata nessuna lista di controllo accessi, quindi non sono necessarie particolari azioni per l’aggiornamento di questa parte.
La Time zone del database sarà aggiornata al termine del processo di upgrade.
• Verifichiamo che non siano in corso recovery o backup prima di procedere con i prossimi step
SQL> SELECT * FROM v$recover_file;
no rows selected
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
no rows selected
SQL> SELECT * FROM dba_2pc_pending;
no rows selected
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in (‘SYS’,’SYSTEM’);
USERNAME DEFAULT_TABLESPACE
—————————— ——————————
SYS SYSTEM
SYSTEM SYSTEM
SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name=’AUD$’;
OWNER TABLESPACE_NAME
—————————— ——————————
SYS SYSTEM
SQL> SELECT name FROM v$controlfile;
NAME
——————————————————————————–
/app/oracle/admin/DB11/data/ctl/control01.ctl
/app/oracle/admin/DB11/data/ctl/control02.ctl
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
——————————————————————————–
/app/oradata/DB11/system/system01.dbf
/app/oradata/DB11/system/sysaux01.dbf
/app/oradata/DB11/undo/undo01.dbf
SQL> SELECT group#, member FROM v$logfile;
GROUP# MEMBER
———- —————————
1 /redo/redo_a/redo01a.log
1 /redo/redo_b/redo01b.log
2 /redo/redo_a/redo02a.log
2 /redo/redo_b/redo02b.log
SQL> select comp_id,version,status from dba_registry
COMP_ID VERSION STATUS
————————– ———-
CATALOG 11.2.0.3.0 VALID
CATPROC 11.2.0.3.0 VALID
• Nessun componente dell’enterprise manager è installato nel database, quindi non sono necessarie ulteriori azioni prima di procedere.
In Oracle 12c, Enterprise Manager Database Control è sostituito da Oracle Enterprise Manager Express, quindi il repository non è più necessario.
Rimuovere il repository di Enterprise Manager Database Control manualmente (lo script emremove.sql necessario si trova nella home di Oracle 12c).
Copiare lo script emremove.sql da 12c $ORACLE_HOME/rdbms/admin a $ORALCE_HOME/rdbms/admin e quindi eseguire prima di effettuare l’upgrade.
$emctl stop dbcontrol
SQL> @ ?/rdbms/admin/emremove.sql
Se il repository EM non viene rimosso preventivamente, verrà rimosso automaticamente durante la fase di post upgrade dallo script catuppst.sql.
• Copiare pfile/spfile e file delle password file nella nuova Oracle Home (12c)
• Spegnere il database.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
• Caricare le nuove variabili per puntare alla ORACLE_HOME 12c
[oracle@ciukinobox ~]$ . .bash_profile12
• Modificare /etc/oratab
[oracle@ciukinobox ~]$ more /etc/oratab
db11:/app/oracle/product/12.1.0:N
• Effettuare l’upgrade del database
[oracle@ciukinobox]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@ciukinobox admin]$ sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 28 17:14:51 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
• Il nuovo script catctl sostituisce catupgrd (script usato fino alla 11gR2) in modalitù parallela (6 è il numero di processi paralleli durante la fase di upgrade)
[oracle@ciukinobox admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql
Analyzing file catupgrd.sql
Log files in /app/oracle/product/12.1.0/diagnostics
14 scripts found in file catupgrd.sql
Next path: catalog.sql
32 scripts found in file catalog.sql
Next path: catproc.sql
37 scripts found in file catproc.sql
Next path: catptabs.sql
…..
……………..
…………………………….
[Phase 56] type is 1 with 1 Files
catshutdown.sql
Using 6 processes.
Serial Phase #: 0 Files: 1 Time: 91s
Serial Phase #: 1 Files: 3 Time: 56s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 18s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 24s
……….
………………………..
Grand Total Time: 1349s
• Startare il database aggiornato ed eseguire i passi post upgrade
SQL> startup
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL> @utlu121s.sql
.
Oracle Database 12.1 Post-Upgrade Status Tool 01-28-2014 17:46:46
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. UPGRADED 12.1.0.1.0 00:14:05
Oracle XML Database
. VALID 12.1.0.1.0 00:02:36
Final Actions
. 00:01:26
Total Upgrade Time: 00:18:10
PL/SQL procedure successfully completed.
SQL> @catuppst.sql
Session altered.
Session altered.
Session altered.
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP POSTUP_BGN 2014-01-28 17:47:27
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP CATREQ_BGN 2014-01-28 17:47:27
PL/SQL procedure successfully completed.
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
……….
……………….
……………………………
SQL> PROMPT Updating registry…
Updating registry…
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, ‘APPLY’,
7 SYS_CONTEXT(‘REGISTRY$CTX’,’NAMESPACE’),
8 ‘12.1.0.1’,
9 0,
10 ‘PSU’,
11 ‘Patchset 12.1.0.0.0′);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_DB11_APPLY_2014Jan28_17_47_29.log
Session altered.
Session altered.
• Ricompilare tutto il database
SQL> @?/rdbms/admin/utlrp
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2014-01-28 17:50:01
PL/SQL procedure successfully completed.
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_END 2014-01-28 17:50:03
OBJECTS WITH ERRORS
——————-
0
ERRORS DURING RECOMPILATION
—————————
0
SQL> select owner,object_name,object_type,status from dba_objects where status<>’VALID’;
no rows selected
• Controllare I componenti post upgrade tramite script dbupgdiag [sotto è riportato un estratto del logfile]
SQL> alter session set nls_language=’American’;
Session altered.
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle
28_Jan_2014_1753 .log
DB11_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 01-28-2014 17:53:11
===============
Hostname
===============
ciukinobox
===============
Database Name
===============
DB11
===============
Database Uptime
===============
17:45 28-JAN-14
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production 0
PL/SQL Release 12.1.0.1.0 – Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 – Production 0
NLSRTL Version 12.1.0.1.0 – Production 0
=============
Compatibility
=============
Compatibility is set as 11.2.0
================
Archive Log Mode
================
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /app/oracle/product/12.1.0/dbs/arch
Oldest online log sequence 23
Current log sequence 24
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table
===========================================
OWNER TABLESPACE_NAME
———— ——————————
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
================
Time Zone version
================
14
================
Local Listener
================
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
——- ———————————- ——— ————– ————– ————–
CATALOG Oracle Database Catalog Views VALID 12.1.0.1.0 11.2.0.3.0 11.2.0.3.0
CATPROC Oracle Database Packages and Types VALID 12.1.0.1.0 11.2.0.3.0 11.2.0.3.0
XDB Oracle XML Database VALID 12.1.0.1.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
——————————————————————
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
——————————————————————
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string ‘B023′ ==> Database was created as 32-bit
DOC> Result referencing the string ‘B047′ ==> Database was created as 64-bit
DOC> When String results in ‘B023′ and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
——– ———————————–
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ….
COUNT(1)
———-
4
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ….
Upload db_upg_diag_DB11_28_Jan_2014_0750.log from “/home/oracle” directory
• Aggiornare Timezone del database
SQL> SELECT version FROM v$timezone_file;
VERSION
———-
14
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Database version is 12.1.0.1 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: Doing checks for known issues …
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv18 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen …
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv18 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen …
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
RACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2290416 bytes
Variable Size 423628048 bytes
Database Buffers 104857600 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen …
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
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
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv18 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> SELECT version FROM v$timezone_file;
VERSION
———-
18
• A questo punto il nostro database è aggiornato alla nuova Release
SQL> select instance_name,version,status from v$instance;
INSTANCE_NAME VERSION STATUS
————— ————– —————–
DB11 12.1.0.1.0 OPEN

Potrebbero interessarti anche...