活动第27次(2018-06-13星期三)
TRANSPORTABLE DATABASE从windows那边,考过来的两个文本:
其中的参数文件
grep -v '^\^'initorcl.ora| grep -v '^同时,在LINUX这边建一下该建的目录:
mkdir -p /u01/app/oracle/fast_recovery_area同时,在LINUX这边建一下该建的目录:
mkdir -p /u01/app/oracle/fast_recovery_area
mkdir -p /u01/app/oracle/admin/winorcl/adump
最终修改过的initorcl.ora:
control_files = '/u01/app/oracle/oradata/winorcl/control01.dbf','/u01/app/oracle/oradata/winorcl/control02.dbf','/u01/app/oracle/oradata/winorcl/control03.dbf'
db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size= 4G
audit_file_dest = '/u01/app/oracle/admin/winorcl/adump'
db_name = 'orcl'
remote_login_passwordfile= 'EXCLUSIVE'
db_domain = 'example.com'
dispatchers = '(PROTOCOL=TCP) (SERVICE=orclXDB)'
processes = 800
nls_language = 'SIMPLIFIED CHINESE'
nls_territory = 'CHINA'
memory_target = 2048M
db_block_size = 8192
compatible = '11.2.0.0.0'
undo_tablespace = 'UNDOTBS1'
audit_trail = 'OS'
open_cursors = 400
有了参数 文件之后,启动到nomount:
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 14 03:30:00 2018
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2042241024 bytes
Fixed Size 1337548 bytes
Variable Size 1124075316 bytes
Database Buffers 905969664 bytes
Redo Buffers 10858496 bytes
ORA-00205: error in identifying control file, check alert log for more info
接下来,编辑控制文件创建脚本:
CREATE CONTROLFILE REUSE SET DATABASE orcl RESETLOGSNOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/winorcl/redo01.log'SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/winorcl/redo02.log'SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/winorcl/redo03.log'SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/winorcl/system01.dbf',
'/u01/app/oracle/oradata/winorcl/sysaux01.dbf',
'/u01/app/oracle/oradata/winorcl/undotbs1.dbf',
'/u01/app/oracle/oradata/winorcl/users01.dbf',
'/u01/app/oracle/oradata/winorcl/example01.dbf',
'/u01/app/oracle/oradata/winorcl/tbszhs01.dbf'
CHARACTER SET ZHS16GBK
;
控制文件创建完, 不完全恢复也做完之后,还要做以下两个操作:
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='C:\DATA\INIT_00T51J1H_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\DATA\INIT_00T51J1H_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
附上在目的地转换的脚本(我们已经在windows源上做了,以下脚本不需要做):
RUN {
CONVERT
FROM PLATFORM 'Microsoft Windows IA (32-bit)'
PARALLELISM 1
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-SYSTEM_FNO-1_08T51J1H'
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-SYSAUX_FNO-2_09T51J20'
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-EXAMPLE_FNO-5_0AT51J2G'
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-UNDOTBS1_FNO-3_0BT51J2J'
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\TBSZHS.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-TBSZHS_FNO-6_0CT51J2M'
DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF' FORMAT 'C:\DATA\DATA_D-ORCL_I-1347195613_TS-USERS_FNO-4_0DT51J2N'
; }
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 14 03:45:13 2018
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @control11d.sql
Control file created.
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
MOUNTED
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1272917 generated at 06/09/2018 18:21:25 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2018_06_14/o1_mf_1_18_%u_.arc
ORA-00280: change 1272917 for thread 1 is in sequence #18
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308:
接下来的工序一般是换dbid和改名字,建议使用nid命令(如过只改名字,建议自己写带有 set的create control脚本):
nid TARGET=sys/oracle_4UDBNAME=winorcl
$ nid TARGET=sys/oracle_4UDBNAME=winorcl
DBNEWID: Release 11.2.0.1.0 - Production on Thu Jun 14 04:09:46 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
Connected to database ORCL (DBID=1347195613)
NID-00121: Database should not be open
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 14 04:09:58 2018
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2042241024 bytes
Fixed Size 1337548 bytes
Variable Size 1174406964 bytes
Database Buffers 855638016 bytes
Redo Buffers 10858496 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ nid TARGET=sys/oracle_4UDBNAME=winorcl
DBNEWID: Release 11.2.0.1.0 - Production on Thu Jun 14 04:10:37 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.All rights reserved.
Connected to database ORCL (DBID=1347195613)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/winorcl/control01.dbf
/u01/app/oracle/oradata/winorcl/control02.dbf
/u01/app/oracle/oradata/winorcl/control03.dbf
Change database ID and database name ORCL to WINORCL? (Y/) => Y
Proceeding with operation
Changing database ID from 1347195613 to 2126894910
Changing database name from ORCL to WINORCL
Control File /u01/app/oracle/oradata/winorcl/control01.dbf - modified
Control File /u01/app/oracle/oradata/winorcl/control02.dbf - modified
Control File /u01/app/oracle/oradata/winorcl/control03.dbf - modified
Datafile /u01/app/oracle/oradata/winorcl/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/winorcl/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/winorcl/undotbs1.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/winorcl/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/winorcl/example01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/winorcl/tbszhs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/winorcl/control01.dbf - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/winorcl/control02.dbf - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/winorcl/control03.dbf - dbid changed, wrote new name
Instance shut down
Database name changed to WINORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database WINORCL changed to 2126894910.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
$
再下一道工序,一般是升级GI和DB:
升级DB之前要先升级GI:
请务必确保安装grid的那个用户,能够执行以下命令:
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.typeONLINE ONLINE station90
ora.diskmon ora....on.type ONLINE ONLINE station90
ora.orcl.db ora....se.type ONLINE ONLINE station90
选目录时候,绝对不能覆盖/u01/app/oracle/product/11.2.0/grid:
执行rootupgrade.sh的过程:
# /u01/app/oracle/product/11.2.0.3/grid/rootupgrade.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/grid
Enter the full pathname of the local bin directory: :
The file "dbhome" already exists in /usr/local/bin.Overwrite it? (y/n)
: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin.Overwrite it? (y/n)
: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.Overwrite it? (y/n)
: y
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/oracle/product/11.2.0.3/grid/crs/install/crsconfig_params
Creating trace directory
已成功升级 ASM 配置。
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node station90 successfully pinned.
Replacing Clusterware entries in upstart
Replacing Clusterware entries in upstart
station90 2018/06/13 21:05:48 /u01/app/oracle/product/11.2.0.3/grid/cdata/station90/backup_20180613_210548.olr
station90 2013/05/23 09:12:57 /u01/app/oracle/product/11.2.0/grid/cdata/station90/backup_20130523_091257.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
#
在原处重装数据库服务器软件,之前把/u01/app/oraInventory/ContentsXML/inventory.xml从:
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.3.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="Ora11g_gridinfrahome1" LOC="/u01/app/oracle/product/11.2.0/grid" TYPE="O" IDX="1"/>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="2"/>
<HOME NAME="Ora11g_gridinfrahome2" LOC="/u01/app/oracle/product/11.2.0.3/grid" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
编辑为:
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2011, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.3.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="Ora11g_gridinfrahome2" LOC="/u01/app/oracle/product/11.2.0.3/grid" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>
做一些删除操作,以下命令供参考:
# su - oracle
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.typeONLINE ONLINE station90
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE station90
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type ONLINE ONLINE station90
$ exit
logout
# cd /u01/app/oracle
# ls
admin checkpointsdiag man_recovery_area oradatastation90
cfgtoollogsClusterwareflash_recovery_areaman_recovery_area.11gproduct
# cd product/
# ls
11.2.011.2.0.3
# cd 11.2.0
# ls
dbhome_1grid
# rm -rf grid
# ln -s /u01/app/oracle/product/11.2.0.3/grid/ ./
# ls
dbhome_1grid
# su - oracle
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE station90
ora.FRA.dg ora....up.type ONLINE ONLINE station90
ora....ER.lsnr ora....er.type ONLINE ONLINE station90
ora.asm ora.asm.type ONLINE ONLINE station90
ora.cssd ora.cssd.typeONLINE ONLINE station90
ora.diskmon ora....on.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE station90
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcl.db ora....se.type ONLINE ONLINE station90
$ . oraenv
ORACLE_SID = ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 13 21:08:51 2018
Copyright (c) 1982, 2009, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> selectopen_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ exit
logout
# vim /u01/app/oraInventory/
backup/ install.platform oraInstaller.propertiesorainstRoot.sh
ContentsXML/ logs/ oraInst.loc oui/
# vim /u01/app/oraInventory/ContentsXML/
comps.xml inventory.xmllibs.xml
# vim /u01/app/oraInventory/ContentsXML/inventory.xml
# vim /u01/app/oraInventory/ContentsXML/inventory.xml
# cd /u01/app/
# ls
oracleoraInventory
# cd oracle/
# ls
admin checkpointsdiag man_recovery_area oradatastation90
cfgtoollogsClusterwareflash_recovery_areaman_recovery_area.11gproduct
# cd product/
# ls
11.2.011.2.0.3
# cd 11.2.0
# ls
dbhome_1grid
# su - oracle
$ pwd
/home/oracle
$ cd /u01/app/oracle/
$ ls
admin checkpointsdiag man_recovery_area oradatastation90
cfgtoollogsClusterwareflash_recovery_areaman_recovery_area.11gproduct
$ cd product/
$ ls
11.2.011.2.0.3
$ cd 11.2.0
$ ls
dbhome_1grid
$ cd dbhome_1/
$ ls
apex csmig dv inventorylog ons perl sqldeveloper utl
assistants css emcli j2ee md OPatch plsql sqlj wwg
bin ctx EMStagePatches_orcljavavm mesg opmn precomp sqlplus xdk
ccr cv has jdbc mgw oracore racg srvm
cdata dbs hs jdev networkoraInst.locrdbms station90.example.com_orcl
cfgtoollogsdc_ocm ide jdk nls ord relnotes sysman
clone deinstall install jlib oc4j oui root.sh timingframework
config demo install.platform ldap odbc owb schedulerucp
crs diagnosticsinstantclient lib olap owm slax uix
$ cd dbs
$ ls
hc_DBUA0.dathc_rcat.datinitorcl.oraorapwemreporapwrcat spfilercat.ora
hc_orcl.dat init.ora lkORCL orapworcl spfileemrep.ora
$ cp initorcl.ora orapworcl spfilercat.oraorapwrcat/home/oracle/
$ cd ..
$ ls
apex csmig dv inventorylog ons perl sqldeveloper utl
assistants css emcli j2ee md OPatch plsql sqlj wwg
bin ctx EMStagePatches_orcljavavm mesg opmn precomp sqlplus xdk
ccr cv has jdbc mgw oracore racg srvm
cdata dbs hs jdev networkoraInst.locrdbms station90.example.com_orcl
cfgtoollogsdc_ocm ide jdk nls ord relnotes sysman
clone deinstall install jlib oc4j oui root.sh timingframework
config demo install.platform ldap odbc owb schedulerucp
crs diagnosticsinstantclient lib olap owm slax uix
$ cd network/
$ cd admin/
$ ls
listener.orasamplesshrept.lsttnsnames.ora
$ cp tnsnames.ora/home/oracle/
$
$ exit
logout
# ls
dbhome_1grid
# rm -rf dbhome_1/
#
11.2.0.3的db软件就安装在原处:
###########
startup upgrade //其实就是禁用新特性
spool filename.log
@utlu112i.sql
@catupgrd.sql
自动shutdown immediate
startup
@utlu112s.sql
@catuppst.sql
@utlrp.sql 2h
catupgr.sql执行完:
......
DOC> component upgrade are also listed.
DOC>
DOC> Please review the status and version columns and check the details
DOC> any errors in the spool log file.If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> Rem =====================================================================
SQL> Rem Index Creation for Bootstrap Objects. utlmmig will shutdown the
SQL> Rem database.
SQL> Rem DB must be restarted after this script.
SQL> Rem For 11.2 patch upgrades, utlmmig.sql is not run, but the database
SQL> Rem is shutdown via catupshd.sql.
SQL> Rem =====================================================================
SQL>
SQL> SELECT version_script FROM DUAL;
VERSION_SCRIPT
--------------------------------------------------------------------------------
1102000
1 row selected.
SQL>
SQL> COLUMN mig_name NEW_VALUE mig_file NOPRINT;
SQL> SELECT version_script AS mig_name FROM DUAL;
1 row selected.
SQL>
SQL> VARIABLE utl_name VARCHAR2(50)
SQL> COLUMN :utl_name NEW_VALUE utl_file NOPRINT;
SQL>
SQL> BEGIN
2 IF '&&mig_file' = '1102000' THEN
3 :utl_name := 'catupshd.sql';
4 ELSE
5 :utl_name := 'utlmmig.sql';
6 END IF;
7END;
8/
old 2: IF '&&mig_file' = '1102000' THEN
new 2: IF '1102000' = '1102000' THEN
PL/SQL procedure successfully completed.
SQL> drop version_script; -- no longer needed
2
SQL> SELECT :utl_name FROM DUAL;
1 row selected.
SQL> @@&utl_file
SQL> Rem
SQL> Rem $Header: catupshd.sql 12-jul-2007.07:16:44 rburns Exp $
SQL> Rem
SQL> Rem catupshd.sql
SQL> Rem
SQL> Rem Copyright (c) 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem catupshd.sql - CATalog UPgrade SHutDown
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script shuts down the database at the conclusion of
SQL> Rem upgrades that do not run utlmmig.sql, which also does a shutdown.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Invoked from catupend.sql
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem rburns 07/12/07 - final upgrade shutdown
SQL> Rem rburns 07/12/07 - Created
SQL> Rem
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$
页:
[1]