—————————————————————————————————————————————————
项目一(实验环境)：
数据库升级迁移
oracle 9i单机，数据库建立在文件系统上
迁移到oracle 11g rac asm
—————————————————————————————————————————————————
项目思路：
1、查看MOS上oracle的升级族谱(MOS：Complete Checklist for Manual Upgrades
to 11gR2 [ID 837570.1])：
Compatibility Matrix
Minimum version of the database that can be
directly upgraded to Oracle 11g Release 2 (11.2)
Source Database Target Database
9.2.0.8 or higher 11.2.x
10.1.0.5 or higher 11.2.x
10.2.0.2 or higher 11.2.x
11.1.0.6 or higher 11.2.x
The following database versions will require an indirect
upgrade path:
Source Database Upgrade Path for Target
Database
Target Database
7.3.3 (or lower) ----> 7.3.4 ->
9.2.0.8 ---->
11.2.x
8.0.5 (or lower) ----> 8.0.6 ->
9.2.0.8 ----> 11.2.x
8.1.7 (or lower) ----> 8.1.7.4
-> 10.2.0.2(or any higher 10GR2 version)
----> 11.2.x
9.0.1.3 (or lower) ----> 9.0.1.4
-> 10.2.0.2 (or any higher 10GR2 version)
----> 11.2.x
9.2.0.7(or lower) ----> 9.2.0.8
----> 11.2.x
For example:
If source database is 8.1.7.0.0, the upgrade path to be
followed is as below:
8.1.7.0.0 --> 8.1.7.4 -->
10.2.0.2(or any higher 10GR2 version)-->
11.2.x.
Reminder :
9.2.0.8 patchset : Patch:4547809
10.1.0.5 patchset : Patch:4505133
10.2.0.2 patchset : Patch:4547817
To get quickly id of a patchset :
Note 438049.1 : How To Find RDBMS patchsets on My Oracle
Support
Note 753736.1 : Quick Reference to Patchset Patch
Numbers
我们得知：要从9i升级到11g，我们需要准备至少9208的环境。
我们准备的是9208的环境
2、具体步骤思路：
1》在rhel4u7上安装9204的数据库，升级数据库到9208版本，建库(选择示例数据库模板)
或者
安装9204的数据库，建库，后升级软件及库到9208(dbua可能会出问题，选择手工跑脚本升级库)
以上环境安装好备用。
2》在rhel5u5上安装11201数据库软件，不建库，备用(两个版本的库可以是不同的oracle用户，也可以一样，但ORACLE_HOME要区分开)
3》将9208的软件目录以及数据文件直接tar到11201上，并测试是否能够启动并打开。
4》使用11201软件挂9208的库升级。
5》将升级后的数据库，由文件系统转为ASM
6》将单机的11201 ASM，转为11201 rac ASM
——————————————————————————————————————————————————————————————————————————
安装前检测及配置：
1、在rhel4u7上安装9204数据库软件
参照以下MOS文章：
Requirements for Installing Oracle 9iR2 64-bit on RHEL 4
x86-64 (AMD64/EM64T) [ID 353529.1]
1>检查软件包
安装redhat4u7的时候，尽量将所有开发包选上
并检查以下包：
glibc-kernheaders
glibc-headers
glibc-devel
libstdc++-devel
compat-db
compat-gcc
compat-gcc-32-c++
compat-libgcc
compat-libstdc++
compat-libstdc++
gcc
gcc-c++
gnome-libs
gnome-libs-devel
libaio-devel
libaio
make
openmotif21
xorg-x11-deprecated-libs-devel
xorg-x11-deprecated-libs
检测命令如下：
rpm -qa|grep compat
rpm -qa|grep gcc
rpm -qa|grep gnome-libs
rpm -qa|grep libaio
rpm -qa|grep make
rpm -qa|grep openmotif21
rpm -qa|grep xorg-x11
rpm -qa|grep glibc
rpm -qa|grep libstdc++-devel
rpm -qa|grep xorg-x11-deprecated
如果没有安装，请加载镜像光盘安装所缺软件包
2>配置所需环境
修改 gcc 、g++ 、gcc32 、 g++32 四个命令
mv /usr/bin/gcc /usr/bin/gcc.orig
mv /usr/bin/g++ /usr/bin/g++.orig
ln -s /usr/bin/x86_64-redhat-linux-gcc32 /usr/bin/gcc
ln -s /usr/bin/x86_64-redhat-linux-g++32 /usr/bin/g++
创建用户和组
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
修改内核参数
Modify your kernel settings in /etc/sysctl.conf (RedHat) as
follows:
kernel.shmall = physical RAM size / pagesize For most systems,
this will be the value 2097152. See Note 301830.1 for more
information.
kernel.shmmax = 1/2 of physical RAM. This
would be the value 2147483648 for a system with 4Gb of physical
RAM.
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 327680 for 640
processes)
net.ipv4.ip_local_port_range = 9000
65500
@per Bug 7507772
The runInstaller (OUI) checks may expect this to be the old
guidance of “1024 65000”. The new guidance from Oracle development
is “9000 65500”. Please allow the runInstaller (OUI) to proceed
with the new guidance from Oracle development.
kernel.hostname = yourhost.yourdomain.com #
full qualified hostname !!
kernel.domainname = yourdomain #
domain name !!
Then run "sysctl -p" to activate these new settings
具体做法：
在/etc/sysctl.conf文件中添加如下几行
kernel.shmmax = 5368709120
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
执行 sysctl -p 使配置生效
修改用户的limits
在 /etc/security/limits.conf 文件下添加如下几行：
oracle hard nofile
65536
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile
65536
修改 /etc/profile 文件
添加如下几行
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
设置oracle用户的环境变量
export ORACLE_BASE=/oracle/app/oralce
export ORACLE_SID=bsrdb
export ORACLE_HOME=$ORACLE_BASE/product/9204
export NLS_LANG=American_america.zhs16gbk
export PATH=$ORACLE_HOME/bin:$PATH
export LD_ASSUME_KERNEL=2.4.19
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export DISPLAY=:0
export LANG=en_U
export GDM_LANG= en_US
export LC= en_US
————————————————————————————————————————————————————————————————————————
开始安装9204
在同一目录下解压一下文件(会出现三个目录Disk1、Disk2、Disk3，请不要修改名字)
gunzip amd_9204_linux_disk1.cpio.gz
gunzip amd_9204_linux_disk2.cpio.gz
gunzip amd_9204_linux_disk3.cpio.gz
cpio -idmv < amd_9204_linux_disk1.cpio
cpio -idmv < amd_9204_linux_disk2.cpio
cpio -idmv < amd_9204_linux_disk3.cpio
#su - oracle
$cd Disk1
$./runInstaller
开始安装，一路下一步，没有报错。
如果出现在17%的时候卡出不动 ”copying naee.o“
说明在环境变量没有设置 LD_ASSUME_KERNEL=2.4.19
在安装到88%的时候，如果报错，错误信息如下：Error in invoking target
install_isqlplus of makefile
/opt/oracle/product/9.2/sqlplus/lib/ins_sqlplus.mk。
请按照以下步骤解决：
这是使用了64位的gcc编译32位程序时出现的错误，这里先点击ignore忽略错误，随后我们在解决这个问题。
在安装到98%的时候，会报和刚才类似的错误：Error in invoking target install of
makefile
/opt/oracle/product/9.2/rdbms/lib/ins_rdbms.mk，这里也点击ignore忽略错误。
忽略了两个错误后，安装完成，这时候Oracle会但出一个对话框，要求用户以root身份执行$ORACLE_HOME目录下的一个root.sh脚本。
我们先修正上面两个错误，然后在执行这个脚本。
修正这两个错误是整个安装过程中最麻烦的地方，参照metalink上的文章，我又反反复复摸索了四、五次才尝试成功。
首先以root身份来到/usr/bin目录下，查看gcc*：
# cd /usr/bin
# ls -l gcc*
-rwxr-xr-x 2 root root 105392 May 24 07:46 gcc
-rwxr-xr-x 2 root root 94360 Dec 2 2004 gcc32
这时候我们需要编写一个gcc296的脚本，内容如下：
#cd /usr/bin
#vi gcc296
#!/bin/sh
exec /usr/bin/gcc32 -static-libgcc
-B/usr/lib/gcc-lib/i386-redhat-linux/2.96/ "$@"
注意，上述脚本是metalink给出的，不过metalink给出的脚本至少有3个问题，
首先metalink上给出的gcc32错误拼写成了gcc33，
第二点metalink脚本中包含了-m32，而这个编译标志在编译上述两个错误时是不需要的，当然metalink上有进一步的说明，不过还是容易使人误导。
第三点其实不是metalink脚本的问题，而是这个脚本太长了，导致脚本最后的”$@”跑到了第三行，而实际上整个脚本只有两行。
对操作系统、shell脚本语言以及gcc编译不是很熟悉的人很容易在这里出错。
将上面给出的内容添加到gcc296中，然后修改gcc296的文件属性为755。
然后进行类似的操作编写g++296的脚本：
#vi g++296
#!/bin/sh
exec /usr/bin/g++32 -static-libgcc
-B/usr/lib/gcc-lib/i386-redhat-linux/2.96/ "$@"
同样，将g++296的脚本的文件属性修改为755。
# chmod 755 gcc296
# chmod 755 g++296
下面根据gcc和g++的版本信息，将gcc和g++重命名。然后将gcc和g++分别指向gcc296和g++296。
# mv gcc gcc346
# mv g++ g++346
# ln -s -f gcc296 gcc
# ln -s -f g++296 g++
下面回到oracle用户环境，重新编译刚才出现错误的两个对象：
通过/opt/oracle/product/9.2/install/make.log文件，找到刚才出现错误的两个对象，重新make：
# su - oracle
$ cd /oracle/app/oracle/product/9204/sqlplus/lib
$ /usr/bin/make -f ins_sqlplus.mk install_isqlplus
ORACLE_HOME=/oracle/app/oracle/product/9204
$ cd /oracle/app/oracle/product/9204/rdbms/lib
$ make -f
/oracle/app/oracle/product/9204/rdbms/lib/ins_rdbms.mk
/oracle/app/oracle/product/9204/rdbms/lib/extproc32
EXTPROC=/oracle/app/oracle/product/9204/rdbms/lib/extproc32
LIBDIR=lib32 LDFLAGS='-m32 -o $@ $(LDPATHFLAG)$(PRODLIBHOME)
$(LDPATHFLAG)$(LIBHOME) $(LDPATHFLAG)$(LIBHOME)stubs/'
重新编译后，回到root用户，恢复刚才进行的操作：
$ exit
# rm -f gcc
# rm -f g++
# mv gcc346 gcc
# mv g++346 g++
# . /opt/oracle/product/9.2/root.sh
至此，Oracle9204的软件部分安装完毕。
--------------------------------------------------------------------------
安装9208补丁 然后建库，就不需要dbua了。比较简单 ，这里就不再阐述
—————————————————————————————————————
先建库 然后 升级软件 ，然后升级库(参照 9208的redme)
记住：在生产环境 要严格按照redme中的步骤，每步都认真检查。
我们这里就略过了检查组件的步骤，因为我们做的最简化的安装，很多组键都没有安装
也没有无效对象。
Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
Review the patch.log file for errors and inspect the list of
components that is displayed at the end of catpatch.sql
script.
This list provides the version and status of each SERVER
component in the database.
If necessary, rerun the catpatch.sql script after correcting
any problems.
Restart the database:
SQL> SHUTDOWN
SQL> STARTUP
Run the utlrp.sql script to recompile all invalid PL/SQL
packages now instead of when the packages are accessed for the
first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
—————————————————————————————————————————————————
11g软件挂9208库升级。
注：由于使用dbua出问题，我们直接使用的手工跑脚本升级
在生产环境要按照MOS文章仔细检查有关信息，我们是实验环境，需要检测的比较少。
将做好的9208的环境--软件以及库文件 做成tar包，传到我们准备好的11g
的环境
11g
rac的环境，最好事先不要建库。安装目录位置要和9208的区分开，用户可以使用oracle，如果怕区分不了，可以两套环境使用不同的用户。
(由于我们的物理机内存有限，为了试验顺利，我们使用的单节点的RAC做试验。)
将9208解压到 和原来环境相同的位置，权限设置好。
手工export ORACLE_HOME PATH
进行9208的库打开关闭测试。
开始前期准备：
参照mos文章：
《Complete Checklist for Manual Upgrades to 11gR2 [ID
837570.1]》
《Complete Checklist to Upgrade the Database to 11gR2 using
DBUA [ID 870814.1]》
根据提示：
执行检测脚本：
将11.2.0.x
$ORACLE_HOME/rdbms/admin/utlu112i.sql拷贝到临时位置/tmp，在sqlplus中执行(这里是9i的库)
sqlplus "/as sysdba"
spool upgrade_info.log
@/tmp/utlu112i.sql
spool off;
按照upgrade_info.log中的提示操作，为升级做准备
a)增加相应表空间数据文件的大小(按具体情况修改到合适大小)
alter database datafile
'/oracle/app/oracle/oradata/bsrdb/system01.dbf' resize 600M;
alter database datafile
'/oracle/app/oracle/oradata/bsrdb/undotbs01.dbf' resize 600M;
alter database tempfile
'/oracle/app/oracle/oradata/bsrdb/temp01.dbf' resize
100M;
搜索具有connect角色的用户
SQL> SELECT grantee FROM dba_role_privs
WHERE granted_role = 'CONNECT' and
grantee NOT IN (
'SYS',
'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS',
'WKPROXY',
'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS',
'DMSYS',
'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');
GRANTEE
--------------------------------------------------------------------------------
HR
SH
2 rows selected.
b)查看connect角色具有的权限
set linesize 150;
column grantee format a10;
column privilege format a60;
SELECT GRANTEE,PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
GRANTEE ='CONNECT';
GRANTEE PRIVILEGE
----------
------------------------------------------------------------
CONNECT CREATE VIEW
CONNECT CREATE
TABLE
CONNECT ALTER
SESSION
CONNECT CREATE
CLUSTER
CONNECT CREATE
SESSION
CONNECT CREATE
SYNONYM
CONNECT CREATE
SEQUENCE
CONNECT CREATE DATABASE
LINK
8 rows selected.
____________________________________________________________
set linesize 150;
column grantee format a10;
column privilege format a60;
SELECT GRANTEE,PRIVILEGE
FROM
DBA_SYS_PRIVS
WHERE
GRANTEE ='CONNECT';
GRANTEE PRIVILEGE
----------
------------------------------------------------------------
CONNECT CREATE VIEW
CONNECT CREATE
TABLE
CONNECT ALTER
SESSION
CONNECT CREATE
CLUSTER
CONNECT CREATE
SESSION
CONNECT CREATE
SYNONYM
CONNECT CREATE
SEQUENCE
CONNECT CREATE DATABASE
LINK
8 rows selected.
SQL>
_________________________________________________________________
c)sqlplus中使用sysdba身份执行：
EXECUTE
dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',-
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt=>'FOR ALL COLUMNS SIZE
AUTO',-
cascade=>TRUE);
EXECUTE
dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',-
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt=>'FOR ALL COLUMNS SIZE
AUTO',-
cascade=>TRUE);
EXECUTE
dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',-
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt=>'FOR ALL COLUMNS SIZE
AUTO',-
cascade=>TRUE);
EXECUTE
dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',-
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt=>'FOR ALL COLUMNS SIZE
AUTO',-
cascade=>TRUE);
EXECUTE
dbms_stats.gather_schema_stats('SYS',options=>'GATHER',-
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
method_opt=>'FOR ALL COLUMNS SIZE
AUTO',-
cascade=>TRUE);
d)创建sysaux表空间
CREATE TABLESPACE SYSAUX
DATAFILE
'/oracle/app/oracle/oradata/bsrdb/sysaux01.dbf'
SIZE
600M REUSE
EXTENT
MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
2)查看是否存在无效对象
spool invalid_obj.log;
select owner||'.'||object_name,object_type type,status from
dba_objects where status
<>'VALID';
spool off;
或
Select substr(owner,1,12) owner, substr(object_name,1,30)
object, Substr(object_type,1,30) type,status from dba_objects where
status
<>'VALID';
如果存在无效对象执行utlrp.sql重新编译
@?/rdbms/admin/utlrp.sql
3)查看9.2.0.8 数据库中安装的组件和版本
set linesize 200;
column comp_name format a60;
column version format a25;
column status format a10;
select comp_name,version,status from dba_registry;
4)生成pfile文件，修改pfile文件为11.2.0.x的参数，默认在9.2.0.8
rdbms的$ORACLE_HOME/dbs目录下init.ora
修改shared_pool_size参数为
*.shared_pool_size=524288000
*.compatible='11.2.0.0.0'
删除过时参数：
hash_join_enabled
background_dump_dest
user_dump_dest
后面两个参数由diagnostic_dest替代
添加11.2.0.x的参数
*.diagnostic_dest='/u01/app/oracle'
将修改好的参数文件和密码文件orapw
拷贝到11.2.0.x rdbms的$ORACLE_HOME/dbs目录下
4)关闭监听，正常关闭9.2.0.8的数据库
6.升级数据库
1)编辑/etc/oratab文件，
bsrdb:/u01/app/oracle/product/11.2.0/db_1:N
2)使用oracle 11.2.0.x的用户登录，sqlplus中以upgrade模式启动数据库进行升级
sqlplus / as sysdba
spool upgrade.log
startup upgrade;
@?/rdbms/admin/catupgrd.sql
以上脚本执行完会自动关闭数据库、退出sqlplus。检查upgrade.log是否有报错
3)启动数据库，查看升级后，数据库中安装组件的版本和状态
sqlplus / as sysdba
startup
spool utlu112s.log
@?/rdbms/admin/utlu112s.sql
spool off
或者
SQL> set linesize 200;
column comp_name format a60;
column version format a25;
column status format a10;
select comp_name,version,status from dba_registry
COMP_NAME VERSION
STATUS
------------------------------------------------------------
------------------------- ----------
Oracle Workspace Manager 11.2.0.1.0 VALID
Oracle Database Catalog Views 11.2.0.1.0
VALID
Oracle Database Packages and Types 11.2.0.1.0
VALID
SQL>
4)执行utlrp.sq脚本，重新编译无效对象
@?/rdbms/admin/utlrp.sql
确认是否还有无效对象
spool invalid_obj.log;
Select owner||'.'||object_name,object_type type,status from
dba_objects where status
<>'VALID';
spool off;
5)重新创建监听或将9.2.0.8 中的listener.ora文件拷贝到11.2.0.x中
5)检查connect角色具有的权限
SQL> set linesize 150;
SQL> column grantee format a10;
SQL> column privilege format a60;
SQL> SELECT GRANTEE,PRIVILEGE
2 FROM
DBA_SYS_PRIVS
3 WHERE GRANTEE ='CONNECT';
GRANTEE PRIVILEGE
----------
------------------------------------------------------------
CONNECT CREATE
SESSION
恢复应用HR,SH用户原有的权限
GRANT CREATE VIEW TO HR,SH;
GRANT CREATE TABLE TO HR,SH;
GRANT ALTER SESSION TO HR,SH;
GRANT CREATE CLUSTER TO HR,SH;
GRANT CREATE SESSION TO HR,SH;
GRANT CREATE SYNONYM TO HR,SH;
GRANT CREATE SEQUENCE TO HR,SH;
GRANT CREATE DATABASE LINK TO HR,SH;
到此为止9208库已经升级到11201
------------------------------------------------------------------------
将单机文件的库转成RAC ASM的库
参照官方文档：
《Converting to Oracle RAC and Oracle RAC One Node from
Single-Instance Oracle Databases》
我们有两种方法：图形化工具和手工(建议手工)
——————————————————————————————————————————
使用的图形化的操作：
使用oracle自带的模板功能，首先我们创建模板(根据上述官档中 C.2.1.1)
C.2.1.1 Back Up the Original Single-Instance Database
Use DBCA to create a preconfigured image of your
single-instance database by using the following procedure:
Navigate to the bin directory in $ORACLE_HOME, and start
DBCA.
At the Welcome page, click Next.
On the Operations page, select Manage Templates, and click
Next.
On the Template Management page, select Create a database
template and From an existing database (structure as well as data),
and click Next.
On the Source Database page, select the database name in the
Database instance list, and click Next.
On the Template Properties page, enter a name for your
template in the Name field. Oracle recommends that you use the
database name.
By default, the template files are generated in the directory
ORACLE_HOME/assistants/dbca/templates If you choose to do so, you
can enter a description of the file in the Description field, and
change the template file location in the Template data file
field.
When you have completed the entries, click Next.
On the Location of Database Related Files page, select
Maintain the file locations, so that you can restore the database
to the current directory structure, and click Finish.
DBCA generates two files: a database structure file
(template_name.dbc), and a database preconfigured image file
(template_name.dfb).
然后再使用DBCA进行创建ASM库，导入模板(事先准备一个待用的ASM磁盘组)
然后dbca导入，事先要将spfile创建出来
dbca
→“create database”
→ 选择我们之前创建的模板点击 “NEXT”
→ 填写“global database
name”(我们之前的叫bsrdb)，选择节点，因为我们的是单节点的，所以选择一个就行，点击“NEXT”
→取消安装EM，“NEXT”
→设置密码，“NEXT”
→选择“ASM的管理方式”
→填写“database area”，我们准备的ASM磁盘组名字为 +DATA，点击“NEXT”。
→“next”
→“next”
→“character sets”选择和以前的库相同的，点击“NEXT”
→“NEXT”
→“finish”
等待完成，实验到此结束
PRCC-1000 parameter spfile value is not valid
—————————————————————————————————————————————————
手工迁移：
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 27 18:11:36
2013
Copyright (c) 1982, 2009, Oracle. All rights
reserved.
Connected to an idle instance.
SQL> create spfile='+DATA' from pfile;
File created.
SQL>
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jan
27 18:13:37 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 1219334144 bytes
Fixed Size 2212816 bytes
Variable Size 553651248
bytes
Database Buffers 654311424 bytes
Redo Buffers 9158656 bytes
RMAN>
RMAN> restore controlfile to '+DATA' from
'/oracle/app/oracle/oradata/bsrdb/control01.ctl'
2> ;
Starting restore at 27-JAN-13
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 27-JAN-13
RMAN>
SQL> alter system set
control_files='+DATA/bsrdb/CONTROLFILE/current.258.805836681'
scope=spfile;
System altered.
SQL>
查看现有的表空间名称
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
EXAMPLE
INDX
TOOLS
USERS
SYSAUX
8 rows selected.
SQL>
修改$ORACLE_HOME/dbs/initsid.ora 文件
spfile='spfile_path_name'
SQL> startup
ORACLE instance started.
Total System Global Area 1219334144 bytes
Fixed Size 2212816 bytes
Variable Size 553651248 bytes
Database Buffers 654311424
bytes
Redo Buffers 9158656
bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> show parameters pfile
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
spfile string +DATA/db_unknown/parameterfile
/spfile.256.805831941
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter system set CLUSTER_DATABASE=true
scope=spfile;
SQL> alter system set INSTANCE_NUMBER=1
scope=spfile sid='bsrdb1';
System altered.
SQL>
SQL> alter system set
undo_tablespace='UNDOTBS1' scope=spfile sid='bsrdb1';
System altered.
SQL>
SQL> alter system set thread=1 scope=spfile
sid='bsrdb1';
System altered.
SQL>
(注：以上参数也可以生成一个pfile之后，直接修改文件，然后启动生效。)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@rac1 ~]$ export ORACLE_SID=bsrdb1
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 27 18:59:39
2013
Copyright (c) 1982, 2009, Oracle. All rights
reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1219334144 bytes
Fixed Size 2212816 bytes
Variable Size 553651248 bytes
Database Buffers 654311424
bytes
Redo Buffers 9158656
bytes
Database mounted.
Database opened.
SQL>
创建rac相关数据字典视图
SQL> @?/rdbms/admin/catclust.sql
将数据文件导入asm
startup mount
rman target /
rman >run
{
allocate channel c1 type disk;
backup as copy database format
'+DATA';
}
rman>switch database to
copy;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/bsrdb/datafile/system.257.805836955
+DATA/bsrdb/datafile/undotbs1.259.805837041
+DATA/bsrdb/datafile/example.261.805837581
+DATA/bsrdb/datafile/indx.262.805837657
+DATA/bsrdb/datafile/tools.264.805837709
+DATA/bsrdb/datafile/users.263.805837683
+DATA/bsrdb/datafile/sysaux.260.805837235
7 rows selected.
SQL>
sql>alter database open;
创建新的临时表空间文件，并删除旧的
alter tablespace temp add tempfile '+DATA' size 200M;
alter database tempfile
'/oracle/app/oracle/oradata/bsrdb/temp01.dbf' drop;
sql>alter database
open;
把redo添加新的成员，删除老的成员，
查看原来有多少组，就添加多少成员
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/app/oracle/oradata/bsrdb/redo01.log
/oracle/app/oracle/oradata/bsrdb/redo02.log
/oracle/app/oracle/oradata/bsrdb/redo03.log
SQL>
alter database add logfile member '+DATA' to
group 1;
alter database add logfile member '+DATA' to
group 2;
alter database add logfile member '+DATA' to
group 3;
初始化新加的logfile
alter system switch logfile;(多执行几次)
删除旧的:
alter database drop logfile member
'/oracle/app/oracle/oradata/bsrdb/redo01.log'
alter database drop logfile member
'/oracle/app/oracle/oradata/bsrdb/redo02.log'
alter database drop
logfile member '/oracle/app/oracle/oradata/bsrdb/redo03.log'
SQL>
select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/bsrdb/onlinelog/group_1.268.805838337
+DATA/bsrdb/onlinelog/group_2.269.805838405
+DATA/bsrdb/onlinelog/group_3.270.805838685
SQL>
SQL> alter system set
remote_listener='rac1:1521' ;
System altered.
SQL> alter system set
LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1vip)(PORT=1521))))'
sid='bsrdb1';
System altered.
SQL> alter system set instance_name='bsrdb1'
scope=spfile sid='bsrdb1';
System altered.
SQL>
[oracle@rac1 grid]$ srvctl add database -d bsrdb -o
/u01/app/oracle/product/11.2.0/db_2
[oracle@rac1 grid]$ srvctl add instance -d bsrdb -i bsrdb1 -n
rac1
[oracle@rac1 grid]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac1
ora....VOTE.dg ora....up.type ONLINE ONLINE rac1
ora.asm ora.asm.type
ONLINE ONLINE rac1
ora.bsrdb.db ora....se.type OFFLINE
OFFLINE
ora.eons ora.eons.type ONLINE
ONLINE rac1
ora.gsd ora.gsd.type
OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type OFFLINE
OFFLINE
ora.ons ora.ons.type
ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE
ONLINE rac1
ora....ry.acfs ora....fs.type ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type ONLINE
ONLINE rac1
[oracle@rac1 grid]$
srvctl start database -d bsrdb
[oracle@rac1 grid]$ srvctl status instance
-d bsrdb -i bsrdb1
Instance bsrdb1 is running on node rac1
[oracle@rac1 grid]$
创建密码文件
[oracle@rac1 dbs]$ orapwd
file=/u01/app/oracle/product/11.2.0/db_2/dbs/orapwbsrdb1
entries=5
1174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
