溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)Oracle數(shù)據(jù)庫異機(jī)升級是怎樣的，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
架構(gòu)介紹：
源庫：單實(shí)例 ip:192.168.56.11 ORCLE_SID:orcl db_nme:orcl 版本：10.2.0.5.0
os:Red Hat Enterprise Linux Server release 4 (Tikanga) hostname:rhel
目標(biāo)庫：單實(shí)例 ip:192.168.56.28 ORACLE_SID:kill 版本：11.2.0.4（只安裝了or源庫cle軟件）
os:Red Hat Enterprise Linux Server release 6.7 (Santiago) hostname:testd目標(biāo)庫
異機(jī)升級步驟：
一、 確認(rèn)是否可以直接升級
二、 目標(biāo)庫機(jī)安裝11g軟件，打好PSU補(bǔ)丁
三、 目標(biāo)庫機(jī)環(huán)境變量檢查
四、 目標(biāo)庫機(jī)創(chuàng)建需要的目錄
五、 源庫機(jī)原庫升級前檢查
5.1 從目標(biāo)庫機(jī)上傳utlu112i.sql腳本到源庫機(jī)
5.2 源庫機(jī)執(zhí)行utlu112i.sql進(jìn)行升級前檢查
5.3 根據(jù)檢查結(jié)果調(diào)整源庫機(jī)數(shù)據(jù)庫
六、 源庫機(jī)備份原庫
七、 目標(biāo)庫機(jī)恢復(fù)數(shù)據(jù)庫
7.1 創(chuàng)建密碼文件
7.2 啟動實(shí)例到nomount狀態(tài)(指定pfile文件)
7.3 RMAN恢復(fù)控制文件，確定備份集有效性
7.4 RMAN恢復(fù)數(shù)據(jù)庫
7.5 打開數(shù)據(jù)庫(resetlogs)
八、 目標(biāo)庫機(jī)升級數(shù)據(jù)庫
8.1 為預(yù)防升級測試過程中默認(rèn)的歸檔空間不夠，這里升級先把庫開啟為非歸檔模式
8.2 升級open數(shù)據(jù)庫
8.3 執(zhí)行升級腳本
九、 目標(biāo)庫機(jī)升級后操作
9.1 創(chuàng)建spfile文件
9.2 執(zhí)行EXECUTE dbms_stats.gather_dictionary_stats收集數(shù)據(jù)字典統(tǒng)計(jì)信息
9.3 重新編譯 @?/rdbms/admin/utlrp.sql
9.4 查看失效對象
9.5 運(yùn)行升級后檢查腳本@?/rdbms/admin/utlu112s.sql
9.6 數(shù)據(jù)庫層面應(yīng)用PSU
一、 確認(rèn)是否可以直接升級
參考：http://blog.itpub.net/31397003/viewspace-2146129/
二、 目標(biāo)庫機(jī)安裝11g軟件，打好PSU補(bǔ)丁
升級安裝至oracle軟件步驟省略...................
打PSU補(bǔ)丁如下：
[oracle@testdb OPatch]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch
[oracle@testdb OPatch]$
[oracle@testdb OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.4
OPatch succeeded.
[oracle@testdb OPatch]$
上傳opatch包及psu包如下：
[oracle@testdb ~]$ ls
database db_install_oui.rsp p24732075_112040_Linux-x86-64.zip p6880880_112000_Linux-x86-64.zip
[oracle@testdb ~]$ pwd
/home/oracle
更新opatch工具：
[oracle@testdb dbhome_1]$ mv OPatch OPatch.bak
[oracle@testdb dbhome_1]$
[oracle@testdb ~]$ unzip p6880880_112000_Linux-x86-64.zip -d /u01/app/oracle/product/11.2.0/dbhome_1
省略.....................
[oracle@testdb ~]$ cd -
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@testdb dbhome_1]$ cd OPatch
[oracle@testdb OPatch]$ ./opatch version
OPatch Version: 11.2.0.3.12
OPatch succeeded.
[oracle@testdb OPatch]$
應(yīng)用psu:
[oracle@testdb ~]$ ls
database db_install_oui.rsp p24732075_112040_Linux-x86-64.zip p6880880_112000_Linux-x86-64.zip
[oracle@testdb ~]$
[oracle@testdb ~]$ mkdir soft
[oracle@testdb ~]$ mv p24732075_112040_Linux-x86-64.zip soft/
[oracle@testdb ~]$
[oracle@testdb ~]$ cd soft/
[oracle@testdb soft]$ ls
p24732075_112040_Linux-x86-64.zip
[oracle@testdb soft]$
[oracle@testdb soft]$ unzip p24732075_112040_Linux-x86-64.zip
省略................................
[oracle@testdb soft]$ ls
24732075 p24732075_112040_Linux-x86-64.zip PatchSearch.xml
[oracle@testdb soft]$
[oracle@testdb 24732075]$ ls
17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075 patchmd.xml README.html README.txt
[oracle@testdb 24732075]$
根據(jù)README.html進(jìn)行打補(bǔ)?。?/strong>
進(jìn)行沖突檢測：
[oracle@testdb 24732075]$ pwd
/home/oracle/soft/24732075
[oracle@testdb 24732075]$
[oracle@testdb 24732075]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-51-47PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
[oracle@testdb 24732075]$
開始應(yīng)用補(bǔ)丁：
[oracle@testdb 24732075]$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-54-33PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.sdo, 11.2.0.4.0...
Patching component oracle.sysman.agent, 10.2.0.4.5...
省略...............................................................
省略...............................................................
Patching component oracle.ordim.server, 11.2.0.4.0...
Composite patch 24732075 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-27_21-54-33PM_1.log
OPatch succeeded.
[oracle@testdb 24732075]$
[oracle@testdb OPatch]$ ./opatch lspatches
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)
OPatch succeeded.
[oracle@testdb OPatch]$
三、 目標(biāo)庫機(jī)環(huán)境變量檢查
[oracle@testdb ~]$ cat .bash_profile
# .bash_profile
export ORACLE_SID=kill
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export NLS_LANG="american_america.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:Mi:SS"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin
[oracle@testdb ~]$
四、目標(biāo)庫創(chuàng)建需要的目錄
目標(biāo)庫建議先創(chuàng)建以下目錄，對于bdump,udump在11g不再需要了，這點(diǎn)從后面的升級前檢查腳本的輸出結(jié)果也可看到。
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
[oracle@testdb ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
五、 源庫機(jī)原庫升級前檢查
5.1 從目標(biāo)庫機(jī)上傳utlu112i.sql腳本到源庫機(jī)
[oracle@testdb admin]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@testdb admin]$
[oracle@testdb admin]$ ls -lrt utlu112i*
-rw-rw-rw-. 1 oracle oinstall 225754 Feb 23 2017 utlu112i.sql
[oracle@testdb admin]$
[oracle@testdb admin]$ scp utlu112i.sql oracle@192.168.56.11:/home/oracle
The authenticity of host '192.168.56.11 (192.168.56.11)' can't be established.
RSA key fingerprint is ed:38:fa:9f:2a:49:b6:c6:22:7a:05:78:3e:ea:c4:28.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.11' (RSA) to the list of known hosts.
oracle@192.168.56.11's password:
utlu112i.sql 100% 220KB 220.5KB/s 00:00
[oracle@testdb admin]$
5.2 源庫機(jī)執(zhí)行utlu112i.sql進(jìn)行升級前檢查
[oracle@rhel ~]$ ls
patch soft utlu112i.sql
[oracle@rhel ~]$
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Nov 23 16:32:27 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> spool upgrade.info
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 11-23-2017 16:32:53
Script Version: 11.2.0.4.0 Build: 007
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Linux 64-bit for AMD
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1113 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 714 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 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: --> "sga_target" needs to be increased to at least 596 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]
**********************************************************************
--> 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
--> 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
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [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.1.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER SYS has 1 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data 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.
.
**********************************************************************
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;
**********************************************************************
SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel ~]$
[oracle@rhel ~]$ ls
patch soft upgrade.info utlu112i.sql
[oracle@rhel ~]$
5.3 根據(jù)檢查結(jié)果調(diào)整源庫機(jī)數(shù)據(jù)庫
5.3.1源庫創(chuàng)建pfile并傳到目標(biāo)庫：
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
.1/db_1/dbs/spfileorcl.ora
SQL>
SQL>
SQL> create pfile from spfile;
File created.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@rhel ~]$
[oracle@rhel ~]$ cd $ORACLE_HOME/dbs
[oracle@rhel dbs]$ ls inito*
initorcl.ora
[oracle@rhel dbs]$ scp initorcl.ora oracle@192.168.56.28:/home/oracle
The authenticity of host '192.168.56.28 (192.168.56.28)' can't be established.
RSA key fingerprint is 16:8d:5a:fb:f2:58:e1:ee:4c:98:3d:76:ec:48:bb:46.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.28' (RSA) to the list of known hosts.
oracle@192.168.56.28's password:
initorcl.ora 100% 994 1.0KB/s 00:00
[oracle@rhel dbs]$
5.3.2在目標(biāo)庫庫上進(jìn)行調(diào)整pfile:
注意下sga的設(shè)定是否符合目的機(jī)器的配置
*.sga_target=624951296 WARNING: --> "sga_target" needs to be increased to at least 596 MB
將*.background_dump_dest和*.user_dump_dest刪除
加上 *.diagnostic_dest='$ORACLE_BASE'
注：參數(shù)文件的修改不用多說，但需要注意其他各參數(shù)的值是否符合需求。
[oracle@testdb ~]$ cat initorcl.ora
kill.__db_cache_size=478150656
kill.__java_pool_size=4194304
kill.__large_pool_size=4194304
kill.__shared_pool_size=109051904
kill.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/kill/adump'
#*.background_dump_dest='/u01/app/oracle/admin/kill/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/kill/control01.ctl','/u01/app/oracle/oradata/kill/control02.ctl','/u01/app/oracle/oradata/kill/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/kill/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.diagnostic_dest='$ORACLE_BASE'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=624951296
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
[oracle@testdb ~]$
根據(jù)參數(shù)文件在目標(biāo)庫上創(chuàng)建相應(yīng)的目錄；
[oracle@testdb ~]$ mkdir -p /u01/app/oracle/oradata/kill/
[oracle@testdb cdump]$ mkdir -p /u01/app/oracle/flash_recovery_area
修改后復(fù)制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.ora
[oracle@testdb ~]$ cp initorcl.ora $ORACLE_HOME/dbs/init$ORACLE_SID.ora
[oracle@testdb ~]$ cd $ORACLE_HOME/dbs/
[oracle@testdb dbs]$ ls
initkill.ora init.ora
[oracle@testdb dbs]$
5.3.3 源庫執(zhí)行PURGE DBA_RECYCLEBIN
SQL> PURGE DBA_RECYCLEBIN;
SQL>
5.3.4 源庫收集系統(tǒng)信息EXECUTE dbms_stats.gather_dictionary_stats;
在升級前收集字典統(tǒng)計(jì)信息，否則預(yù)升級工具( utlu102i.sql)會花費(fèi)更長時間
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
5.3.5 源庫運(yùn)行utlrp.sql 腳本，重新編譯無效對象
select * from registry$nonsys_inv_objs; //非sys/system的失效對象
select * from registry$sys_inv_objs; //sys/system的失效對象
SQL> @?/rdbms/admin/utlrp.sql;
重新執(zhí)行 @/home/oracle/utlu112i.sql，查看是否重新編譯
SQL> select * from registry$sys_inv_objs;
OWNER OBJECT_NAME OBJECT_TYP
---------- ------------------------------ ----------
SYS CALLING PROCEDURE
SQL> drop procedure calling;
SQL>
注意：升級之后執(zhí)行utluiobj.sql
Use utluiobj.sql after the upgrade to identify any new invalid
六、 源庫備份
創(chuàng)建備份腳本：
注意；腳本將備份歸檔，需開啟歸檔；
[oracle@rhel ~]$ mkdir bak
[oracle@rhel ~]$ vi backup_all.sh
#!/bin/bash
#Define variable <You may need to change the value of basedir.>
basedir=/home/oracle/bak
date=`date +%Y%m%d`
#Create pfile
sqlplus / as sysdba <<EOF
create pfile='$basedir/pfile$date.ora' from spfile;
EOF
#RMAN BACKUP
rman target / log=$basedir/backup_all_$date.log <<EOF
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
backup database filesperset 4 format '$basedir/full_%d_%T_%s_%p';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
backup archivelog all format '$basedir/arch_%d_%T_%s_%p' delete input;
backup current controlfile format '$basedir/ctl_%d_%T_%s_%p';
release channel c1;
release channel c2;
}
EOF
~
"backup_all.sh" [New] 27L, 773C written
[oracle@rhel ~]$
執(zhí)行腳本；
[oracle@rhel ~]$ nohup sh backup_all.sh &
[1] 17818
[oracle@rhel ~]$ nohup: appending output to `nohup.out'
[oracle@rhel ~]$
[oracle@rhel ~]$ jobs
[1]+ Running nohup sh backup_all.sh &
[oracle@rhel ~]$
[oracle@rhel ~]$ jobs
[1]+ Running nohup sh backup_all.sh &
[oracle@rhel ~]$
[1]+ Done nohup sh backup_all.sh
[oracle@rhel ~]$
[oracle@rhel ~]$ cd bak
[oracle@rhel bak]$ ls -lrt
total 1231432
-rw-r--r-- 1 oracle oinstall 994 Nov 23 17:21 pfile20171123.ora
-rw-r----- 1 oracle oinstall 564199424 Nov 23 17:22 full_ORCL_20171123_6_1
-rw-r----- 1 oracle oinstall 7110656 Nov 23 17:22 full_ORCL_20171123_7_1
-rw-r----- 1 oracle oinstall 98304 Nov 23 17:22 full_ORCL_20171123_8_1
-rw-r----- 1 oracle oinstall 676749312 Nov 23 17:22 full_ORCL_20171123_5_1
-rw-r----- 1 oracle oinstall 4425728 Nov 23 17:23 arch_ORCL_20171123_9_1
-rw-r----- 1 oracle oinstall 4608 Nov 23 17:23 arch_ORCL_20171123_10_1
-rw-r----- 1 oracle oinstall 2560 Nov 23 17:23 arch_ORCL_20171123_11_1
-rw-r----- 1 oracle oinstall 7110656 Nov 23 17:23 ctl_ORCL_20171123_12_1
-rw-r--r-- 1 oracle oinstall 5045 Nov 23 17:23 backup_all_20171123.log
[oracle@rhel bak]$
將源庫備份的文件傳到目標(biāo)路（目標(biāo)庫操作）
[oracle@testdb ~]$ mkdir bak
[oracle@testdb ~]$ cd bak
[oracle@testdb bak]$ ls
[oracle@testdb bak]$
[oracle@testdb bak]$ scp oracle@192.168.56.11:/home/oracle/bak/* .
oracle@192.168.56.11's password:
arch_ORCL_20171019_5_1 100% 36MB 18.0MB/s 00:02
arch_ORCL_20171019_6_1 100% 4096 4.0KB/s 00:00
arch_ORCL_20171019_7_1 100% 2560 2.5KB/s 00:00
backup_all_20171019.log 100% 5042 4.9KB/s 00:00
ctl_ORCL_20171019_8_1 100% 6944KB 6.8MB/s 00:00
full_ORCL_20171019_1_1 100% 692MB 22.3MB/s 00:31
full_ORCL_20171019_2_1 100% 438MB 15.1MB/s 00:29
full_ORCL_20171019_3_1 100% 6944KB 6.8MB/s 00:01
full_ORCL_20171019_4_1 100% 96KB 96.0KB/s 00:00
pfile20171019.ora 100% 995 1.0KB/s 00:00
[oracle@testdb bak]$ ls -lrt
total 1207652
-rw-r----- 1 oracle oinstall 37643264 Dec 31 13:39 arch_ORCL_20171019_5_1
-rw-r----- 1 oracle oinstall 4096 Dec 31 13:39 arch_ORCL_20171019_6_1
-rw-r----- 1 oracle oinstall 2560 Dec 31 13:39 arch_ORCL_20171019_7_1
-rw-r--r-- 1 oracle oinstall 5042 Dec 31 13:39 backup_all_20171019.log
-rw-r----- 1 oracle oinstall 7110656 Dec 31 13:39 ctl_ORCL_20171019_8_1
-rw-r----- 1 oracle oinstall 725647360 Dec 31 13:39 full_ORCL_20171019_1_1
-rw-r----- 1 oracle oinstall 458997760 Dec 31 13:40 full_ORCL_20171019_2_1
-rw-r----- 1 oracle oinstall 7110656 Dec 31 13:40 full_ORCL_20171019_3_1
-rw-r----- 1 oracle oinstall 98304 Dec 31 13:40 full_ORCL_20171019_4_1
-rw-r--r-- 1 oracle oinstall 995 Dec 31 13:40 pfile20171019.ora
[oracle@testdb bak]$
七、目標(biāo)庫恢復(fù)數(shù)據(jù)庫
注意：這里恢復(fù)完成不要直接嘗試打開數(shù)據(jù)庫。因?yàn)槲覀兪且?！需要open resetlogs upgrade（在下一個步驟說明）。
7.1 創(chuàng)建密碼文件:
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y entries=5
[oracle@testdb admin]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y entries=5
[oracle@testdb admin]$ cd $ORACLE_HOME/dbs/
[oracle@testdb dbs]$ ls
initkill.ora init.ora orapwkill
[oracle@testdb dbs]$
7.2 啟動實(shí)例到nomount狀態(tài)(指定pfile文件)
根據(jù)之前修改傳到目標(biāo)庫上的pfile啟動數(shù)據(jù)庫到nomount狀態(tài)；
startup nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
[oracle@testdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:02:13 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
SQL>
7.3 RMAN恢復(fù)控制文件，確定備份集有效性
7.3.1 RMAN恢復(fù)控制文件，啟動數(shù)據(jù)庫到mount狀態(tài)，確定備份集有效性
[oracle@testdb 11gbak]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 28 02:03:42 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
RMAN> restore controlfile from '/home/oracle/bak/ctl_ORCL_20171019_8_1';
Starting restore at 2017-12-31 13:46:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/kill/control01.ctl
output file name=/u01/app/oracle/oradata/kill/control02.ctl
output file name=/u01/app/oracle/oradata/kill/control03.ctl
Finished restore at 2017-12-31 13:46:23
RMAN> alter database mount;
RMAN> crosscheck backupset;
RMAN> crosscheck backupset;
Starting implicit crosscheck backup at 2017-12-31 13:46:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 2017-12-31 13:46:59
Starting implicit crosscheck copy at 2017-12-31 13:46:59
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2017-12-31 13:47:00
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 RECID=1 STAMP=957803210
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_3_1 RECID=2 STAMP=957803260
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_4_1 RECID=3 STAMP=957803264
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 RECID=4 STAMP=957803210
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 RECID=5 STAMP=957803311
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 RECID=6 STAMP=957803311
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 RECID=7 STAMP=957803314
Crosschecked 7 objects
RMAN>
注意，如果備份集過期，則先注冊，再刪除，如下：示例
一般是兩臺主機(jī)的備份目錄不一樣，控制文件記錄的備份集路徑找不到對應(yīng)的備份集，狀態(tài)為'EXPIRED'，此時應(yīng)該刪除這些過期的備份集，catalog新的備份集，再次確認(rèn)備份集有效性。
rman target / > catalog.log <<EOF
catalog backuppiece '/u01/orabak/backup/full_JINGYU_20150911_39_1';
catalog backuppiece '/u01/orabak/backup/full_JINGYU_20150911_40_1';
catalog backuppiece '/u01/orabak/backup/full_JINGYU_20150911_41_1';
catalog backuppiece '/u01/orabak/backup/full_JINGYU_20150911_42_1';
catalog backuppiece '/u01/orabak/backup/arch_JINGYU_20150911_43_1';
catalog backuppiece '/u01/orabak/backup/arch_JINGYU_20150911_44_1';
catalog backuppiece '/u01/orabak/backup/arch_JINGYU_20150911_45_1';
crosscheck backupset;
delete noprompt expired backupset;
crosscheck backupset;
EOF
7.4 RMAN恢復(fù)數(shù)據(jù)庫
7.4.1 restore數(shù)據(jù)文件
如果數(shù)據(jù)文件存放目錄已經(jīng)更改，需要重命名還原。
rman target / log=restore.log <<EOF
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
set newname for datafile 1 to '/u01/app/oracle/oradata/kill/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/kill/undotbs01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/kill/sysaux01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/kill/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/kill/example01.dbf';
restore database;
switch datafile all;
release channel c1;
release channel c2;
}
EOF
[oracle@testdb 11gbak]$ rman target / log=restore.log <<EOF
> run {
> allocate channel c1 device type disk;
> allocate channel c2 device type disk;
> set newname for datafile 1 to '/u01/app/oracle/oradata/kill/system01.dbf';
> set newname for datafile 2 to '/u01/app/oracle/oradata/kill/undotbs01.dbf';
> set newname for datafile 3 to '/u01/app/oracle/oradata/kill/sysaux01.dbf';
> set newname for datafile 4 to '/u01/app/oracle/oradata/kill/users01.dbf';
> set newname for datafile 5 to '/u01/app/oracle/oradata/kill/example01.dbf';
> restore database;
> switch datafile all;
> release channel c1;
> release channel c2;
> }
> EOF
RMAN>
RMAN> [oracle@testdb 11gbak]$
[oracle@testdb 11gbak]$
[oracle@testdb 11gbak]$ tail -200f restore.log
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 28 02:34:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1485502468, not open)
RMAN>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=21 device type=DISK
allocated channel: c2
channel c2: SID=24 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2017-12-31 13:52:59
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/kill/system01.dbf
channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/kill/example01.dbf
channel c1: reading from backup piece /home/oracle/bak/full_ORCL_20171019_2_1
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to /u01/app/oracle/oradata/kill/undotbs01.dbf
channel c2: restoring datafile 00003 to /u01/app/oracle/oradata/kill/sysaux01.dbf
channel c2: restoring datafile 00004 to /u01/app/oracle/oradata/kill/users01.dbf
channel c2: reading from backup piece /home/oracle/bak/full_ORCL_20171019_1_1
channel c1: piece handle=/home/oracle/bak/full_ORCL_20171019_2_1 tag=TAG20171019T162649
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:55
channel c2: piece handle=/home/oracle/bak/full_ORCL_20171019_1_1 tag=TAG20171019T162649
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:00:56
Finished restore at 2017-12-31 13:53:56
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=964187637 file name=/u01/app/oracle/oradata/kill/example01.dbf
released channel: c1
released channel: c2
RMAN>
Recovery Manager complete.
[oracle@testdb bak]$
7.4.2 recover數(shù)據(jù)文件
7.4.2.1 使用recover database命令恢復(fù)數(shù)據(jù)
RMAN> recover database;
RMAN> recover database;
Starting recover at 2017-12-31 13:55:19
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_5_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_5_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc thread=1 sequence=38
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_38_f4jyy9kb_.arc RECID=6 STAMP=964187722
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=40
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=41
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_6_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_6_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc thread=1 sequence=39
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_39_f4jyyf2v_.arc RECID=8 STAMP=964187725
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc thread=1 sequence=40
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_40_f4jyyf1t_.arc RECID=7 STAMP=964187725
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc thread=1 sequence=41
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_41_f4jyyf3q_.arc RECID=9 STAMP=964187725
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=42
channel ORA_DISK_1: reading from backup piece /home/oracle/bak/arch_ORCL_20171019_7_1
channel ORA_DISK_1: piece handle=/home/oracle/bak/arch_ORCL_20171019_7_1 tag=TAG20171019T162831
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc thread=1 sequence=42
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_12_31/o1_mf_1_42_f4jyyg9r_.arc RECID=10 STAMP=964187726
unable to find archived log
archived log thread=1 sequence=43
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2017 13:55:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 43 and starting SCN of 1015325
RMAN>
7.4.2.2 recover database until scn xxxxxxx;
注意：這個scn根據(jù)上一步的日志信息獲取。
RMAN> recover database until scn 1015325;
Starting recover at 2017-12-31 13:55:53
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-12-31 13:55:54
RMAN>
7.4.2.3 修改日志文件路徑
注意：可以用UE列編輯模式快速處理下新的redo文件名字(即redoXXX.log改為對應(yīng)的實(shí)際值)
new_dest=/u01/app/oracle/oradata/kill
sqlplus / as sysdba > logfile.log <<EOF
set linesize 180 pagesize 100
select 'alter database rename file '''||member||''' to ''$new_dest/redoXXX.log'';' from v\$logfile;
EOF
執(zhí)行記錄如下：
[oracle@testdb ~]$ new_dest=/u01/app/oracle/oradata/kill
[oracle@testdb ~]$ sqlplus / as sysdba > logfile.log <<EOF
> set linesize 180 pagesize 100
> select 'alter database rename file '''||member||''' to ''$new_dest/redoXXX.log'';' from v\$logfile;
> EOF
[oracle@testdb ~]$
[oracle@testdb ~]$ cat logfile.log
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 13:57:16 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
'ALTERDATABASERENAMEFILE'''||MEMBER||'''TO''/U01/APP/ORACLE/ORADATA/KILL/REDOXXX.LOG'';'
-----------------------------------------------------------------------------------------------------------
alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/kill/redoXXX.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/kill/redoXXX.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/kill/redoXXX.log';
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb ~]$
正式執(zhí)行：
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/kill/redo03.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/kill/redo02.log';
alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/kill/redo01.log';
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/kill/redo03.log
/u01/app/oracle/oradata/kill/redo02.log
/u01/app/oracle/oradata/kill/redo01.log
7.4.3 修改臨時文件路徑
注意：同樣處理下新的temp文件名字(即tempXXX.dbf改為對應(yīng)的實(shí)際值)。
new_dest=/u01/app/oracle/oradata/kill
sqlplus / as sysdba > tempfile.log<<EOF
set linesize 180 pagesize 100
select 'alter database rename file '''||name||''' to ''$new_dest/tempXXX.dbf'';' from v\$tempfile;
EOF
--查詢
[oracle@testdb 11gbak]$ new_dest=/u01/app/oracle/oradata/kill
[oracle@testdb 11gbak]$ sqlplus / as sysdba > tempfile.log<<EOF
> set linesize 180 pagesize 100
> select 'alter database rename file '''||name||''' to ''$new_dest/tempXXX.dbf'';' from v\$tempfile;
> EOF
[oracle@testdb 11gbak]$ cat tempfile.log
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 02:50:11 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/U01/APP/ORACLE/ORADATA/KILL/TEMPXXX.DBF'';'
-------------------------------------------------------------------------------------------------
alter database rename file '/u01/app/oracle/oradata/orcl/temp01.dbf' to '/u01/app/oracle/oradata/kill/tempXXX.dbf';
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb 11gbak]$
執(zhí)行：
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/temp01.dbf' to '/u01/app/oracle/oradata/kill/temp01.dbf';
Database altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/kill/temp01.dbf
SQL>
7.4 打開數(shù)據(jù)庫(resetlogs)
注意：這里恢復(fù)完成不要直接嘗試打開數(shù)據(jù)庫。因?yàn)槲覀兪且?！需要open upgrade（在下一個步驟說明）。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6097
Session ID: 22 Serial number: 63
SQL>
八、目標(biāo)庫機(jī)升級數(shù)據(jù)庫
--先用pfile啟動到mount下：
SQL> startup mount pfile='$ORACLE_HOME/dbs/init$ORACLE_SID.ora'
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database mounted.
SQL>
8.1 為預(yù)防升級測試過程中默認(rèn)的歸檔空間不夠，這里升級先把庫開啟為非歸檔模式。
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL>
SQL> alter database noarchivelog;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 1
SQL>
8.2 升級open數(shù)據(jù)庫
alter database open resetlogs upgrade; 用于不完全恢復(fù)
alter database open upgrade; 用于完全恢復(fù)
SQL> alter database open resetlogs upgrade;
alter database open resetlogs upgrade
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open upgrade;
Database altered.
SQL>
8.3 執(zhí)行升級腳本
注意此步驟升級時間可能較長；
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
記錄省略........................
..................................
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@testdb dbs]$
[oracle@testdb dbs]$
--腳本執(zhí)行完畢后，自動關(guān)閉了數(shù)據(jù)庫,經(jīng)過以上操作，便可以打開數(shù)據(jù)庫了。
九、 目標(biāo)庫升級后操作
正常啟庫：
[oracle@testdb dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:23:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database mounted.
Database opened.
SQL>
SQL> set lines 200
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string
9.1 創(chuàng)建spfile文件
SQL> create spfile from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2255792 bytes
Variable Size 230687824 bytes
Database Buffers 385875968 bytes
Redo Buffers 3330048 bytes
Database mounted.
Database opened.
SQL>
SQL> set lines 200
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfilekill.ora
SQL>
9.2 執(zhí)行EXECUTE dbms_stats.gather_dictionary_stats收集數(shù)據(jù)字典統(tǒng)計(jì)信息;
SQL> exec dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
SQL>
9.3 重新編譯 @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
---------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2017-12-31 15:33:44
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 2017-12-31 15:35: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>
9.4 查看失效對象
SELECT count(*) FROM dba_invalid_objects;
如果失效對象為升級之前的失效對象，升級過程中沒有使對象失效。
SQL> SELECT count(*) FROM dba_invalid_objects;
COUNT(*)
----------
1
SQL> col owner for a10
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_invalid_objects;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------- -------------------------------------- --------------
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID
SQL>
--源庫查詢：
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------- ------------------- -------
SH FWEEK_PSCAT_SALES_MV TABLE VALID
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW VALID
SQL>
--目標(biāo)庫查詢：
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------- -------------------------------------- --------------
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW INVALID
SH FWEEK_PSCAT_SALES_MV TABLE VALID
SQL>
--目標(biāo)庫重新編譯：
QL> alter MATERIALIZED VIEW sh.FWEEK_PSCAT_SALES_MV compile;
Materialized view altered.
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_name='FWEEK_PSCAT_SALES_MV';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------- -------------------------------------- --------------
SH FWEEK_PSCAT_SALES_MV MATERIALIZED VIEW VALID
SH FWEEK_PSCAT_SALES_MV TABLE VALID
--執(zhí)行utluiobj.sql腳本列出無效對象
[oracle@testdb admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin
[oracle@testdb admin]$
[oracle@testdb admin]$ ls utluiobj.sql
utluiobj.sql
[oracle@testdb admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 16:15:49 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @utluiobj.sql
.
Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 12-31-2017 16:16:00
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner Object Name Object Type
.
PL/SQL procedure successfully completed.
SQL>
9.5 運(yùn)行升級后檢查腳本@?/rdbms/admin/utlu112s.sql
SQL> @?/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 12-31-2017 15:39:37
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:12:16
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:09:05
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:35
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:01:16
OLAP Catalog
. VALID 11.2.0.4.0 00:00:47
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:33
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:07:43
Oracle XDK
. VALID 11.2.0.4.0 00:01:31
Oracle Text
. VALID 11.2.0.4.0 00:00:50
Oracle XML Database
. VALID 11.2.0.4.0 00:04:39
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:16
Oracle Multimedia
. VALID 11.2.0.4.0 00:04:04
Spatial
. VALID 11.2.0.4.0 00:04:47
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:13
Oracle Rule Manager
. VALID 11.2.0.4.0 00:00:10
Final Actions
. 00:00:26
Total Upgrade Time: 00:49:24
PL/SQL procedure successfully completed.
SQL>
9.6 數(shù)據(jù)庫層面應(yīng)用PSU
cd $ORACLE_HOME/rdbms/admin/
SQL> @catbundle.sql psu apply
[oracle@testdb ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@testdb admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Dec 31 15:40:51 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @catbundle.sql psu apply
省略........................
............................
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2017Dec31_15_41_00.log
SQL>
--檢查opatch補(bǔ)丁相關(guān)信息：
[oracle@testdb dbhome_1]$ cd $ORACLE_HOME/OPatch
[oracle@testdb OPatch]$ ./opatch lspatches
24732075;Database Patch Set Update : 11.2.0.4.170418 (24732075)
OPatch succeeded.
[oracle@testdb OPatch]$
[oracle@testdb OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch3017-12-31_15-44-55PM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-12-31_15-44-55PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: testdb
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch 24732075 : applied on Sun Dec 31 13:10:55 CST 2017
Unique Patch ID: 21176096
Patch description: "Database Patch Set Update : 11.2.0.4.170418 (24732075)"
Created on 22 Feb 2017, 21:40:49 hrs PST8PDT
Sub-patch 24006111; "Database Patch Set Update : 11.2.0.4.161018 (24006111)"
Sub-patch 23054359; "Database Patch Set Update : 11.2.0.4.160719 (23054359)"
Sub-patch 22502456; "Database Patch Set Update : 11.2.0.4.160419 (22502456)"
Sub-patch 21948347; "Database Patch Set Update : 11.2.0.4.160119 (21948347)"
Sub-patch 21352635; "Database Patch Set Update : 11.2.0.4.8 (21352635)"
Sub-patch 20760982; "Database Patch Set Update : 11.2.0.4.7 (20760982)"
Sub-patch 20299013; "Database Patch Set Update : 11.2.0.4.6 (20299013)"
Sub-patch 19769489; "Database Patch Set Update : 11.2.0.4.5 (19769489)"
Sub-patch 19121551; "Database Patch Set Update : 11.2.0.4.4 (19121551)"
Sub-patch 18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch 18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch 17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
Bugs fixed:
17288409, 21051852, 24316947, 17811429, 17205719, 18607546, 20506699
17816865, 17922254, 23330119, 17754782, 16934803, 13364795, 17311728
17284817, 17441661, 24560906, 16992075, 17446237, 14015842, 19972569
21756677, 17375354, 20925795, 21538558, 17449815, 19463897, 13866822
17235750, 17982555, 17478514, 18317531, 14338435, 18235390, 20803583
13944971, 20142975, 17811789, 16929165, 18704244, 20506706, 17546973
20334344, 14054676, 17088068, 17346091, 18264060, 17343514, 21538567
19680952, 18471685, 19211724, 13951456, 21847223, 16315398, 18744139
16850630, 23177648, 19049453, 18673304, 17883081, 19915271, 18641419
18262334, 17006183, 16065166, 18277454, 16833527, 10136473, 18051556
17865671, 17852463, 18554871, 17853498, 18334586, 17551709, 17588480
19827973, 17344412, 17842825, 18828868, 17025461, 11883252, 13609098
17239687, 17602269, 19197175, 18316692, 22195457, 17313525, 12611721
19544839, 18964939, 17600719, 18191164, 19393542, 17571306, 20777150
18482502, 19466309, 22243719, 17040527, 17165204, 18098207, 16785708
17465741, 17174582, 16180763, 12982566, 16777840, 19463893, 22195465
16875449, 12816846, 22148226, 17237521, 6599380, 19358317, 25505394
17811438, 17811447, 17945983, 21983325, 18762750, 16912439, 17184721
18061914, 17282229, 18331850, 18202441, 17082359, 18723434, 21972320
19554106, 25505371, 14034426, 18339044, 19458377, 17752995, 20448824
17891943, 17258090, 17767676, 16668584, 18384391, 17040764, 17381384
15913355, 18356166, 14084247, 20596234, 20506715, 21756661, 13853126
18203837, 14245531, 16043574, 21756699, 22195441, 17848897, 17877323
21453153, 17468141, 20861693, 17786518, 17912217, 17037130, 16956380
18155762, 17478145, 17394950, 18641461, 18189036, 18619917, 17027426
21352646, 16268425, 24476274, 22195492, 19584068, 18436307, 22507210
17265217, 17634921, 13498382, 21526048, 19258504, 20004087, 17443671
22195485, 18000422, 22321756, 20004021, 17571039, 21067387, 22905130
16344544, 18009564, 14354737, 21286665, 18135678, 18614015, 20441797
18362222, 17835048, 16472716, 17936109, 17050888, 14010183, 17325413
18747196, 17761775, 16721594, 17082983, 20067212, 21179898, 17302277
18084625, 15990359, 24842886, 18203835, 17297939, 17811456, 22380919
16731148, 21168487, 14133975, 13829543, 17215560, 17694209, 17385178
18091059, 8322815, 17586955, 17201159, 17655634, 18331812, 19730508
18868646, 17648596, 16220077, 16069901, 17348614, 17393915, 17274537
17957017, 18096714, 17308789, 18436647, 14285317, 19289642, 14764829
17622427, 18328509, 16943711, 22195477, 14368995, 22502493, 17346671
18996843, 17783588, 21343838, 16618694, 17672719, 18856999, 18783224
17851160, 17546761, 17798953, 18273830, 22092979, 16596890, 19972566
16384983, 17726838, 22296366, 17360606, 22321741, 13645875, 18199537
16542886, 21787056, 17889549, 14565184, 17071721, 17610798, 20299015
21343897, 22893153, 20657441, 17397545, 18230522, 16360112, 19769489
12905058, 18641451, 12747740, 18430495, 17016369, 17042658, 14602788
17551063, 19972568, 21517440, 18508861, 19788842, 14657740, 17332800
13837378, 19972564, 17186905, 18315328, 19699191, 17437634, 22353199
18093615, 19006849, 19013183, 17296856, 18674024, 17232014, 16855292
17762296, 14692762, 21051840, 17705023, 22507234, 19121551, 21330264
19854503, 21868720, 19309466, 18681862, 20558005, 18554763, 17390160
18456514, 16306373, 13955826, 18139690, 17501491, 17752121, 21668627
17299889, 17889583, 18673325, 19721304, 18293054, 17242746, 17951233
18094246, 17649265, 19615136, 17011832, 16870214, 17477958, 18522509
20631274, 16091637, 17323222, 16595641, 16524926, 18228645, 18282562
17596908, 18031668, 17156148, 16494615, 22683225, 17545847, 25093656
17655240, 24528741, 17614134, 13558557, 17341326, 17891946, 17716305
22657942, 18440095, 16392068, 19271443, 21351877, 18092127, 17614227
18440047, 16903536, 14106803, 18973907, 18673342, 25505382, 19032867
17389192, 17612828, 16194160, 17006570, 25369547, 25505407, 17721717
17390431, 17570240, 16863422, 18325460, 19727057, 16422541, 19972570
17267114, 18244962, 21538485, 18765602, 18203838, 16198143, 17246576
14829250, 17835627, 18247991, 14458214, 21051862, 16692232, 17786278
17227277, 24476265, 16042673, 16314254, 16228604, 16837842, 17393683
23536835, 17787259, 20331945, 20074391, 15861775, 16399083, 18018515
22683212, 18260550, 21051858, 17080436, 16613964, 17036973, 16579084
24433711, 18384537, 18280813, 20296213, 16901385, 15979965, 23330124
18441944, 16450169, 9756271, 17892268, 11733603, 16285691, 17587063
21343775, 18180390, 16538760, 18193833, 21387964, 21051833, 17238511
17824637, 16571443, 18306996, 14852021, 17853456, 18674047, 12364061
24411921, 22195448
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@testdb OPatch]$
--查詢dba_registry_history:
DBA_REGISTRY_HISTORY:
DBA_REGISTRY_HISTORY provides information about upgrades, downgrades, and critical patch updates that have been performed on the database.
SQL> col ACTION_TIME for a30
SQL> col ACTION for a20
SQL> col NAMESPACE for a15
SQL> col VERSION for a15
SQL> col BUNDLE_SERIES for a15
SQL> col COMMENTS for a50
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
------------------------------ -------------------- --------------- --------------- ---------- --------------- ----------------------------
19-OCT-17 02.48.11.467752 PM VIEW RECOMPILE 8289601 view recompilation
19-OCT-17 02.48.11.862564 PM UPGRADE SERVER 10.2.0.5.0 Upgraded from 10.2.0.1.0
31-DEC-17 02.55.55.820329 PM VIEW INVALIDATE 8289601 view invalidation
31-DEC-17 02.56.22.289206 PM UPGRADE SERVER 11.2.0.4.0 Upgraded from 10.2.0.5.0
31-DEC-17 03.41.37.543270 PM APPLY SERVER 11.2.0.4 170418 PSU PSU 11.2.0.4.170418
SQL>
到此，已完成數(shù)據(jù)庫異機(jī)升級操作!
上述就是小編為大家分享的Oracle數(shù)據(jù)庫異機(jī)升級是怎樣的了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
