oracle資料庫升級11.2.0.3升級到11.2.0.4
#######################################
## 升級準備期 ##
#######################################
1. 介質準備，在所有伺服器上，準備如下介質
mkdir -p /u02/soft
unzip /u02/soft/p13390677_112040_Linux-x86-64_1of7 -d /u02/soft
unzip /u02/soft/p13390677_112040_Linux-x86-64_2of7 -d /u02/soft
unzip /u02/soft/p18139609_112040_Linux-x86-64.zip -d /u02/soft
-- 安裝完成DB軟體後，立即更新OPatch
unzip /u02/soft/p18139609_112040_Linux-x86-64.zip -d /u02/soft
1. 安裝11.2.0.4 DB軟體，安裝DB PSU 11.2.0.4.2,編輯新的profile檔案
-- 中轉機器：安裝軟體
. ~/bash_profile_11204
HOSTNAME=histdb
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/db/lib:/usr/lib
ORACLE_SID=billhist
PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.4/db/bin:/u01/app/11.2.0/grid/bin
GRID_HOME=/u01/app/11.2.0.4/grid
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db
2. 安裝11.2.0.4GRID軟體，安裝PSU 11.2.0.4.2，編輯新的profile檔案
-- 注意先不進行css配置，GI的PSU先不安裝
. ~/bash_profile_asm_11204
ORACLE_HOME=/u01/app/11.2.0.4
ORACLE_SID=+ASM
3. 複製11.2.0.3的listener.ora、tnsnames.ora到新的GRID HOME環境中的相關目錄，並修改listener.ora中ORACLE_HOME路徑
複製11.2.0.3 spfile、orapw、檔案ASM 至新的GRID HOME DB 至新的DB HOME
複製主庫crontab 指令碼至standby
/opt/monitor/rman/rmandw_level0_bak.sh
/opt/monitor/rman/rmandw_level1_bak.sh
/opt/monitor/rman/rmandw_level2_bak.sh
/home/oracle/sh/check_disk.sh
/home/oracle/sh/get_ORA_errors.sh
/opt/monitor/sql/backup_listenerlog.sh
/home/oracle/sh/gather_stats.sh
/home/oracle/sh/get_vmstat.sh
/home/oracle/sh/check_trace_dw.sh
/opt/monitor/sql/switchlog.sh
/opt/monitor/sql/gather_stats.sh
/opt/monitor/sql/gather_stats_empty.sh
/opt/monitor/sql/create_awrrpt.sh
/home/oracle/autoextend_ts/autoextend_ts.sh
/home/oracle/dailycheck/dailycheck_diskspace.sh
/home/oracle/dailycheck/dailycheck_alertlog.sh
/home/oracle/dailycheck/dailycheck_backup.sh
/home/oracle/dailycheck/dailycheck_scp.sh
/u01/report/sql/dwdailydatacheck.sh
/u01/report/sql/dwdailydatacheck.sh
/u01/report/sql/data_check.sh
/u01/report/sql/data_check.sh
/opt/monitor/sql/asm_dh_backup_total.sh
/home/oracle/scripts/clear_archi.sh
/root/sh/ntpd-deploy.sh
-- DB所需配置檔案：spfilebillhist.ora, orapwbillhist, listener.ora, tnsnames.ora, sqlnet.ora
-- ASM所需配置檔案：spfile+ASM.ora orapw+ASM, listener.ora, tnsnames.ora, sqlnet.ora
-- 所有伺服器，預先準備好 bash_profile_11204, bash_profile_asm_11204 等環境變數檔案
-- 所有伺服器，預先準備好 utlu112i.sql 等升級指令碼
4. 根據spfile生成pfile檔案
create pfile from spfile;
5. 引數修改：
background_dump_dest
6. 根據修改後的pfile建立新的spfile
CREATE spfile from pfile;
##########################################
## 升級視窗期前10小時內 ##
##########################################
1. 蒐集統計資訊，縮短升級時間，11.2.0.3環境執行
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
2. 清空回收站，11.2.0.3執行
PURGE DBA_RECYCLEBIN;
3. 檢查資料字典邏輯結構是否有損壞，11.2.0.3執行
-- 執行時間較長，但中途終止不會影響升級及後續執行
Set verify off space 0 line 120 heading off feedback off pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION ALL
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION ALL
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
exit
vi analyze.sql
drop table INVALID_ROWS;
@?/rdbms/admin/utlvalid.sql
@analyze.sql
select count(*) from INVALID_ROWS;
#########################################
## 升級視窗期前40分鐘 ##
#########################################
1. 執行升級檢查指令碼dbupgdiag.sql
sqlplus '/ as sysdba'
@/NEW_HOME_ORACLE/rdbms/admin/dbupgdiag.sql
2. 執行預升級指令碼utlu112i.sql，主庫執行
sqlplus '/ as sysdba'
@NEW_HOME_ORACLE/rdbms/admin/utlu112i.sql
3. 檢查自定義觸發器設定，如果有返回記錄，那麼需要在升級期間禁用
SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, STATUS
FROM DBA_TRIGGERS
WHERE TRIGGERING_EVENT='DDL'
AND OWNER NOT IN ('SYS', 'SYSTEM');
4. 檢查資料庫中是否有SQL Profile資訊，應該返回：no rows selected
SELECT SIGNATURE, CATEGORY, COUNT(*)
FROM DBA_SQL_PROFILES
GROUP BY SIGNATURE, CATEGORY
HAVING COUNT(*) > 1;
-- 檢查資料庫中的 outline 資訊
COL NAME FOR A26
COL OWNER FOR A8
COL CATEGORY FOR A8
SELECT NAME, OWNER, CATEGORY, USED, SQL_TEXT FROM DBA_OUTLINES;
-- 升級後遷移outline的命令（升級過程中不執行）：
-- SELECT NAME, OWNER, CATEGORY, USED, MIGRATED, SQL_TEXT FROM DBA_OUTLINES;
-- SET LINES 132 PAGES 1111 LONG 99999 SERVERVOUTPUT ON SIZE UNLIMITED
-- DECLARE
-- L_MIG_OUT CLOB;
-- BEGIN
-- L_MIG_OUT := DBMS_SPM.MIGRATE_STORED_OUTLINE('all');
-- DBMS_OUTPUT.PUT_LINE(L_MIG_OUT);
-- END;
-- /
5. 檢查資料庫中當前的DB Link資訊，儲存DB Link的建立指令碼，主備庫分別執行
set lines 1111 pages 0 trim on trims on
spo create_dblink.sql
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
SPO OFF
6. 檢查當前資料庫的國家字符集設定
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
7. 檢查當前資料檔案狀態和備份狀態，主備庫分別執行
-- 如果有RECOVER狀態的檔案，需要先進行恢復，正常應該返回：no rows selected
SELECT * FROM V$RECOVER_FILE;
SELECT * FROM V$BACKUP WHERE STATUS != 'NOT ACTIVE';
8. 記錄當前資料庫中各項檔案的位置，主備庫分別執行
SET LINES 1111 PAGES 0 TRIM ON TRIMS ON
COL NAME FOR A1000
SPO dbfile_name_info.txt
SELECT 'Datafile' TYPE, NAME FROM V$DBFILE
UNION ALL
SELECT 'Tempfile' TYPE, NAME FROM V$TEMPFILE
UNION ALL
SELECT 'Logfile' TYPE, MEMBER FROM V$LOGFILE
UNION ALL
SELECT 'Control' TYPE, NAME FROM V$CONTROLFILE;
SPO OFF
9.檢查當前資料庫的備庫資訊
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
show parameter log_arch
10.檢查SYS和SYSTEM使用者資訊，主備庫分別執行
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
-- 如果SYS和SYSTEM使用者預設表空間不是SYSTEM，將其修改為SYSTEM
-- ALTER user SYS default tablespace SYSTEM;
-- ALTER user SYSTEM default tablespace SYSTEM;
11.檢查審計結果表AUD$和FGA_LOG$，，主備庫分別執行
-- 正常應該返回：AUD$位於SYSTEM表空間，屬於SYS使用者，兩張審計表都沒有記錄
SELECT OWNER,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='AUD$';
SELECT COUNT(*) FROM AUD$;
SELECT COUNT(*) FROM FGA_LOG$;
-- 如果審計表中有記錄，可使用如下方式清除審計表中的記錄：
-- CREATE TABLE AUD_BAK20141209 AS SELECT * FROM AUD$;
-- CREATE TABLE FGA_LOG_BAK20141209 AS SELECT * FROM FGA_LOG$;
-- TRUNCATE TABLE AUD$;
-- TRUNCATE TABLE FGA_LOG$;
12.檢查資料庫中是否存在外部認證的使用者，正常返回：no rows selected
SELECT NAME FROM SYS.USER$ WHERE EXT_USERNAME IS NOT NULL AND PASSWORD = 'GLOBAL';
13.檢查是否有正在執行的Job，停止後續Job的執行
-- 停用Job
SHOW PARAMETER JOB_QUEUE_PROCESSES --記錄原始Job程式數 20
--ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT /*+ RULE */ * FROM DBA_JOBS_RUNNING;
-- 停用所有的crontab，關閉監控
crontab -l
crontab -e
--註釋掉預計在時間視窗內的
###backup dw
#0 2 1,7,14,21,28 * * /opt/monitor/rman/rmandw_level0_bak.sh >> /opt/monitor/log/rmandw_level0_bak.log
#0 2 2,3,5,6,8,9,10,12,13,15,16,17,19,20,22,23,24,26,27,29,30,31 * * /opt/monitor/rman/rmandw_level1_bak.sh >>/opt/monitor/log/rmandw_level1_bak.log
#0 2 4,11,18,25 * * /opt/monitor/rman/rmandw_level2_bak.sh >> /opt/monitor/log/rmandw_level2_bak.log
#*/10 * * * * /home/oracle/sh/check_disk.sh
#*/10 * * * * /home/oracle/sh/get_ORA_errors.sh >> /home/oracle/sh/get_ORA_errors.log
1 0 1 * * /opt/monitor/sql/backup_listenerlog.sh >> /opt/monitor/log/backup_listenerlog.log
0 9 * * 5 /home/oracle/sh/gather_stats.sh
*/10 * * * * /home/oracle/sh/get_vmstat.sh >> /home/oracle/log/vmstat.log
0 * * * * /home/oracle/sh/check_trace_dw.sh >> /home/oracle/log/check_trace_dw.log
0,30 * * * * /opt/monitor/sql/switchlog.sh >> /opt/monitor/log/switchlog.log
05 13 * * 5 /opt/monitor/sql/gather_stats.sh >> /opt/monitor/log/gather_stats.log
30 17 * * 2 /opt/monitor/sql/gather_stats_empty.sh >> /opt/monitor/log/gather_stats_empty.log
30 9 * * * /opt/monitor/sql/create_awrrpt.sh
##########AUTOEXTEND TABLESPACE CLIENT SHELL by jason 2011-08-11
#13 4 * * * /home/oracle/autoextend_ts/autoextend_ts.sh >> /home/oracle/autoextend_ts/autoextend_ts.log 2>&1
##################################################################################
## dailycheck --larry
##################################################################################
00 08 * * * sh /home/oracle/dailycheck/dailycheck_diskspace.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_alertlog.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_backup.sh
01 08 * * * sh /home/oracle/dailycheck/dailycheck_scp.sh
#################################################################################
## dm add
20 12 * * * sh /u01/report/sql/dwdailydatacheck.sh
##36 15 * * * sh /u01/report/sql/dwdailydatacheck.sh
##12 17 3 * * sh /u01/report/sql/data_check.sh
28 10 * * * sh /u01/report/sql/etl_report.sh
##12 12 * * * sh /u01/report/sql/data_check.sh
########################
#ASM disk header backup#
########################
0 3 * * * /opt/monitor/sql/asm_dh_backup_total.sh
######################
#clean archive log
######################
15 * * * * sh /home/oracle/scripts/clear_archi.sh
14.檢查是否存在未完成的分散式事務，主備庫分別執行
SELECT local_tran_id, state, db_user FROM DBA_2PC_PENDING;
-- 如果存在，使用如下方式清除：
-- SELECT local_tran_id FROM dba_2pc_pending;
-- EXECUTE dbms_transaction.purge_lost_db_entry('');
-- COMMIT;
15.儲存當前連線到資料庫的會話的Machine資訊
set lines 132 pages 1111 trim on trims on
spo machine_before_upgrade.txt
select inst_id, machine, count(*) from gv$session where username!='SYS' group by inst_id, machine;
spo off
16.檢查是否有使用者使用了郵件傳送相關的網路包
SET LINES 132 PAGES 1111
COL OWNER FOR A11
COL NAME FOR A33
COL REFERENCED_OWNER FOR A11
COL REFERENCED_NAME FOR A33
SELECT OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE FROM DBA_DEPENDENCIES
WHERE REFERENCED_NAME IN ('UTL_SMTP', 'UTL_TCP');
OWNER NAME REFERENCED_ REFERENCED_NAME REFERENCED_TYPE
----------- --------------------------------- ----------- --------------------------------- ------------------
SYS UTL_SMTP SYS UTL_SMTP PACKAGE
PUBLIC UTL_SMTP SYS UTL_SMTP PACKAGE
SYS DBMS_AQELM SYS UTL_SMTP PACKAGE
SYS DBMS_ISCHED SYS UTL_SMTP PACKAGE
PUBLIC UTL_TCP SYS UTL_TCP PACKAGE
SYS UTL_SMTP SYS UTL_TCP PACKAGE
SYS MAIL_TOOLS SYS UTL_TCP PACKAGE
SYS MAIL_TOOLS SYS UTL_TCP PACKAGE
SYS UTL_TCP SYS UTL_TCP PACKAGE
SYS UTL_SMTP SYS UTL_TCP PACKAGE
SYS SCHEDULER$_JOB_EVENT_HANDLER SYS UTL_TCP PACKAGE
DW PRC_SENDEMAIL SYS UTL_TCP PACKAGE
SYS DBMS_AQELM SYS UTL_TCP PACKAGE
SYS DBMS_ISCHED SYS UTL_TCP PACKAGE
DMDEV PRC_SENDEMAIL PUBLIC UTL_SMTP SYNONYM
DW PRC_SENDEMAIL PUBLIC UTL_SMTP SYNONYM
DW001 MAIL_TOOLS PUBLIC UTL_TCP SYNONYM
DW001 MAIL_TOOLS PUBLIC UTL_TCP SYNONYM
DW MAIL_TOOLS_STREAM1 PUBLIC UTL_TCP SYNONYM
DMDEV PRC_SENDEMAIL PUBLIC UTL_TCP SYNONYM
DW MAIL_TOOLS_STREAM1 PUBLIC UTL_TCP SYNONYM
DW PRC_SENDEMAIL PUBLIC UTL_TCP SYNONYM
DW PRC_SENDEMAIL DW UTL_SMTP NON-EXISTENT
DW MAIL_TOOLS_STREAM1 DW UTL_TCP NON-EXISTENT
DW MAIL_TOOLS_STREAM1 DW UTL_TCP NON-EXISTENT
DW PRC_SENDEMAIL DW UTL_TCP NON-EXISTENT
DMDEV PRC_SENDEMAIL DMDEV UTL_SMTP NON-EXISTENT
DMDEV PRC_SENDEMAIL DMDEV UTL_TCP NON-EXISTENT
DW001 MAIL_TOOLS DW001 UTL_TCP NON-EXISTENT
DW001 MAIL_TOOLS DW001 UTL_TCP NON-EXISTENT
###########################################
## 升級視窗期 ##
###########################################
1.開啟screen
screen -S upgrade_dw
3. 停止監聽
oracle 使用者 lsnrctl stop
3. 停止所有連線資料庫的應用,確認主備庫沒有連線
SELECT USERNAME, MACHINE, COUNT(*)
FROM V$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY USERNAME, MACHINE
ORDER BY 3;
4. Kill掉任然連線到資料庫的會話
SELECT 'kill -9 '||SPID FROM V$PROCESS
WHERE ADDR IN (SELECT PADDR FROM V$SESSION WHERE USERNAME != 'SYS');
5.關閉資料庫
oracle 使用者
alter system checkpoint;
shutdown immediate;
6.GRID 升級
關閉原來11.2.0.3 GIRD
root 使用者執行GRID 升級指令碼
/u01/app/11.2.0.4/grid/perl/bin/perl -I/u01/app/11.2.0.4/grid/perl/lib -I/u01/app/11.2.0.4/grid/crs/install /u01/app/11.2.0.4/grid/crs/install/roothas.pl
srvctl upgrade asm
7.安裝Grid PSU 11.2.0.4.2
-- 安裝Grid PSU 11.2.0.4.2，Root執行
export ORACLE_HOME=/u01/app/11.2.0.4/grid
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp
$ORACLE_HOME/OPatch/opatch auto /u02/p18139609_112040_Linux-x86-64 /u02/ocm.rsp
8.DB升級：確認ASM是否正常啟動，啟動DB到升級模式
-- 啟動11g的ASM例項
. ~/bash_profile_asm_11204 -- 若當前Session已經是11.2.0.4 ASM環境，可不執行這條命令
引數檔案已經提前複製至新的11.2.0.4 目錄
sqlplus '/ as sysasm'
startup
-- 啟動資料庫到Upgrade模式
. ~/bash_profile_11204 -- 若當前Session已經是11g環境，可不執行這條命令
sqlplus '/ as sysdba'
startup upgrade
alter system set JOB_QUEUE_PROCESSES=0 scope=memory;
alter system set large_pool_size=256m scope=memory;
alter system set java_pool_size=256m scope=memory;
9.DB升級：
-- 執行升級指令碼，耗時40分鐘左右。升級完成後會自動關閉資料庫
. ~/bash_profile_11204 -- 若當前Session已經是11g環境，可不執行這條命令
sqlplus / as sysdba
spool upgrade_11204.log
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/catbundle.sql psu apply
10.檢查AUTOTASK狀態
15:45:42 sys@warehous> select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
如狀態不是disable 需要執行以下操作
-- 關閉auto sql tuning
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
11.DB升級：檢查升級前後的物件狀態
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
select action,comments from registry$history;
spool alter_obj_inval.log
select 'set echo on feedback on' from dual;
select 'spool alter_obj_inval.lst' from dual;
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects
where
STATUS = 'INVALID' and OBJECT_TYPE in
( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
select 'spool off' from dual;
spool off
12.dataguard 升級：
確定主庫已經可以正常使用
開啟歸檔傳輸
alter system set log_archive_dest_state_4=enable;
切換至11.2.0.4 HOME,開啟DATA GUARD 應用
升級完成
###########################################
## 升級完成 ##
###########################################
1. 開啟crontab，開啟jmc監控
###backup dw
#0 2 1,7,14,21,28 * * /opt/monitor/rman/rmandw_level0_bak.sh >> /opt/monitor/log/rmandw_level0_bak.log
#0 2 2,3,5,6,8,9,10,12,13,15,16,17,19,20,22,23,24,26,27,29,30,31 * * /opt/monitor/rman/rmandw_level1_bak.sh >>/opt/monitor/log/rmandw_level1_bak.log
#0 2 4,11,18,25 * * /opt/monitor/rman/rmandw_level2_bak.sh >> /opt/monitor/log/rmandw_level2_bak.log
#*/10 * * * * /home/oracle/sh/check_disk.sh
#*/10 * * * * /home/oracle/sh/get_ORA_errors.sh >> /home/oracle/sh/get_ORA_errors.log
1 0 1 * * /opt/monitor/sql/backup_listenerlog.sh >> /opt/monitor/log/backup_listenerlog.log
0 9 * * 5 /home/oracle/sh/gather_stats.sh
*/10 * * * * /home/oracle/sh/get_vmstat.sh >> /home/oracle/log/vmstat.log
0 * * * * /home/oracle/sh/check_trace_dw.sh >> /home/oracle/log/check_trace_dw.log
0,30 * * * * /opt/monitor/sql/switchlog.sh >> /opt/monitor/log/switchlog.log
05 13 * * 5 /opt/monitor/sql/gather_stats.sh >> /opt/monitor/log/gather_stats.log
30 17 * * 2 /opt/monitor/sql/gather_stats_empty.sh >> /opt/monitor/log/gather_stats_empty.log
30 9 * * * /opt/monitor/sql/create_awrrpt.sh
##########AUTOEXTEND TABLESPACE CLIENT SHELL by jason 2011-08-11
#13 4 * * * /home/oracle/autoextend_ts/autoextend_ts.sh >> /home/oracle/autoextend_ts/autoextend_ts.log 2>&1
##################################################################################
## dailycheck --larry
##################################################################################
00 08 * * * sh /home/oracle/dailycheck/dailycheck_diskspace.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_alertlog.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_backup.sh
01 08 * * * sh /home/oracle/dailycheck/dailycheck_scp.sh
#################################################################################
## dm add
20 12 * * * sh /u01/report/sql/dwdailydatacheck.sh
##36 15 * * * sh /u01/report/sql/dwdailydatacheck.sh
##12 17 3 * * sh /u01/report/sql/data_check.sh
28 10 * * * sh /u01/report/sql/etl_report.sh
##12 12 * * * sh /u01/report/sql/data_check.sh
########################
#ASM disk header backup#
########################
0 3 * * * /opt/monitor/sql/asm_dh_backup_total.sh
######################
#clean archive log
######################
15 * * * * sh /home/oracle/scripts/clear_archi.sh
###########################################
## 回退方法 ##
###########################################
1.確保升級之前停機歸檔傳輸
alter system set log_archive_dest_state_4=defer;
2.如果在歸檔時間視窗不能完成升級,需要直接開啟standby 使用
standby:
Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP
3.複製生產環境指令碼至standby 主機
scp /u01/report.tar 192.168.1.203:/u01/
scp /opt/monitor.tar.20141208.gz 192.168.1.203:/opt/
scp /home/oracle/dailycheck.tar.20141208.gz 192.168.1.203:/home/oracle
scp /home/oracle/sh.tar.20141208 192.168.1.203:/home/oracle
## 升級準備期 ##
#######################################
1. 介質準備，在所有伺服器上，準備如下介質
mkdir -p /u02/soft
unzip /u02/soft/p13390677_112040_Linux-x86-64_1of7 -d /u02/soft
unzip /u02/soft/p13390677_112040_Linux-x86-64_2of7 -d /u02/soft
unzip /u02/soft/p18139609_112040_Linux-x86-64.zip -d /u02/soft
-- 安裝完成DB軟體後，立即更新OPatch
unzip /u02/soft/p18139609_112040_Linux-x86-64.zip -d /u02/soft
1. 安裝11.2.0.4 DB軟體，安裝DB PSU 11.2.0.4.2,編輯新的profile檔案
-- 中轉機器：安裝軟體
. ~/bash_profile_11204
HOSTNAME=histdb
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/db/lib:/usr/lib
ORACLE_SID=billhist
PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.4/db/bin:/u01/app/11.2.0/grid/bin
GRID_HOME=/u01/app/11.2.0.4/grid
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db
2. 安裝11.2.0.4GRID軟體，安裝PSU 11.2.0.4.2，編輯新的profile檔案
-- 注意先不進行css配置，GI的PSU先不安裝
. ~/bash_profile_asm_11204
ORACLE_HOME=/u01/app/11.2.0.4
ORACLE_SID=+ASM
3. 複製11.2.0.3的listener.ora、tnsnames.ora到新的GRID HOME環境中的相關目錄，並修改listener.ora中ORACLE_HOME路徑
複製11.2.0.3 spfile、orapw、檔案ASM 至新的GRID HOME DB 至新的DB HOME
複製主庫crontab 指令碼至standby
/opt/monitor/rman/rmandw_level0_bak.sh
/opt/monitor/rman/rmandw_level1_bak.sh
/opt/monitor/rman/rmandw_level2_bak.sh
/home/oracle/sh/check_disk.sh
/home/oracle/sh/get_ORA_errors.sh
/opt/monitor/sql/backup_listenerlog.sh
/home/oracle/sh/gather_stats.sh
/home/oracle/sh/get_vmstat.sh
/home/oracle/sh/check_trace_dw.sh
/opt/monitor/sql/switchlog.sh
/opt/monitor/sql/gather_stats.sh
/opt/monitor/sql/gather_stats_empty.sh
/opt/monitor/sql/create_awrrpt.sh
/home/oracle/autoextend_ts/autoextend_ts.sh
/home/oracle/dailycheck/dailycheck_diskspace.sh
/home/oracle/dailycheck/dailycheck_alertlog.sh
/home/oracle/dailycheck/dailycheck_backup.sh
/home/oracle/dailycheck/dailycheck_scp.sh
/u01/report/sql/dwdailydatacheck.sh
/u01/report/sql/dwdailydatacheck.sh
/u01/report/sql/data_check.sh
/u01/report/sql/data_check.sh
/opt/monitor/sql/asm_dh_backup_total.sh
/home/oracle/scripts/clear_archi.sh
/root/sh/ntpd-deploy.sh
-- DB所需配置檔案：spfilebillhist.ora, orapwbillhist, listener.ora, tnsnames.ora, sqlnet.ora
-- ASM所需配置檔案：spfile+ASM.ora orapw+ASM, listener.ora, tnsnames.ora, sqlnet.ora
-- 所有伺服器，預先準備好 bash_profile_11204, bash_profile_asm_11204 等環境變數檔案
-- 所有伺服器，預先準備好 utlu112i.sql 等升級指令碼
4. 根據spfile生成pfile檔案
create pfile from spfile;
5. 引數修改：
background_dump_dest
6. 根據修改後的pfile建立新的spfile
CREATE spfile from pfile;
##########################################
## 升級視窗期前10小時內 ##
##########################################
1. 蒐集統計資訊，縮短升級時間，11.2.0.3環境執行
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
2. 清空回收站，11.2.0.3執行
PURGE DBA_RECYCLEBIN;
3. 檢查資料字典邏輯結構是否有損壞，11.2.0.3執行
-- 執行時間較長，但中途終止不會影響升級及後續執行
Set verify off space 0 line 120 heading off feedback off pages 1000
Spool analyze.sql
SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION ALL
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION ALL
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';
spool off
exit
vi analyze.sql
drop table INVALID_ROWS;
@?/rdbms/admin/utlvalid.sql
@analyze.sql
select count(*) from INVALID_ROWS;
#########################################
## 升級視窗期前40分鐘 ##
#########################################
1. 執行升級檢查指令碼dbupgdiag.sql
sqlplus '/ as sysdba'
@/NEW_HOME_ORACLE/rdbms/admin/dbupgdiag.sql
2. 執行預升級指令碼utlu112i.sql，主庫執行
sqlplus '/ as sysdba'
@NEW_HOME_ORACLE/rdbms/admin/utlu112i.sql
3. 檢查自定義觸發器設定，如果有返回記錄，那麼需要在升級期間禁用
SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TABLE_NAME, STATUS
FROM DBA_TRIGGERS
WHERE TRIGGERING_EVENT='DDL'
AND OWNER NOT IN ('SYS', 'SYSTEM');
4. 檢查資料庫中是否有SQL Profile資訊，應該返回：no rows selected
SELECT SIGNATURE, CATEGORY, COUNT(*)
FROM DBA_SQL_PROFILES
GROUP BY SIGNATURE, CATEGORY
HAVING COUNT(*) > 1;
-- 檢查資料庫中的 outline 資訊
COL NAME FOR A26
COL OWNER FOR A8
COL CATEGORY FOR A8
SELECT NAME, OWNER, CATEGORY, USED, SQL_TEXT FROM DBA_OUTLINES;
-- 升級後遷移outline的命令（升級過程中不執行）：
-- SELECT NAME, OWNER, CATEGORY, USED, MIGRATED, SQL_TEXT FROM DBA_OUTLINES;
-- SET LINES 132 PAGES 1111 LONG 99999 SERVERVOUTPUT ON SIZE UNLIMITED
-- DECLARE
-- L_MIG_OUT CLOB;
-- BEGIN
-- L_MIG_OUT := DBMS_SPM.MIGRATE_STORED_OUTLINE('all');
-- DBMS_OUTPUT.PUT_LINE(L_MIG_OUT);
-- END;
-- /
5. 檢查資料庫中當前的DB Link資訊，儲存DB Link的建立指令碼，主備庫分別執行
set lines 1111 pages 0 trim on trims on
spo create_dblink.sql
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
SPO OFF
6. 檢查當前資料庫的國家字符集設定
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_NCHAR_CHARACTERSET';
7. 檢查當前資料檔案狀態和備份狀態，主備庫分別執行
-- 如果有RECOVER狀態的檔案，需要先進行恢復，正常應該返回：no rows selected
SELECT * FROM V$RECOVER_FILE;
SELECT * FROM V$BACKUP WHERE STATUS != 'NOT ACTIVE';
8. 記錄當前資料庫中各項檔案的位置，主備庫分別執行
SET LINES 1111 PAGES 0 TRIM ON TRIMS ON
COL NAME FOR A1000
SPO dbfile_name_info.txt
SELECT 'Datafile' TYPE, NAME FROM V$DBFILE
UNION ALL
SELECT 'Tempfile' TYPE, NAME FROM V$TEMPFILE
UNION ALL
SELECT 'Logfile' TYPE, MEMBER FROM V$LOGFILE
UNION ALL
SELECT 'Control' TYPE, NAME FROM V$CONTROLFILE;
SPO OFF
9.檢查當前資料庫的備庫資訊
SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';
show parameter log_arch
10.檢查SYS和SYSTEM使用者資訊，主備庫分別執行
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
-- 如果SYS和SYSTEM使用者預設表空間不是SYSTEM，將其修改為SYSTEM
-- ALTER user SYS default tablespace SYSTEM;
-- ALTER user SYSTEM default tablespace SYSTEM;
11.檢查審計結果表AUD$和FGA_LOG$，，主備庫分別執行
-- 正常應該返回：AUD$位於SYSTEM表空間，屬於SYS使用者，兩張審計表都沒有記錄
SELECT OWNER,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='AUD$';
SELECT COUNT(*) FROM AUD$;
SELECT COUNT(*) FROM FGA_LOG$;
-- 如果審計表中有記錄，可使用如下方式清除審計表中的記錄：
-- CREATE TABLE AUD_BAK20141209 AS SELECT * FROM AUD$;
-- CREATE TABLE FGA_LOG_BAK20141209 AS SELECT * FROM FGA_LOG$;
-- TRUNCATE TABLE AUD$;
-- TRUNCATE TABLE FGA_LOG$;
12.檢查資料庫中是否存在外部認證的使用者，正常返回：no rows selected
SELECT NAME FROM SYS.USER$ WHERE EXT_USERNAME IS NOT NULL AND PASSWORD = 'GLOBAL';
13.檢查是否有正在執行的Job，停止後續Job的執行
-- 停用Job
SHOW PARAMETER JOB_QUEUE_PROCESSES --記錄原始Job程式數 20
--ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SELECT /*+ RULE */ * FROM DBA_JOBS_RUNNING;
-- 停用所有的crontab，關閉監控
crontab -l
crontab -e
--註釋掉預計在時間視窗內的
###backup dw
#0 2 1,7,14,21,28 * * /opt/monitor/rman/rmandw_level0_bak.sh >> /opt/monitor/log/rmandw_level0_bak.log
#0 2 2,3,5,6,8,9,10,12,13,15,16,17,19,20,22,23,24,26,27,29,30,31 * * /opt/monitor/rman/rmandw_level1_bak.sh >>/opt/monitor/log/rmandw_level1_bak.log
#0 2 4,11,18,25 * * /opt/monitor/rman/rmandw_level2_bak.sh >> /opt/monitor/log/rmandw_level2_bak.log
#*/10 * * * * /home/oracle/sh/check_disk.sh
#*/10 * * * * /home/oracle/sh/get_ORA_errors.sh >> /home/oracle/sh/get_ORA_errors.log
1 0 1 * * /opt/monitor/sql/backup_listenerlog.sh >> /opt/monitor/log/backup_listenerlog.log
0 9 * * 5 /home/oracle/sh/gather_stats.sh
*/10 * * * * /home/oracle/sh/get_vmstat.sh >> /home/oracle/log/vmstat.log
0 * * * * /home/oracle/sh/check_trace_dw.sh >> /home/oracle/log/check_trace_dw.log
0,30 * * * * /opt/monitor/sql/switchlog.sh >> /opt/monitor/log/switchlog.log
05 13 * * 5 /opt/monitor/sql/gather_stats.sh >> /opt/monitor/log/gather_stats.log
30 17 * * 2 /opt/monitor/sql/gather_stats_empty.sh >> /opt/monitor/log/gather_stats_empty.log
30 9 * * * /opt/monitor/sql/create_awrrpt.sh
##########AUTOEXTEND TABLESPACE CLIENT SHELL by jason 2011-08-11
#13 4 * * * /home/oracle/autoextend_ts/autoextend_ts.sh >> /home/oracle/autoextend_ts/autoextend_ts.log 2>&1
##################################################################################
## dailycheck --larry
##################################################################################
00 08 * * * sh /home/oracle/dailycheck/dailycheck_diskspace.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_alertlog.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_backup.sh
01 08 * * * sh /home/oracle/dailycheck/dailycheck_scp.sh
#################################################################################
## dm add
20 12 * * * sh /u01/report/sql/dwdailydatacheck.sh
##36 15 * * * sh /u01/report/sql/dwdailydatacheck.sh
##12 17 3 * * sh /u01/report/sql/data_check.sh
28 10 * * * sh /u01/report/sql/etl_report.sh
##12 12 * * * sh /u01/report/sql/data_check.sh
########################
#ASM disk header backup#
########################
0 3 * * * /opt/monitor/sql/asm_dh_backup_total.sh
######################
#clean archive log
######################
15 * * * * sh /home/oracle/scripts/clear_archi.sh
14.檢查是否存在未完成的分散式事務，主備庫分別執行
SELECT local_tran_id, state, db_user FROM DBA_2PC_PENDING;
-- 如果存在，使用如下方式清除：
-- SELECT local_tran_id FROM dba_2pc_pending;
-- EXECUTE dbms_transaction.purge_lost_db_entry('');
-- COMMIT;
15.儲存當前連線到資料庫的會話的Machine資訊
set lines 132 pages 1111 trim on trims on
spo machine_before_upgrade.txt
select inst_id, machine, count(*) from gv$session where username!='SYS' group by inst_id, machine;
spo off
16.檢查是否有使用者使用了郵件傳送相關的網路包
SET LINES 132 PAGES 1111
COL OWNER FOR A11
COL NAME FOR A33
COL REFERENCED_OWNER FOR A11
COL REFERENCED_NAME FOR A33
SELECT OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE FROM DBA_DEPENDENCIES
WHERE REFERENCED_NAME IN ('UTL_SMTP', 'UTL_TCP');
OWNER NAME REFERENCED_ REFERENCED_NAME REFERENCED_TYPE
----------- --------------------------------- ----------- --------------------------------- ------------------
SYS UTL_SMTP SYS UTL_SMTP PACKAGE
PUBLIC UTL_SMTP SYS UTL_SMTP PACKAGE
SYS DBMS_AQELM SYS UTL_SMTP PACKAGE
SYS DBMS_ISCHED SYS UTL_SMTP PACKAGE
PUBLIC UTL_TCP SYS UTL_TCP PACKAGE
SYS UTL_SMTP SYS UTL_TCP PACKAGE
SYS MAIL_TOOLS SYS UTL_TCP PACKAGE
SYS MAIL_TOOLS SYS UTL_TCP PACKAGE
SYS UTL_TCP SYS UTL_TCP PACKAGE
SYS UTL_SMTP SYS UTL_TCP PACKAGE
SYS SCHEDULER$_JOB_EVENT_HANDLER SYS UTL_TCP PACKAGE
DW PRC_SENDEMAIL SYS UTL_TCP PACKAGE
SYS DBMS_AQELM SYS UTL_TCP PACKAGE
SYS DBMS_ISCHED SYS UTL_TCP PACKAGE
DMDEV PRC_SENDEMAIL PUBLIC UTL_SMTP SYNONYM
DW PRC_SENDEMAIL PUBLIC UTL_SMTP SYNONYM
DW001 MAIL_TOOLS PUBLIC UTL_TCP SYNONYM
DW001 MAIL_TOOLS PUBLIC UTL_TCP SYNONYM
DW MAIL_TOOLS_STREAM1 PUBLIC UTL_TCP SYNONYM
DMDEV PRC_SENDEMAIL PUBLIC UTL_TCP SYNONYM
DW MAIL_TOOLS_STREAM1 PUBLIC UTL_TCP SYNONYM
DW PRC_SENDEMAIL PUBLIC UTL_TCP SYNONYM
DW PRC_SENDEMAIL DW UTL_SMTP NON-EXISTENT
DW MAIL_TOOLS_STREAM1 DW UTL_TCP NON-EXISTENT
DW MAIL_TOOLS_STREAM1 DW UTL_TCP NON-EXISTENT
DW PRC_SENDEMAIL DW UTL_TCP NON-EXISTENT
DMDEV PRC_SENDEMAIL DMDEV UTL_SMTP NON-EXISTENT
DMDEV PRC_SENDEMAIL DMDEV UTL_TCP NON-EXISTENT
DW001 MAIL_TOOLS DW001 UTL_TCP NON-EXISTENT
DW001 MAIL_TOOLS DW001 UTL_TCP NON-EXISTENT
###########################################
## 升級視窗期 ##
###########################################
1.開啟screen
screen -S upgrade_dw
3. 停止監聽
oracle 使用者 lsnrctl stop
3. 停止所有連線資料庫的應用,確認主備庫沒有連線
SELECT USERNAME, MACHINE, COUNT(*)
FROM V$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY USERNAME, MACHINE
ORDER BY 3;
4. Kill掉任然連線到資料庫的會話
SELECT 'kill -9 '||SPID FROM V$PROCESS
WHERE ADDR IN (SELECT PADDR FROM V$SESSION WHERE USERNAME != 'SYS');
5.關閉資料庫
oracle 使用者
alter system checkpoint;
shutdown immediate;
6.GRID 升級
關閉原來11.2.0.3 GIRD
root 使用者執行GRID 升級指令碼
/u01/app/11.2.0.4/grid/perl/bin/perl -I/u01/app/11.2.0.4/grid/perl/lib -I/u01/app/11.2.0.4/grid/crs/install /u01/app/11.2.0.4/grid/crs/install/roothas.pl
srvctl upgrade asm
7.安裝Grid PSU 11.2.0.4.2
-- 安裝Grid PSU 11.2.0.4.2，Root執行
export ORACLE_HOME=/u01/app/11.2.0.4/grid
$ORACLE_HOME/OPatch/ocm/bin/emocmrsp
$ORACLE_HOME/OPatch/opatch auto /u02/p18139609_112040_Linux-x86-64 /u02/ocm.rsp
8.DB升級：確認ASM是否正常啟動，啟動DB到升級模式
-- 啟動11g的ASM例項
. ~/bash_profile_asm_11204 -- 若當前Session已經是11.2.0.4 ASM環境，可不執行這條命令
引數檔案已經提前複製至新的11.2.0.4 目錄
sqlplus '/ as sysasm'
startup
-- 啟動資料庫到Upgrade模式
. ~/bash_profile_11204 -- 若當前Session已經是11g環境，可不執行這條命令
sqlplus '/ as sysdba'
startup upgrade
alter system set JOB_QUEUE_PROCESSES=0 scope=memory;
alter system set large_pool_size=256m scope=memory;
alter system set java_pool_size=256m scope=memory;
9.DB升級：
-- 執行升級指令碼，耗時40分鐘左右。升級完成後會自動關閉資料庫
. ~/bash_profile_11204 -- 若當前Session已經是11g環境，可不執行這條命令
sqlplus / as sysdba
spool upgrade_11204.log
@?/rdbms/admin/catupgrd.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/catbundle.sql psu apply
10.檢查AUTOTASK狀態
15:45:42 sys@warehous> select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor DISABLED
如狀態不是disable 需要執行以下操作
-- 關閉auto sql tuning
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
11.DB升級：檢查升級前後的物件狀態
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
select action,comments from registry$history;
spool alter_obj_inval.log
select 'set echo on feedback on' from dual;
select 'spool alter_obj_inval.lst' from dual;
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects
where
STATUS = 'INVALID' and OBJECT_TYPE in
( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
select 'spool off' from dual;
spool off
12.dataguard 升級：
確定主庫已經可以正常使用
開啟歸檔傳輸
alter system set log_archive_dest_state_4=enable;
切換至11.2.0.4 HOME,開啟DATA GUARD 應用
升級完成
###########################################
## 升級完成 ##
###########################################
1. 開啟crontab，開啟jmc監控
###backup dw
#0 2 1,7,14,21,28 * * /opt/monitor/rman/rmandw_level0_bak.sh >> /opt/monitor/log/rmandw_level0_bak.log
#0 2 2,3,5,6,8,9,10,12,13,15,16,17,19,20,22,23,24,26,27,29,30,31 * * /opt/monitor/rman/rmandw_level1_bak.sh >>/opt/monitor/log/rmandw_level1_bak.log
#0 2 4,11,18,25 * * /opt/monitor/rman/rmandw_level2_bak.sh >> /opt/monitor/log/rmandw_level2_bak.log
#*/10 * * * * /home/oracle/sh/check_disk.sh
#*/10 * * * * /home/oracle/sh/get_ORA_errors.sh >> /home/oracle/sh/get_ORA_errors.log
1 0 1 * * /opt/monitor/sql/backup_listenerlog.sh >> /opt/monitor/log/backup_listenerlog.log
0 9 * * 5 /home/oracle/sh/gather_stats.sh
*/10 * * * * /home/oracle/sh/get_vmstat.sh >> /home/oracle/log/vmstat.log
0 * * * * /home/oracle/sh/check_trace_dw.sh >> /home/oracle/log/check_trace_dw.log
0,30 * * * * /opt/monitor/sql/switchlog.sh >> /opt/monitor/log/switchlog.log
05 13 * * 5 /opt/monitor/sql/gather_stats.sh >> /opt/monitor/log/gather_stats.log
30 17 * * 2 /opt/monitor/sql/gather_stats_empty.sh >> /opt/monitor/log/gather_stats_empty.log
30 9 * * * /opt/monitor/sql/create_awrrpt.sh
##########AUTOEXTEND TABLESPACE CLIENT SHELL by jason 2011-08-11
#13 4 * * * /home/oracle/autoextend_ts/autoextend_ts.sh >> /home/oracle/autoextend_ts/autoextend_ts.log 2>&1
##################################################################################
## dailycheck --larry
##################################################################################
00 08 * * * sh /home/oracle/dailycheck/dailycheck_diskspace.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_alertlog.sh
00 08 * * * sh /home/oracle/dailycheck/dailycheck_backup.sh
01 08 * * * sh /home/oracle/dailycheck/dailycheck_scp.sh
#################################################################################
## dm add
20 12 * * * sh /u01/report/sql/dwdailydatacheck.sh
##36 15 * * * sh /u01/report/sql/dwdailydatacheck.sh
##12 17 3 * * sh /u01/report/sql/data_check.sh
28 10 * * * sh /u01/report/sql/etl_report.sh
##12 12 * * * sh /u01/report/sql/data_check.sh
########################
#ASM disk header backup#
########################
0 3 * * * /opt/monitor/sql/asm_dh_backup_total.sh
######################
#clean archive log
######################
15 * * * * sh /home/oracle/scripts/clear_archi.sh
###########################################
## 回退方法 ##
###########################################
1.確保升級之前停機歸檔傳輸
alter system set log_archive_dest_state_4=defer;
2.如果在歸檔時間視窗不能完成升級,需要直接開啟standby 使用
standby:
Alter DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Alter DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP
3.複製生產環境指令碼至standby 主機
scp /u01/report.tar 192.168.1.203:/u01/
scp /opt/monitor.tar.20141208.gz 192.168.1.203:/opt/
scp /home/oracle/dailycheck.tar.20141208.gz 192.168.1.203:/home/oracle
scp /home/oracle/sh.tar.20141208 192.168.1.203:/home/oracle
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2125105/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫11.2.0.3升級到11.2.0.4Oracle資料庫
- 探索Oracle之資料庫升級二 11.2.0.3升級到11.2.0.4完整步驟Oracle資料庫
- oracle rac 11.2.0.3 升級到11.2.0.4Oracle
- 探索Oracle之資料庫升級三 回退升級操作(11.2.0.4Downgrade 11.2.0.3)Oracle資料庫
- oracle 資料庫從10.2.0.4升級到11.2.0.3Oracle資料庫
- Oracle 11.2.0.1 升級到11.2.0.3Oracle
- ORACLE 11.2.0.1升級到11.2.0.3Oracle
- Oracle 11.2.0.1升級到11.2.0.3Oracle
- Oracle 11.2.0.4升級到12.2.0.1Oracle
- 如何升級Oracle Grid Infrastructure和RAC從11.2.0.3到11.2.0.4？OracleASTStruct
- Oracle資料庫異機升級（10.2.0.5 --> 11.2.0.4）Oracle資料庫
- ORACLE11.2.0.1升級到11.2.0.3Oracle
- oracle for windows 11.2.0.1升級到11.2.0.4OracleWindows
- 11.2.0.3 database異機升級至11.2.0.4Database
- Oracle 資料庫升級Oracle資料庫
- rac 升級crs 升級資料庫軟體，升級資料庫資料庫
- 靜默升級oracle 11g （從11.2.0.1升級到11.2.0.4）Oracle
- 單例項環境下Oracle 11.2.0.3升級到11.2.0.4的過程單例Oracle
- oracle版本升級：從11.2.0.1到11.2.0.3Oracle
- 資料庫版本升級(upgrade 9.2.0.1 to11.2.0.3)資料庫
- oracle11.2.0.3升級到11.2.0.4出現查詢效能問題，分析處理Oracle
- 升級到資料庫到10.2.0.5.0版本資料庫
- 資料庫升級資料庫
- ♀♀資料庫升級♀♀資料庫
- Oracle資料庫升級(轉發)Oracle資料庫
- Oracle 資料庫 升級為 RACOracle資料庫
- Oracle 10.2.0.5升級至11.2.0.4Oracle
- RAC資料庫升級到10.2.0.5資料庫
- 資料庫升級之-Dataguard滾動升級資料庫
- RAC 資料庫升級 10.2.0.1.0 到 10.2.0.2.0 之升級catalog資料庫
- Oracle10g RAC 資料庫升級為10.2.0.4 ，clusterware需要升級到10.2.0.4 ？Oracle資料庫
- 生產庫升級:oracle 9.2.0.1升級oracle 9.2.0.8Oracle
- 靜默方式安裝、升級oracle(三): 升級資料庫軟體及資料庫Oracle資料庫
- 探索Oracle之資料庫升級一 升級補丁修復概述Oracle資料庫
- Oracle資料庫升級與補丁Oracle資料庫
- Oracle 資料庫升級注意事項Oracle資料庫
- Oracle資料庫從 9.2.0.1 到 9.2.0.8升級步驟 for winOracle資料庫
- 圖形化升級單機oracle 11.2.0.1 到 11.2.0.4Oracle