透過遷移資料庫檔案方式來實現10gR2升級11gR2
Oracle 10gR2遷移資料庫檔案(引數檔案、控制檔案、資料檔案、聯機日誌檔案、歸檔日誌檔案)至Oracle 11gR2，啟到mount狀態，startup upgrade模式開啟資料庫，刷資料字典即升級catupgra.sql
主端：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>
備端：
[oracle@bei ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.3.0 Production
[oracle@bei admin]$ ll|grep utlu112i.sql
-rw-r--r--. 1 oracle oinstall 213918 Jul 13 2011 utlu112i.sql
[oracle@bei admin]$ scp utlu112i.sql oracle@192.168.186.123:/home/oracle
oracle@192.168.186.123's password:
utlu112i.sql 100% 209KB 208.9KB/s 00:00
主端：
[oracle@zhu ~]$ ls -rtl
total 216
-rw-r--r-- 1 oracle oinstall 213918 Aug 9 21:40 utlu112i.sql
[oracle@zhu ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 9 21:44:07 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2015 21:44:14
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: NODE
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 548 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 228 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
根據utlu112i.sql反饋的結果，進行配置
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
[oracle@zhu ~]$ ls -rlt
total 220
-rw-r--r-- 1 oracle oinstall 213918 Aug 9 21:40 utlu112i.sql
-rw-r--r-- 1 oracle oinstall 931 Aug 9 21:47 initorcl.ora
[oracle@zhu ~]$ scp initorcl.ora oracle@192.168.186.88:/u01/app/base/product/11.2.0/db_1/dbs/
oracle@192.168.186.88's password:
initorcl.ora 100% 931 0.9KB/s 00:00
[oracle@bei dbs]$ ls -rtl
total 8
-rw-r-----. 1 oracle oinstall 1536 Aug 7 14:27 orapworcl
-rw-r--r--. 1 oracle oinstall 931 Aug 9 14:45 initorcl.ora
修改備庫初始化引數檔案
[oracle@bei dbs]$ more initorcl.ora
*.audit_file_dest='/u01/app/base/admin/node/adump'
*.diagnostic_dest='/u01/app/base'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/standby/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='node'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nodeXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oradata/archivelog'
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=629145600
*.sga_target=629145600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@zhu ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 9 21:58:34 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system archive log current;
System altered.
SQL> alter database begin backup;
Database altered.
[oracle@zhu node]$ ls -rtl
total 1043044
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:32 redo03.log
-rw-r----- 1 oracle oinstall 20979712 Aug 9 21:45 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:59 redo01.log
-rw-r----- 1 oracle oinstall 5251072 Aug 9 21:59 users01.dbf
-rw-r----- 1 oracle oinstall 335552512 Aug 9 21:59 undotbs01.dbf
-rw-r----- 1 oracle oinstall 398467072 Aug 9 21:59 system01.dbf
-rw-r----- 1 oracle oinstall 146808832 Aug 9 21:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:59 redo02.log
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control01.ctl
[oracle@zhu node]$ scp * oracle@192.168.186.88:/oradata/standby
oracle@192.168.186.88's password:
control01.ctl 100% 6896KB 6.7MB/s 00:00
control02.ctl 100% 6896KB 6.7MB/s 00:00
control03.ctl 100% 6896KB 6.7MB/s 00:00
redo01.log 100% 50MB 16.7MB/s 00:03
redo02.log 100% 50MB 10.0MB/s 00:05
redo03.log 100% 50MB 10.0MB/s 00:05
sysaux01.dbf 100% 140MB 10.8MB/s 00:13
system01.dbf 100% 380MB 11.9MB/s 00:32
temp01.dbf 100% 20MB 20.0MB/s 00:01
undotbs01.dbf 100% 320MB 16.8MB/s 00:19
users01.dbf 100% 5128KB 5.0MB/s 00:01
SQL> alter system archive log current;
System altered.
SQL> alter database end backup;
Database altered.
[oracle@zhu archivelog]$ ls -rtl
total 80316
-rw-r----- 1 oracle oinstall 27262464 Jul 30 00:23 1_31_886375689.dbf
-rw-r----- 1 oracle oinstall 1024 Jul 30 00:23 1_32_886375689.dbf
-rw-r----- 1 oracle oinstall 2048 Jul 30 00:23 1_33_886375689.dbf
-rw-r----- 1 oracle oinstall 6198272 Jul 31 20:15 1_34_886375689.dbf
-rw-r----- 1 oracle oinstall 9528832 Jul 31 23:00 1_22_886537215.dbf
-rw-r----- 1 oracle oinstall 1504768 Aug 7 21:20 1_23_886537215.dbf
-rw-r----- 1 oracle oinstall 20880384 Aug 7 22:55 1_24_886537215.dbf
-rw-r----- 1 oracle oinstall 1700864 Aug 7 23:28 1_25_886537215.dbf
-rw-r----- 1 oracle oinstall 34304 Aug 7 23:54 1_26_886537215.dbf
-rw-r----- 1 oracle oinstall 20480 Aug 7 23:55 1_27_886537215.dbf
-rw-r----- 1 oracle oinstall 827392 Aug 8 00:05 1_28_886537215.dbf
-rw-r----- 1 oracle oinstall 21504 Aug 8 00:08 1_29_886537215.dbf
-rw-r----- 1 oracle oinstall 26624 Aug 9 21:32 1_30_886537215.dbf
-rw-r----- 1 oracle oinstall 18986496 Aug 9 21:59 1_31_886537215.dbf
-rw-r----- 1 oracle oinstall 1298944 Aug 9 22:03 1_32_886537215.dbf
[oracle@zhu archivelog]$ scp * oracle@192.168.186.88:/archive/standby
oracle@192.168.186.88's password:
1_22_886537215.dbf 100% 9306KB 9.1MB/s 00:00
1_23_886537215.dbf 100% 1470KB 1.4MB/s 00:00
1_24_886537215.dbf 100% 20MB 19.9MB/s 00:01
1_25_886537215.dbf 100% 1661KB 1.6MB/s 00:00
1_26_886537215.dbf 100% 34KB 33.5KB/s 00:00
1_27_886537215.dbf 100% 20KB 20.0KB/s 00:00
1_28_886537215.dbf 100% 808KB 808.0KB/s 00:00
1_29_886537215.dbf 100% 21KB 21.0KB/s 00:00
1_30_886537215.dbf 100% 26KB 26.0KB/s 00:00
1_31_886375689.dbf 100% 26MB 26.0MB/s 00:01
1_31_886537215.dbf 100% 18MB 18.1MB/s 00:00
1_32_886375689.dbf 100% 1024 1.0KB/s 00:00
1_32_886537215.dbf 100% 1269KB 1.2MB/s 00:00
1_33_886375689.dbf 100% 2048 2.0KB/s 00:00
1_34_886375689.dbf 100% 6053KB 5.9MB/s 00:00
[oracle@bei dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 9 15:09:34 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SQL>
SQL> alter database rename file '/oradata/node/system01.dbf' to '/oradata/standby/system01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/undotbs01.dbf' to '/oradata/standby/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/sysaux01.dbf' to '/oradata/standby/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/users01.dbf' to '/oradata/standby/users01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/redo01.log' to '/oradata/standby/redo01.log';
Database altered.
SQL> alter database rename file '/oradata/node/redo02.log' to '/oradata/standby/redo02.log';
Database altered.
SQL> alter database rename file '/oradata/node/redo03.log' to '/oradata/standby/redo03.log';
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open upgrade;
Database altered.
SQL> create temporary tablespace temp1 tempfile '/oradata/standby/temp1.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace temp including contents and datafiles cascade constraints;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile '/oradata/standby/temp01.dbf' size 1G;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/archivelog
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oradata/archivelog
Oldest online log sequence 31
Current log sequence 33
SQL> alter database open upgrade;
Database altered.
如果在升級時啟用歸檔，則會產生大量歸檔日誌，建議在升級時禁用歸檔
SQL> @?/rdbms/admin/catupgrd.sql #upgrade模式
[oracle@bei admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 9 21:01:49 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/catuppst.sql #非upgrade模式
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2015-08-09 21:02:06
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
Move BL Data "SYS"."WRH$_FILESTATXS" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SQLSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYSTEM_EVENT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_WAITSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_CHILDREN" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_PARENT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_MISSES_SUMMARY" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_DB_CACHE_ADVICE" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_ROWCACHE_SUMMARY" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SGASTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYSSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_PARAMETER" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SEG_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_DLM_MISC" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SERVICE_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_TABLESPACE_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_OSSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYS_TIME_MODEL" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SERVICE_WAIT_CLASS" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_INST_CACHE_TRANSFER" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_ACTIVE_SESSION_HISTORY" (0 rows in 0 seconds)
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
Drop Renamed Baseline Table SYS."WRH$_FILESTATXS_BR"
Drop Renamed Baseline Table SYS."WRH$_SQLSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYSTEM_EVENT_BR"
Drop Renamed Baseline Table SYS."WRH$_WAITSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_CHILDREN_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_PARENT_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_MISSES_SUMMARY_BR"
Drop Renamed Baseline Table SYS."WRH$_DB_CACHE_ADVICE_BR"
Drop Renamed Baseline Table SYS."WRH$_ROWCACHE_SUMMARY_BR"
Drop Renamed Baseline Table SYS."WRH$_SGASTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYSSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_PARAMETER_BR"
Drop Renamed Baseline Table SYS."WRH$_SEG_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_DLM_MISC_BR"
Drop Renamed Baseline Table SYS."WRH$_SERVICE_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_TABLESPACE_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_OSSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYS_TIME_MODEL_BR"
Drop Renamed Baseline Table SYS."WRH$_SERVICE_WAIT_CLASS_BR"
Drop Renamed Baseline Table SYS."WRH$_INST_CACHE_TRANSFER_BR"
Drop Renamed Baseline Table SYS."WRH$_ACTIVE_SESSION_HISTORY_BR"
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
5 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
5 rows updated.
Table dropped.
Commit complete.
4 rows updated.
Commit complete.
4 rows updated.
Commit complete.
4 rows updated.
Commit complete.
4 rows created.
Commit complete.
10 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2015-08-09 21:02:11
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/base/cfgtoollogs/catbundle/catbundle_PSU_NODE_GENERATE_2015Aug09_21_02_18.log
Apply script: /u01/app/base/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_NODE_APPLY.sql
Rollback script: /u01/app/base/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_NODE_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/base/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
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 '11.2.0.3',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/base/cfgtoollogs/catbundle/catbundle_PSU_NODE_APPLY_2015Aug09_21_02_20.log
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
4466
DOC> Use the following queries to track recompilation progress:
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
4466
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-08-09 21:04:21
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2015-08-09 21:13:39
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
SQL> col COMP_NAME for a40
SQL> select comp_name,version, status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ----------------------
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
10 rows selected.
SQL>
SQL> select * from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_4.dat 4
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade; #uograde模式
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Failures:0
PL/SQL procedure successfully completed.
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL>
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 08-09-2015 21:50:23
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 00:10:40
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:07:06
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:00:35
Oracle XDK
. VALID 11.2.0.3.0 00:01:19
Oracle XML Database
. VALID 11.2.0.3.0 00:04:27
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:22
Oracle Multimedia
. VALID 11.2.0.3.0 00:07:03
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:11
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:10
Gathering Statistics
. 00:01:42
Total Upgrade Time: 00:33:42
PL/SQL procedure successfully completed.
SQL>
注意：
將10g Restore到11g的2個關鍵點：
1. 必須在10g庫上先執行@?/rdbms/admin/utlu112i.sql指令碼，然後在遷移，否則Restore之後的升級catupgrd.sql報如下錯誤：
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
2. 10g的版本必須大於10.2.0.2
主端：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>
備端：
[oracle@bei ~]$ sqlplus -v
SQL*Plus: Release 11.2.0.3.0 Production
[oracle@bei admin]$ ll|grep utlu112i.sql
-rw-r--r--. 1 oracle oinstall 213918 Jul 13 2011 utlu112i.sql
[oracle@bei admin]$ scp utlu112i.sql oracle@192.168.186.123:/home/oracle
oracle@192.168.186.123's password:
utlu112i.sql 100% 209KB 208.9KB/s 00:00
主端：
[oracle@zhu ~]$ ls -rtl
total 216
-rw-r--r-- 1 oracle oinstall 213918 Aug 9 21:40 utlu112i.sql
[oracle@zhu ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 9 21:44:07 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2015 21:44:14
Script Version: 11.2.0.3.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: NODE
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 548 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 228 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> PURGE DBA_RECYCLEBIN;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
SQL>
根據utlu112i.sql反饋的結果，進行配置
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
[oracle@zhu ~]$ ls -rlt
total 220
-rw-r--r-- 1 oracle oinstall 213918 Aug 9 21:40 utlu112i.sql
-rw-r--r-- 1 oracle oinstall 931 Aug 9 21:47 initorcl.ora
[oracle@zhu ~]$ scp initorcl.ora oracle@192.168.186.88:/u01/app/base/product/11.2.0/db_1/dbs/
oracle@192.168.186.88's password:
initorcl.ora 100% 931 0.9KB/s 00:00
[oracle@bei dbs]$ ls -rtl
total 8
-rw-r-----. 1 oracle oinstall 1536 Aug 7 14:27 orapworcl
-rw-r--r--. 1 oracle oinstall 931 Aug 9 14:45 initorcl.ora
修改備庫初始化引數檔案
[oracle@bei dbs]$ more initorcl.ora
*.audit_file_dest='/u01/app/base/admin/node/adump'
*.diagnostic_dest='/u01/app/base'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/standby/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='node'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nodeXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oradata/archivelog'
*.log_archive_dest_state_1='ENABLE'
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=629145600
*.sga_target=629145600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
[oracle@zhu ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Aug 9 21:58:34 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system archive log current;
System altered.
SQL> alter database begin backup;
Database altered.
[oracle@zhu node]$ ls -rtl
total 1043044
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:32 redo03.log
-rw-r----- 1 oracle oinstall 20979712 Aug 9 21:45 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:59 redo01.log
-rw-r----- 1 oracle oinstall 5251072 Aug 9 21:59 users01.dbf
-rw-r----- 1 oracle oinstall 335552512 Aug 9 21:59 undotbs01.dbf
-rw-r----- 1 oracle oinstall 398467072 Aug 9 21:59 system01.dbf
-rw-r----- 1 oracle oinstall 146808832 Aug 9 21:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 9 21:59 redo02.log
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 9 22:00 control01.ctl
[oracle@zhu node]$ scp * oracle@192.168.186.88:/oradata/standby
oracle@192.168.186.88's password:
control01.ctl 100% 6896KB 6.7MB/s 00:00
control02.ctl 100% 6896KB 6.7MB/s 00:00
control03.ctl 100% 6896KB 6.7MB/s 00:00
redo01.log 100% 50MB 16.7MB/s 00:03
redo02.log 100% 50MB 10.0MB/s 00:05
redo03.log 100% 50MB 10.0MB/s 00:05
sysaux01.dbf 100% 140MB 10.8MB/s 00:13
system01.dbf 100% 380MB 11.9MB/s 00:32
temp01.dbf 100% 20MB 20.0MB/s 00:01
undotbs01.dbf 100% 320MB 16.8MB/s 00:19
users01.dbf 100% 5128KB 5.0MB/s 00:01
SQL> alter system archive log current;
System altered.
SQL> alter database end backup;
Database altered.
[oracle@zhu archivelog]$ ls -rtl
total 80316
-rw-r----- 1 oracle oinstall 27262464 Jul 30 00:23 1_31_886375689.dbf
-rw-r----- 1 oracle oinstall 1024 Jul 30 00:23 1_32_886375689.dbf
-rw-r----- 1 oracle oinstall 2048 Jul 30 00:23 1_33_886375689.dbf
-rw-r----- 1 oracle oinstall 6198272 Jul 31 20:15 1_34_886375689.dbf
-rw-r----- 1 oracle oinstall 9528832 Jul 31 23:00 1_22_886537215.dbf
-rw-r----- 1 oracle oinstall 1504768 Aug 7 21:20 1_23_886537215.dbf
-rw-r----- 1 oracle oinstall 20880384 Aug 7 22:55 1_24_886537215.dbf
-rw-r----- 1 oracle oinstall 1700864 Aug 7 23:28 1_25_886537215.dbf
-rw-r----- 1 oracle oinstall 34304 Aug 7 23:54 1_26_886537215.dbf
-rw-r----- 1 oracle oinstall 20480 Aug 7 23:55 1_27_886537215.dbf
-rw-r----- 1 oracle oinstall 827392 Aug 8 00:05 1_28_886537215.dbf
-rw-r----- 1 oracle oinstall 21504 Aug 8 00:08 1_29_886537215.dbf
-rw-r----- 1 oracle oinstall 26624 Aug 9 21:32 1_30_886537215.dbf
-rw-r----- 1 oracle oinstall 18986496 Aug 9 21:59 1_31_886537215.dbf
-rw-r----- 1 oracle oinstall 1298944 Aug 9 22:03 1_32_886537215.dbf
[oracle@zhu archivelog]$ scp * oracle@192.168.186.88:/archive/standby
oracle@192.168.186.88's password:
1_22_886537215.dbf 100% 9306KB 9.1MB/s 00:00
1_23_886537215.dbf 100% 1470KB 1.4MB/s 00:00
1_24_886537215.dbf 100% 20MB 19.9MB/s 00:01
1_25_886537215.dbf 100% 1661KB 1.6MB/s 00:00
1_26_886537215.dbf 100% 34KB 33.5KB/s 00:00
1_27_886537215.dbf 100% 20KB 20.0KB/s 00:00
1_28_886537215.dbf 100% 808KB 808.0KB/s 00:00
1_29_886537215.dbf 100% 21KB 21.0KB/s 00:00
1_30_886537215.dbf 100% 26KB 26.0KB/s 00:00
1_31_886375689.dbf 100% 26MB 26.0MB/s 00:01
1_31_886537215.dbf 100% 18MB 18.1MB/s 00:00
1_32_886375689.dbf 100% 1024 1.0KB/s 00:00
1_32_886537215.dbf 100% 1269KB 1.2MB/s 00:00
1_33_886375689.dbf 100% 2048 2.0KB/s 00:00
1_34_886375689.dbf 100% 6053KB 5.9MB/s 00:00
[oracle@bei dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 9 15:09:34 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SQL>
SQL> alter database rename file '/oradata/node/system01.dbf' to '/oradata/standby/system01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/undotbs01.dbf' to '/oradata/standby/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/sysaux01.dbf' to '/oradata/standby/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/users01.dbf' to '/oradata/standby/users01.dbf';
Database altered.
SQL> alter database rename file '/oradata/node/redo01.log' to '/oradata/standby/redo01.log';
Database altered.
SQL> alter database rename file '/oradata/node/redo02.log' to '/oradata/standby/redo02.log';
Database altered.
SQL> alter database rename file '/oradata/node/redo03.log' to '/oradata/standby/redo03.log';
Database altered.
SQL> recover database;
Media recovery complete.
SQL> alter database open upgrade;
Database altered.
SQL> create temporary tablespace temp1 tempfile '/oradata/standby/temp1.dbf' size 10M;
Tablespace created.
SQL> alter database default temporary tablespace temp1;
Database altered.
SQL> drop tablespace temp including contents and datafiles cascade constraints;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile '/oradata/standby/temp01.dbf' size 1G;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;
Tablespace dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/archivelog
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oradata/archivelog
Oldest online log sequence 31
Current log sequence 33
SQL> alter database open upgrade;
Database altered.
如果在升級時啟用歸檔，則會產生大量歸檔日誌，建議在升級時禁用歸檔
SQL> @?/rdbms/admin/catupgrd.sql #upgrade模式
[oracle@bei admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Aug 9 21:01:49 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/catuppst.sql #非upgrade模式
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2015-08-09 21:02:06
PL/SQL procedure successfully completed.
This script will migrate the Baseline data on a pre-11g database
to the 11g database.
Move BL Data "SYS"."WRH$_FILESTATXS" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SQLSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYSTEM_EVENT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_WAITSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_CHILDREN" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_PARENT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_LATCH_MISSES_SUMMARY" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_DB_CACHE_ADVICE" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_ROWCACHE_SUMMARY" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SGASTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYSSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_PARAMETER" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SEG_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_DLM_MISC" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SERVICE_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_TABLESPACE_STAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_OSSTAT" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SYS_TIME_MODEL" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_SERVICE_WAIT_CLASS" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_INST_CACHE_TRANSFER" (0 rows in 0 seconds)
Move BL Data "SYS"."WRH$_ACTIVE_SESSION_HISTORY" (0 rows in 0 seconds)
... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
Drop Renamed Baseline Table SYS."WRH$_FILESTATXS_BR"
Drop Renamed Baseline Table SYS."WRH$_SQLSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYSTEM_EVENT_BR"
Drop Renamed Baseline Table SYS."WRH$_WAITSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_CHILDREN_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_PARENT_BR"
Drop Renamed Baseline Table SYS."WRH$_LATCH_MISSES_SUMMARY_BR"
Drop Renamed Baseline Table SYS."WRH$_DB_CACHE_ADVICE_BR"
Drop Renamed Baseline Table SYS."WRH$_ROWCACHE_SUMMARY_BR"
Drop Renamed Baseline Table SYS."WRH$_SGASTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYSSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_PARAMETER_BR"
Drop Renamed Baseline Table SYS."WRH$_SEG_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_DLM_MISC_BR"
Drop Renamed Baseline Table SYS."WRH$_SERVICE_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_TABLESPACE_STAT_BR"
Drop Renamed Baseline Table SYS."WRH$_OSSTAT_BR"
Drop Renamed Baseline Table SYS."WRH$_SYS_TIME_MODEL_BR"
Drop Renamed Baseline Table SYS."WRH$_SERVICE_WAIT_CLASS_BR"
Drop Renamed Baseline Table SYS."WRH$_INST_CACHE_TRANSFER_BR"
Drop Renamed Baseline Table SYS."WRH$_ACTIVE_SESSION_HISTORY_BR"
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
PL/SQL procedure successfully completed.
5 rows created.
Commit complete.
Table created.
2 rows created.
1 row updated.
2 rows updated.
5 rows updated.
Table dropped.
Commit complete.
4 rows updated.
Commit complete.
4 rows updated.
Commit complete.
4 rows updated.
Commit complete.
4 rows created.
Commit complete.
10 rows created.
Commit complete.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2015-08-09 21:02:11
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/base/cfgtoollogs/catbundle/catbundle_PSU_NODE_GENERATE_2015Aug09_21_02_18.log
Apply script: /u01/app/base/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_NODE_APPLY.sql
Rollback script: /u01/app/base/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_NODE_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/base/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
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 '11.2.0.3',
9 0,
10 'PSU',
11 'Patchset 11.2.0.2.0');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/base/cfgtoollogs/catbundle/catbundle_PSU_NODE_APPLY_2015Aug09_21_02_20.log
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
4466
DOC> Use the following queries to track recompilation progress:
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE statusIN (4, 5, 6);
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROMUTL_RECOMP_COMPILED;
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
4466
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-08-09 21:04:21
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2015-08-09 21:13:39
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL>
SQL> col COMP_NAME for a40
SQL> select comp_name,version, status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ ----------------------
Oracle Multimedia 11.2.0.3.0 VALID
Oracle XML Database 11.2.0.3.0 VALID
Oracle Expression Filter 11.2.0.3.0 VALID
Oracle Rules Manager 11.2.0.3.0 VALID
Oracle Workspace Manager 11.2.0.3.0 VALID
Oracle Database Catalog Views 11.2.0.3.0 VALID
Oracle Database Packages and Types 11.2.0.3.0 VALID
JServer JAVA Virtual Machine 11.2.0.3.0 VALID
Oracle XDK 11.2.0.3.0 VALID
Oracle Database Java Packages 11.2.0.3.0 VALID
10 rows selected.
SQL>
SQL> select * from v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_4.dat 4
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade; #uograde模式
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$affected_tables;
Table truncated.
SQL> TRUNCATE TABLE sys.dst$error_table;
Table truncated.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
no rows selected
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 184550744 bytes
Database Buffers 436207616 bytes
Redo Buffers 3338240 bytes
Database mounted.
Database opened.
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Failures:0
PL/SQL procedure successfully completed.
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION
-------------------- ----------
timezlrg_14.dat 14
SQL>
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 08-09-2015 21:50:23
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 00:10:40
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:07:06
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:00:35
Oracle XDK
. VALID 11.2.0.3.0 00:01:19
Oracle XML Database
. VALID 11.2.0.3.0 00:04:27
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:22
Oracle Multimedia
. VALID 11.2.0.3.0 00:07:03
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:11
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:10
Gathering Statistics
. 00:01:42
Total Upgrade Time: 00:33:42
PL/SQL procedure successfully completed.
SQL>
注意：
將10g Restore到11g的2個關鍵點：
1. 必須在10g庫上先執行@?/rdbms/admin/utlu112i.sql指令碼，然後在遷移，否則Restore之後的升級catupgrd.sql報如下錯誤：
SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
*
ERROR at line 1:
ORA-01722: invalid number
2. 10g的版本必須大於10.2.0.2
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30310891/viewspace-1766493/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle舊版本透過資料遷移方式升級到新版本Oracle
- 資料庫的升級及遷移資料庫
- 透過移動資料檔案來均衡檔案I/O
- 達夢資料庫資料檔案遷移過程資料庫
- 透過rman全庫備份遷移資料庫資料庫
- 資料庫檔案的遷移資料庫
- 遷移資料庫檔案到ASM資料庫ASM
- iOS CoreData (二) 版本升級和資料庫遷移iOS資料庫
- 使用資料庫冷備份方式進行資料庫遷移,資料庫檔案遷移到不同的目錄資料庫
- Mysql百萬級資料遷移，怎麼遷移？實戰過沒？MySql
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- cassandra百億級資料庫遷移實踐資料庫
- 資料檔案遷移
- dataguard備庫的資料檔案的遷移實戰
- 動手為王 | Oracle 資料庫跨版本升級遷移實踐Oracle資料庫
- 資料庫的建立、遷移、升級和流等方面資料庫
- 利用offline datafile檔案方式遷移資料
- 利用拷貝data目錄檔案的方式遷移mysql資料庫MySql資料庫
- 資料庫所有檔案丟失後透過RMAN實現恢復資料庫
- 使用檔案複製的方式進行資料庫版本升級資料庫
- 透過REMAP_SCHEMA引數來實現不同使用者之間的資料遷移REM
- 達夢資料庫系統表空間資料檔案遷移過程資料庫
- dataguard備庫的資料檔案的遷移
- 【資料遷移】RMAN遷移資料庫到ASM(二)切換資料檔案到ASM資料庫ASM
- Grafana的版本升級和資料遷移Grafana
- 資料檔案的遷移
- oracle 資料檔案遷移Oracle
- oracle資料檔案遷移Oracle
- 資料庫遷移的幾個方式資料庫
- 海量資料遷移之透過shell估算資料量
- 如何透過.dbf檔案還原資料庫資料庫
- Oracle_遷移資料檔案Oracle
- Android 資料庫綜述（一） 資料庫片的升級與資料的遷移操作Android資料庫
- 用begin backup的方式遷移資料庫資料庫
- iOS 資料庫升級資料遷移解決方案iOS資料庫
- 通過移動資料檔案來均衡檔案I/O
- 遷移資料庫的檔案到不同路徑(轉)資料庫
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM