2015年（9）
分类： Oracle
2015-02-12 10:05:01
DataGuard 11.2.0.2 upgrade 11.2.0.4
升級前關閉所有crotab 排程
PATH 11.2.0.2
1.收集統計信息減少升級時間
SQL> spool /u01/20150211_upgrade.log
SQL> set time on
17:11:56 SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;
2.重啟Primary、Standby修改SGA參數
vim initdg0.ora
###SGA
*.db_cache_size=256M
*.sga_max_size=1008M
*.shared_pool_size=472M
*.large_pool_size=128M
*.java_pool_size=160M
*.pga_aggregate_target=671088640
##*.memory_target=842006528
######
Standby
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora'
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
Primary
SQL> startup pfile='/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/initdg0.ora'
3.升級前相關檢查
Pimary
SQL> spool /u01/upgrade_info.log
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-11-2015 18:04:27
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: DG
--> version: 11.2.0.2.0
--> compatible: 11.2.0.0.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 917 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 642 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
.
**********************************************************************
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:
WARNING: --> "shared_pool_size" needs to be increased to at least 472 MB
.
**********************************************************************
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]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
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
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Sync standby database prior to upgrade.
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.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
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 removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name,description from SYS.V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
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.
**********************************************************************
修改tablespace大小，滿足升級所需
SQL> alter database datafile '/u01/app/oracle/oradata/dg/system01.dbf' resize 1024M;
SQL> alter database datafile '/u01/app/oracle/oradata/dg/sysaux01.dbf' resize 642M;
SQL> alter database datafile '/u01/app/oracle/oradata/dg/undotbs01.dbf' resize 400M;
SQL> alter database tempfile '/u01/app/oracle/oradata/dg/temp01.dbf' resize 60M;
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE UPPER(name) ='EVENT' AND isdefault='FALSE';
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
查看角色的連接權限
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');
檢查acl網絡應用程序包
SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
識別無效的對象
SQL> select owner,object_type, object_name from dba_objects where status='INVALID' order by object_name;
or
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utluiobj.sql
驗證物理視圖
SQL> SELECT * FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;
確認無需修復的文件
SQL> SELECT * FROM v$recover_file;
解決分佈式事務
SQL> SELECT * FROM dba_2pc_pending;
如果有記錄按如下作業步驟
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
檢查是否有備機依賴
SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
如有備機需確認兩台同步狀態是否ok
清空回收站
SQL> PURGE DBA_RECYCLEBIN;
檢查完畢再次關閉Primary、Standby并關閉監聽器
PATH 11.2.0.4
1.升級前環境配置
修改環境變量(Primary、Standby)
[oracle@DG01 dbs]$ vim /home/oracle/.bash_profile
RACLE_HOME=$ORACLE_BASE/product/11.2.0.2/dbhome_1; export ORACLE_HOME
to
RACLE_HOME=$ORACLE_BASE/product/11.2.0.4/dbhome_1; export ORACLE_HOME
[oracle@DG01 dbs]$ source /home/oracle/.bash_profile
[oracle@DG01 dbs]$ echo $ORACLE_HOME
建立新的監聽器(圖形界面netca新增)
copy Path11.2.0.2 init、密碼文件、tnsname、至Path11.2.0.4目錄下
[oracle@DG01 dbs]$ cp initdg0.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
[oracle@DG01 dbs]$ cp orapwdg0 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/
[oracle@DG01 dbs]$ cp /u01/app/oracle/product/11.2.0.2/dbhome_1/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/
2.升級數據庫
Standby
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora'
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
Primary
SQL> spool /u01/upgrade.log
SQL> STARTUP UPGRADE pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora'
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catupgrd.sql
tail -f /u01/app/oracle/diag/rdbms/dg01/dg0/trace/alert_dg0.log
升級完畢后數據庫會關閉，查看記錄日誌中是否有錯誤。
[oracle@DG01 dbs]$ cat /u01/upgrade.log |grep ORA- |less
3.重啟啟動數據庫檢查
SQL> startup pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora'
更新工具狀態
SQL> spool upgrade_post.log
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 02-11-2015 19:52:39
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:13:37
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:06:18
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:39
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:24
OLAP Catalog
. VALID 11.2.0.4.0 00:00:56
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:33
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:03:56
Oracle XDK
. VALID 11.2.0.4.0 00:00:37
Oracle Text
. VALID 11.2.0.4.0 00:00:42
Oracle XML Database
. VALID 11.2.0.4.0 00:03:02
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:25
Oracle Multimedia
. VALID 11.2.0.4.0 00:03:05
Spatial
. VALID 11.2.0.4.0 00:03:09
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:13
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:10
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:37:53
PL/SQL procedure successfully completed.
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/catuppst.sql
重新編譯SP
SQL> spool /u01/upgrade_rp.log
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2015-02-11 19:57:00
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-02-11 19:59:35
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> 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> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utluiobj.sql
or
SQL> select owner,object_type, object_name from dba_objects where status='INVALID' order by object_name;
SQL> select COMP_NAME,VERSION, STATUS from dba_registry;
COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------
OWB 11.2.0.2.0 VALID
Oracle Applicat 3.2.1.00.12 VALID
ion Express
Oracle Enterpri 11.2.0.4.0 VALID
se Manager
OLAP Catalog 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimed 11.2.0.4.0 VALID
ia
COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------
Oracle XML Data 11.2.0.4.0 VALID
base
Oracle Text 11.2.0.4.0 VALID
Oracle Expressi 11.2.0.4.0 VALID
on Filter
Oracle Rules Ma 11.2.0.4.0 VALID
nager
COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------
Oracle Workspac 11.2.0.4.0 VALID
e Manager
Oracle Database 11.2.0.4.0 VALID
Catalog Views
Oracle Database 11.2.0.4.0 VALID
Packages and T
ypes
JServer JAVA Vi 11.2.0.4.0 VALID
COMP_NAME VERSION STATUS
--------------- -------------------- ------------------------------
rtual Machine
Oracle XDK 11.2.0.4.0 VALID
Oracle Database 11.2.0.4.0 VALID
Java Packages
OLAP Analytic W 11.2.0.4.0 VALID
orkspace
Oracle OLAP API 11.2.0.4.0 VALID
SQL> select comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified
COMP_NAME STATUS VERSION
---------- ------- -----------
Oracle Enterprise Manager VALID 11.2.0.4.0
Oracle Database Catalog Views VALID 11.2.0.4.0
Oracle Workspace Manager VALID 11.2.0.4.0
Oracle Database Packages and Types VALID 11.2.0.4.0
JServer JAVA VirtualMachine VALID 11.2.0.4.0
Oracle Database JavaPackages VALID 11.2.0.4.0
Oracle XDK VALID 11.2.0.4.0
Oracle Expression Filter VALID 11.2.0.4.0
Oracle Text VALID 11.2.0.4.0
Oracle XML Database VALID 11.2.0.4.0
Oracle Rules Manager VALID 11.2.0.4.0
Oracle Multimedia VALID 11.2.0.4.0
OLAP Analytic Workspace VALID 11.2.0.4.0
Oracle OLAP API VALID 11.2.0.4.0
OLAP Catalog VALID 11.2.0.4.0
Spatial VALID 11.2.0.4.0
Oracle Application Express VALID 3.2.1.00.1
OWB VALID 11.2.0.2.0
18 rows selected.
Standby
recover automatic standby database;(可能會使用到)
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initdg0.ora'
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select sequence#,applied from v$archived_log;
SQL> select comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified;
4.升級后
在負載穩定期間創建系統統計信息--否則，無法針對CBO使用適當的值
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start');
... -- some time delay while the database is under a typical workload execute
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');
SQL> select pname NAME, pval1 VALUE, pval2 INFO from aux_stats$;
NAME VALUE INFO
-------------------- ---------- ------------------------------
STATUS COMPLETED
DSTART 04-03-2009 12:30
DSTOP 05-03-2009 12:30
FLAGS 1
CPUSPEEDNW 1392.39
IOSEEKTIM 8.405
IOTFRSPEED 255945.605
其他參考
