步骤:1、rac安装数据库软件,asm实例,clusterware软件(不安装数据库),参照文档
2、rman备份单节点数据库,并拷贝到rac
3. 在RAC 上还原并修改初始化参数文件,还原控制文件和数据文件
4. 增加undo 表空间和redo log 线程组,创建密钥文件
5. 配置RAC监听
6. 将数据库等资源添加到CRS
rac:版本:10.2.0.4
192.168.75.111 yutian1.localdomain yutian1
192.168.75.112 yutian2.localdomain yutian2
192.168.80.111 yutian1-priv.localdomain yutian1-priv
192.168.80.112 yutian2-priv.localdomain yutian2-priv
192.168.75.121 yutian1-vip.localdomain yutian1-vip
192.168.75.122 yutian2-vip.localdomain yutian2-vip
sid:yutian1
sid:yutian2
单节点:
192.168.75.131 dcw.localdomain dcw
db_name:dcw
oracle_sid:dcw
1、rac安装数据库软件,asm实例,clusterware软件(不安装数据库),参照文档
2、rman备份单节点数据库,并拷贝到rac
为了确保前已成功,可以提前插入一条数据
SQL> create table dcw(name varchar2(10),location varchar2(20));
Table created.
SQL> insert into dcw values('dcw','yutian');
1 row created.
SQL> commit;
Commit complete.
备份:
run{
allocate channel c0 device type disk format "/u01/backup/%U";
backup database plus archivelog;
release channel c0;
}
using target database control file instead of recovery catalog
allocated channel: c0
channel c0: sid=143 devtype=DISK
Starting backup at 01-APR-15
current log archived
channel c0: starting archive log backupset
channel c0: specifying archive log(s) in backup set
input archive log thread=1 sequence=33 recid=1 stamp=875852860
input archive log thread=1 sequence=34 recid=2 stamp=875852894
input archive log thread=1 sequence=35 recid=3 stamp=875852918
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/01q38t3n_1_1 tag=TAG20150401T042839 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:08
Finished backup at 01-APR-15
Starting backup at 01-APR-15
channel c0: starting full datafile backupset
channel c0: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/product/10.2.0/oradata/dcw/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/product/10.2.0/oradata/dcw/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/product/10.2.0/oradata/dcw/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/product/10.2.0/oradata/dcw/users01.dbf
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/02q38t40_1_1 tag=TAG20150401T042847 comment=NONE
channel c0: backup set complete, elapsed time: 00:01:35
channel c0: starting full datafile backupset
channel c0: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/03q38t6v_1_1 tag=TAG20150401T042847 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:06
Finished backup at 01-APR-15
Starting backup at 01-APR-15
current log archived
channel c0: starting archive log backupset
channel c0: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=4 stamp=875853030
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/04q38t76_1_1 tag=TAG20150401T043030 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:02
Finished backup at 01-APR-15
released channel: c0
[oracle@dcw ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Apr 1 04:35:21 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DCW (DBID=3749110133)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B A A DISK 01-APR-15 1 1 NO TAG20150401T042839
2 B F A DISK 01-APR-15 1 1 NO TAG20150401T042847
3 B F A DISK 01-APR-15 1 1 NO TAG20150401T042847
4 B A A DISK 01-APR-15 1 1 NO TAG20150401T043030
[oracle@dcw backup]$ scp * 192.168.75.111:/u01/backup/
The authenticity of host '192.168.75.111 (192.168.75.111)' can't be established.
RSA key fingerprint is 07:55:d2:00:51:4a:2d:e3:c3:60:12:7c:fe:94:88:c3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.75.111' (RSA) to the list of known hosts.
oracle@192.168.75.111's password:
01q38t3n_1_1 100% 48MB 24.1MB/s 00:02
02q38t40_1_1 100% 817MB 8.4MB/s 01:37
03q38t6v_1_1 100% 6976KB 6.8MB/s 00:00
04q38t76_1_1 100% 32KB 32.0KB/s 00:00
1_33_875841721.dbf 100% 48MB 8.0MB/s 00:06
1_34_875841721.dbf 100% 8192 8.0KB/s 00:00
1_35_875841721.dbf 100% 8704 8.5KB/s 00:00
1_36_875841721.dbf 100% 31KB 30.5KB/s 00:00
[oracle@dcw backup]$
2.3 还原并修改初始化文件
2.3.1 还原spfile 到pfile
[oracle@yutian1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 05:24:28 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/db_1/dbs/inityutian1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 71305632 bytes
Database Buffers 79691776 bytes
Redo Buffers 6303744 bytes
RMAN> restore spfile to pfile '/u01/oracle/db_1/dbs/inityutian1.ora' from '/u01/backup/03q38t6v_1_1';
Starting restore at 03-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/backup/03q38t6v_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 03-APR-15
看一下我们刚才恢复的参数文件:
[oracle@yutian1 dbs]$ pwd
/u01/oracle/db_1/dbs
[oracle@yutian1 dbs]$ ls
ab_+ASM1.dat hc_+ASM1.dat hc_yutian1.dat init+ASM1.ora initdw.ora init.ora inityutian1.ora orapw+ASM1
[oracle@yutian1 dbs]$ cat inityutian1.ora
dcw.__db_cache_size=58720256
dcw.__java_pool_size=50331648
dcw.__large_pool_size=4194304
dcw.__shared_pool_size=163577856
dcw.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2.0/oradata/dcw/control01.ctl','/u01/app/oracle/product/10.2.0/oradata/dcw/control02.ctl','/u01/app/oracle/product/10.2.0/oradata/dcw/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dcw'
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcwXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/backup'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/udump'
这里面都是单实例的参数,我们需要把这个改成RAC的参数。
2.3.2 查看ASM 实例的相关目录信息
export ORACLE_SID=+ASM1
SQL> select state,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;
STATE REDUNDA TOTAL_MB FREE_MB NAME FAILGROUP
---------- ------- ---------- ---------- --------------- --------------------
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 2047 2011 VOL1 VOL1
NORMAL UNKNOWN 2047 2017 VOL2 VOL2
NORMAL UNKNOWN 2047 2016 VOL3 VOL3
8 rows selected.
SQL> select group_number,name,state,type,total_mb,free_mb,unbalanced from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB U
------------ --------------- ---------- ------ ---------- ---------- -
1 DATA MOUNTED EXTERN 6141 6044 N
[oracle@yutian1 dbs]$ asmcmd
ASMCMD> help
asmcmd [-p] [command]
The environment variables ORACLE_HOME and ORACLE_SID determine the
instance to which the program connects, and ASMCMD establishes a
bequeath connection to it, in the same manner as a SQLPLUS / AS
SYSDBA. The user must be a member of the SYSDBA group.
Specifying the -p option allows the current directory to be displayed
in the command prompt, like so:
ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >
[command] specifies one of the following commands, along with its
parameters.
Type "help [command]" to get help on a specific ASMCMD command.
commands:
--------
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
2.3.3 修改初始话参数
注意事项:
(1)RMAN 异机恢复的db_name 必须和备份的一致,如果说想改成其他名称,可以等还原之后,在用nid 命令修改。
(2)控制文件需要指定到共享设备上
(3)检查audit_file_dest,background_dump_dest, core_dump_dest,log_archive_dest_1,user_dump_dest等参数的位置。 如果2个节点和共享位置没有对应的目录,先把目录建好。
[oracle@yutian1 u01]$ cd admin/
[oracle@yutian1 admin]$ pwd
/u01/admin
[oracle@yutian1 admin]$ mkdir yutian
[oracle@yutian1 admin]$ ls
+ASM yutian
[oracle@yutian1 admin]$ cd yutian/
[oracle@yutian1 yutian]$ mkdir adump
[oracle@yutian1 yutian]$ mkdir bdump
[oracle@yutian1 yutian]$ mkdir cdump
[oracle@yutian1 yutian]$ mkdir dpdump
[oracle@yutian1 yutian]$ mkdir hdump
[oracle@yutian1 yutian]$ mkdir udump
[oracle@yutian1 yutian]$ mkdir pfile
[oracle@yutian1 yutian]$ ll
total 24
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 adump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 bdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 cdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:56 hdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:56 pfile
官网文档说明需要添加如下参数:
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
<SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
<SID1>.instance_name=<SID1>
<SID1>.instance_number=1
<SID1>.thread=1
<SID1>.local_listener=<LISTENERNAME>_<HOSTNAME1>
<SID2>.instance_name=<SID2>
<SID2>.instance_number=2
<SID2>.local_listener=<LISTENERNAME>_<HOSTNAME2>
<SID2>.thread=2
<SID2>.undo_tablespace=UNDOTBS2
<SID2>.cluster_database = TRUE
<SID2>.cluster_database_instances = 2
最终我们修改之后的pfile 文件如下:
[oracle@yutian1 dbs]$ vim inityutian1.ora
*.audit_file_dest='/u01/admin/yutian/adump'
*.background_dump_dest='/u01/admin/yutian/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.2.0.4.0'
*.control_files='+DATA/control01.ctl','+DATA/control02.ctl'
*.core_dump_dest='/u01/admin/yutian/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='yutian'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
yutian1.instance_name='yutian1'
yutian2.instance_name='yutian2'
yutian1.instance_number=1
yutian2.instance_number=2
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/backup'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=285212672
yutian1.thread=1
yutian2.thread=2
*.undo_management='AUTO'
yutian1.undo_tablespace='UNDOTBS1'
yutian2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/admin/yutian/udump'
2.3.4 用修改的pfile 来创建spfile,注意放在共享设备上
[oracle@yutian1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 10:25:16 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/spfileyutian.ora' from pfile='/u01/oracle/db_1/dbs/inityutian1.ora';
File created.
SQL> exit
Disconnected
[oracle@yutian1 ~]$ export ORACLE_SID=+ASM1
[oracle@yutian1 ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
spfileyutian.ora
在所有节点上,修改pfile内容,将其指向共享设备上的spfile:
[oracle@yutian1 dbs]$ echo "SPFILE='+DATA/spfileyutian.ora'" > /u01/oracle/db_1/dbs/inityutian1.ora
[oracle@yutian1 dbs]$ cat inityutian1.ora
SPFILE='+DATA/spfileyutian.ora'
[oracle@yutian2 dbs]$ echo "SPFILE='+DATA/spfileyutian.ora'" > /u01/oracle/db_1/dbs/inityutian2.ora
[oracle@yutian2 dbs]$ cat inityutian2.ora
SPFILE='+DATA/spfileyutian.ora'
2.4 创建口令文件
在所有节点执行:
[oracle@yutian1 dbs]$ orapwd file=/u01/oracle/db_1/dbs/orapwyutian1 password=oracle
[oracle@yutian2 dbs]$ orapwd file=/u01/oracle/db_1/dbs/orapwyutian2 password=oracle
2.5 还原控制文件
在其中一个节点上执行。
2.5.1 用spfile,将DB 启动到nomount 状态
[oracle@yutian1 yutian]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 10:56:22 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfileyutian.ora
2.5.2 RMAN 执行对控制文件的恢复
[oracle@yutian1 yutian]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 11:03:27 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: yutian (not mounted)
RMAN> restore controlfile from '/u01/backup/03q38t6v_1_1';
Starting restore at 03-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 instance=yutian1 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATA/control01.ctl
output filename=+DATA/control02.ctl
Finished restore at 03-APR-15
这个位置是我们在初始化参数里指定的,到ASM 实例里验证下:
[oracle@yutian1 backup]$ export ORACLE_SID=+ASM1
[oracle@yutian1 backup]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> cd YUTIAN
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
current.257.876049549
current.258.876049551
ASMCMD> cd
usage: cd <dir>
ASMCMD> ls
current.257.876049549
current.258.876049551
ASMCMD> pwd
+DATA/YUTIAN/CONTROLFILE
ASMCMD> cd ../
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd ../
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> cd DB_UNKNOWN
ASMCMD> ls
PARAMETERFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls
SPFILE.256.876047125
这里除了我们指定的,还自动生成了一个,这个control02.ctl 是我们current.258.876049551 文件的一个别名。 我们确认一下:
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
Y YUTIAN/
N control01.ctl => +DATA/YUTIAN/CONTROLFILE/current.257.876049549
N control02.ctl => +DATA/YUTIAN/CONTROLFILE/current.258.876049551
N spfileyutian.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.876047125
在创建db时系统自动创建的几个表空间(system,undotbs,sysaux,users)对应的都是真实的数据文件,即ASM 文件默认的命名格式。而且这个信息都写到了控制文件里。如果我们使用别名的话,
会方便很多。 对于这些创建数据库时自动创建的表空间,我们要他们使用别名,除了手工创建对应别名外,还需要重建控制文件,并且在重建时,datafile 里写别名的信息。 这样数据库也就使用
别名了。
对这个现象的一个说明: ASM 有自己的文件固定格式,如果我们在指定参数或者文件名时,指定了具体的文件名,那么这个文件名会以别名的方式指向ASM中的文件。
2.6 restore数据库
在其中一个节点执行
2.6.1 将数据库启动到MOUNT状态
[oracle@yutian1 yutian]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 11:15:20 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'DCW' in control file is not 'YUTIAN'
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string yutian
db_unique_name string yutian
global_names boolean FALSE
instance_name string yutian1
lock_name_space string
log_file_name_convert string
service_names string yutian
SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
中间把/u01/oracle/db_1/dbs/inityutian1.ora.1中的数据库名字改为dcw
SQL> create spfile='+DATA/spfileyutian.ora' from pfile='/u01/oracle/db_1/dbs/inityutian1.ora.1';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string dcw
db_unique_name string dcw
global_names boolean FALSE
instance_name string yutian1
lock_name_space string
log_file_name_convert string
service_names string dcw
SQL> alter database mount;
Database altered.
2.6.2 查看源库数据文件存储位置信息
注意一点,我们单实例和RAC 实例存储数据文件的位置不同,如果我们使用rman 的duplicate,那么我们使用log_file_name_convert 和 db_file_name_convert来进行转换,在这个实验中,
我们使用的是RMAN的异机恢复,所以只能在restore的时候用set newname来进行转换。
SQL> set line 80
SQL> col file_name for a60
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
4 /u01/app/oracle/product/10.2.0/oradata/dcw/users01.dbf
3 /u01/app/oracle/product/10.2.0/oradata/dcw/sysaux01.dbf
2 /u01/app/oracle/product/10.2.0/oradata/dcw/undotbs01.dbf
1 /u01/app/oracle/product/10.2.0/oradata/dcw/system01.dbf
SQL> select file_id,file_name from dba_temp_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/product/10.2.0/oradata/dcw/temp01.dbf
2.6.3 在RAC上restore 数据文件
run {
set newname for datafile 1 to '+DATA/system01.dbf';
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 3 to '+DATA/sysaux01.dbf';
set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
注意:
(1)我们datafile 3 和 4的区别,datafile 4 我没有指定具体的文件名,那么在还原时会使用ASM 自己的命名方式来命名datafile4. 其他的文件会创建别名。
(2)对switch的说明:
对于nocatalog 模式下,rman备份的信息是保存在控制文件里的,包括文件的路径信息。 这里的switch的作用,就是更新控制文件里的信息。
(3)restore 的时候不会对temp 表空间进行restore。所以等restore 之后,我们需要手工创建temp表空间。
不过在这个测试里,我们还是对tempfile 进行了指定。 但是这个操作只更新控制文件,不恢复数据文件。
run {
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 1 to '+DATA/system01.dbf';
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 3 to '+DATA/sysaux01.dbf';
5> set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA/temp01.dbf';
7> restore database;
8> switch datafile all;
9> switch tempfile all;
10> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 03-APR-15
Starting implicit crosscheck backup at 03-APR-15
allocated channel: ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck backup at 03-APR-15
Starting implicit crosscheck copy at 03-APR-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-APR-15
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/system01.dbf
restoring datafile 00002 to +DATA/undotbs01.dbf
restoring datafile 00003 to +DATA/sysaux01.dbf
restoring datafile 00004 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/backup/02q38t40_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/02q38t40_1_1 tag=TAG20150401T042847
channel ORA_DISK_1: restore complete, elapsed time: 00:03:47
Finished restore at 03-APR-15
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=876052048 filename=+DATA/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=876052049 filename=+DATA/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=876052049 filename=+DATA/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=876052049 filename=+DATA/dcw/datafile/users.262.876051821
renamed temporary file 1 to +DATA/temp01.dbf in control file
-- 从这里可以看到,对tempfile 仅仅是rename。即更新控制文件。
RMAN>
[oracle@yutian1 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
undotbs01.dbf
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
Y DCW/
Y YUTIAN/
N control01.ctl => +DATA/YUTIAN/CONTROLFILE/current.257.876049549
N control02.ctl => +DATA/YUTIAN/CONTROLFILE/current.258.876049551
N spfileyutian.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.876051011
N sysaux01.dbf => +DATA/DCW/DATAFILE/SYSAUX.261.876051819
N system01.dbf => +DATA/DCW/DATAFILE/SYSTEM.259.876051819
N undotbs01.dbf => +DATA/DCW/DATAFILE/UNDOTBS1.260.876051819
ASMCMD> cd DCW
ASMCMD> ls
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.261.876051819
SYSTEM.259.876051819
UNDOTBS1.260.876051819
USERS.262.876051821
ASMCMD> cd ../
ASMCMD> ls
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.261.876051819
SYSTEM.259.876051819
UNDOTBS1.260.876051819
USERS.262.876051821
ASMCMD> cd ../../
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
undotbs01.dbf
ASMCMD> cd YUTIAN
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
current.257.876049549
current.258.876049551
2.8 recover 数据库
在执行restore的节点执行,因为备份文件在该节点上。
RMAN> recover database;
Starting recover at 03-APR-15
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=36
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/03/2015 11:59:40
RMAN-06054: media recovery requesting unknown log: thread 1 seq 36 lowscn 940781
最后一行的错误说明:
RMAN-06054: media recovery requesting unknown log: thread 1 seq 36 lowscn 940781
这里是提醒恢复到一个未知的scn号。在alter database mount之后,通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。
2.9 处理online redo
因为此时另一个节点还没有配好,所以我们只能在执行恢复的节点上处理redo。
SQL> set wrap off
SQL> select * from v$logfile;
rows will be truncated
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo03.log
2 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo02.log
1 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo01.log
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo01.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo02.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo03.log' to '+DATA';
Database altered.
SQL> select * from v$logfile;
rows will be truncated
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE +DATA
2 ONLINE +DATA
1 ONLINE +DATA
2.10 open resetlogs 打开DB
在恢复的节点执行该操作。
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
DCW READ WRITE
SQL> select comp_name,version,status from sys.dba_registry;
rows will be truncated
rows will be truncated
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rule Manager
COMP_NAME
--------------------------------------------------------------------------------
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
17 rows selected.
2.11 检查并修改几个初始化参数
SQL> select * from v$option where parameter='Real Application Clusters';
PARAMETER VALUE
---------------------------------------------------------------- ---------------
Real Application Clusters TRUE
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter thread
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
SQL> show parameter instance_number
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
由上述返回结果可知,RAC特性是支持的,不过尚未启用集群数据库,因此接下来首先要改的,就是enable CLUSTER DATABASE,操作如下:
修改2个节点的配置参数:
SQL> alter system set instance_number=1 scope=spfile sid='yutian1';
System altered.
SQL> alter system set instance_number=2 scope=spfile sid='yutian2';
System altered.
SQL> alter system set thread=1 scope=spfile sid='yutian1';
System altered.
SQL> alter system set thread=2 scope=spfile sid='yutian2';
System altered.
2.12 创建节点2的undo 表空间
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
这个是rac1上节点的信息,我们在之前的在spfile参数指定的rac2节点的undo 空间是UNDOTBS2.
所以这里我们创建该undo 表空间,并指定相关参数。
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/undotbs02.dbf' size 200M;
Tablespace created.
--这里我们直接指定别名
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
temp01.dbf
undotbs01.dbf
undotbs02.dbf
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='yutian2';
System altered.
2.13 添加rac2 节点的redo 文件
rac 的redo 是接其他节点来的,我们之前的rac1上已经有3组,所以我们这里从4开始,在添加2组给rac2,使用thread 2.
SQL> alter database add logfile thread 2 group 4 '+DATA' size 100M;
Database altered.
SQL> alter database add logfile thread 2 group 5 '+DATA' size 100M;
Database altered.
SQL> alter database enable thread 2;
Database altered.
2.14 重启节点1和节点2 上的实例,使相关参数生效
节点1:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 100664792 bytes
Database Buffers 176160768 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
节点2:
[oracle@yutian2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 12:51:50 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
最后确认:
SQL> select instance_number,instance_name,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- -----------------------------------------------
1 yutian1 yutian1.localdomain
2 yutian2 yutian2.localdomain
到现在已经是集群了,不过还有一些收尾的工作要做。
2.15 执行catclust.sql脚本来创建相关视图
$ORACLE_HOME/rdbms/admin/catclust.sql to create cluster database specific views within the existing instance
SQL>@$ORACLE_HOME/rdbms/admin/catclust.sql
2.16 重建Temp 表空间
在恢复的时候,会自动创建TEMP 表空间。和一个前辈聊到过这个问题,他说这个temp 文件好像没有起到作用。 尤其是数据量大的时候,他建议重建添加temp 数据文件。 这个还是小心为好,
反正重建一些也不费什么事。但是万一出问题,就是大麻烦。
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp01.dbf
SQL> alter tablespace temp add tempfile '+DATA/temp02.dbf' size 100M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp01.dbf
+DATA/temp02.dbf
SQL> alter database tempfile '+DATA/temp01.dbf' offline;
Database altered.
SQL> alter database tempfile '+DATA/temp01.dbf' drop including datafiles;
Database altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp02.dbf
rac2:
SQL> select instance_number,instance_name,host_name from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
2 yutian2 yutian2.localdomain
2.17 添加集群的监听
2.18 将其他信息注册到CRS里
不过这时候通过crs_stat -t查看的时候,还看不到新创建的repdb数据库信息:
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
[oracle@yutian1 dbs]$ srvctl add database -d dcw -o $ORACLE_HOME -p +DATA/spfileyutian.ora
[oracle@yutian1 dbs]$ srvctl add instance -d dcw -i yutian1 -n yutian1
[oracle@yutian1 dbs]$ srvctl add instance -d dcw -i yutian2 -n yutian2
[oracle@yutian1 dbs]$ srvctl modify instance -d dcw -i yutian1 -s +ASM1
[oracle@yutian1 dbs]$ srvctl modify instance -d dcw -i yutian2 -s +ASM2
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.dcw.db application OFFLINE OFFLINE
ora....n1.inst application OFFLINE OFFLINE
ora....n2.inst application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
这里并没有启动,因为我们的服务是刚添加上来的,还没有同步。 我们启动一下就ok了。
[oracle@yutian1 dbs]$ srvctl start database -d dcw
[oracle@yutian1 dbs]$ srvctl start -d dcw
Usage: srvctl <command> <object> [<options>]
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.dcw.db application ONLINE ONLINE yutian1
ora....n1.inst application ONLINE ONLINE yutian1
ora....n2.inst application ONLINE ONLINE yutian2
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
2.19 配置RAC 的Failover 和 Loadbalance
2.20 最后创建查看一下我们开始创建的表:
SQL> set wrap off
SQL> set linesize 80
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
yutian1
SQL> select * from dcw;
NAME LOCATION
---------- --------------------
dcw yutian
2、rman备份单节点数据库,并拷贝到rac
3. 在RAC 上还原并修改初始化参数文件,还原控制文件和数据文件
4. 增加undo 表空间和redo log 线程组,创建密钥文件
5. 配置RAC监听
6. 将数据库等资源添加到CRS
rac:版本:10.2.0.4
192.168.75.111 yutian1.localdomain yutian1
192.168.75.112 yutian2.localdomain yutian2
192.168.80.111 yutian1-priv.localdomain yutian1-priv
192.168.80.112 yutian2-priv.localdomain yutian2-priv
192.168.75.121 yutian1-vip.localdomain yutian1-vip
192.168.75.122 yutian2-vip.localdomain yutian2-vip
sid:yutian1
sid:yutian2
单节点:
192.168.75.131 dcw.localdomain dcw
db_name:dcw
oracle_sid:dcw
1、rac安装数据库软件,asm实例,clusterware软件(不安装数据库),参照文档
2、rman备份单节点数据库,并拷贝到rac
为了确保前已成功,可以提前插入一条数据
SQL> create table dcw(name varchar2(10),location varchar2(20));
Table created.
SQL> insert into dcw values('dcw','yutian');
1 row created.
SQL> commit;
Commit complete.
备份:
run{
allocate channel c0 device type disk format "/u01/backup/%U";
backup database plus archivelog;
release channel c0;
}
using target database control file instead of recovery catalog
allocated channel: c0
channel c0: sid=143 devtype=DISK
Starting backup at 01-APR-15
current log archived
channel c0: starting archive log backupset
channel c0: specifying archive log(s) in backup set
input archive log thread=1 sequence=33 recid=1 stamp=875852860
input archive log thread=1 sequence=34 recid=2 stamp=875852894
input archive log thread=1 sequence=35 recid=3 stamp=875852918
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/01q38t3n_1_1 tag=TAG20150401T042839 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:08
Finished backup at 01-APR-15
Starting backup at 01-APR-15
channel c0: starting full datafile backupset
channel c0: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/product/10.2.0/oradata/dcw/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/product/10.2.0/oradata/dcw/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/product/10.2.0/oradata/dcw/sysaux01.dbf
input datafile fno=00004 name=/u01/app/oracle/product/10.2.0/oradata/dcw/users01.dbf
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/02q38t40_1_1 tag=TAG20150401T042847 comment=NONE
channel c0: backup set complete, elapsed time: 00:01:35
channel c0: starting full datafile backupset
channel c0: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/03q38t6v_1_1 tag=TAG20150401T042847 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:06
Finished backup at 01-APR-15
Starting backup at 01-APR-15
current log archived
channel c0: starting archive log backupset
channel c0: specifying archive log(s) in backup set
input archive log thread=1 sequence=36 recid=4 stamp=875853030
channel c0: starting piece 1 at 01-APR-15
channel c0: finished piece 1 at 01-APR-15
piece handle=/u01/backup/04q38t76_1_1 tag=TAG20150401T043030 comment=NONE
channel c0: backup set complete, elapsed time: 00:00:02
Finished backup at 01-APR-15
released channel: c0
[oracle@dcw ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Apr 1 04:35:21 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DCW (DBID=3749110133)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B A A DISK 01-APR-15 1 1 NO TAG20150401T042839
2 B F A DISK 01-APR-15 1 1 NO TAG20150401T042847
3 B F A DISK 01-APR-15 1 1 NO TAG20150401T042847
4 B A A DISK 01-APR-15 1 1 NO TAG20150401T043030
[oracle@dcw backup]$ scp * 192.168.75.111:/u01/backup/
The authenticity of host '192.168.75.111 (192.168.75.111)' can't be established.
RSA key fingerprint is 07:55:d2:00:51:4a:2d:e3:c3:60:12:7c:fe:94:88:c3.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.75.111' (RSA) to the list of known hosts.
oracle@192.168.75.111's password:
01q38t3n_1_1 100% 48MB 24.1MB/s 00:02
02q38t40_1_1 100% 817MB 8.4MB/s 01:37
03q38t6v_1_1 100% 6976KB 6.8MB/s 00:00
04q38t76_1_1 100% 32KB 32.0KB/s 00:00
1_33_875841721.dbf 100% 48MB 8.0MB/s 00:06
1_34_875841721.dbf 100% 8192 8.0KB/s 00:00
1_35_875841721.dbf 100% 8704 8.5KB/s 00:00
1_36_875841721.dbf 100% 31KB 30.5KB/s 00:00
[oracle@dcw backup]$
2.3 还原并修改初始化文件
2.3.1 还原spfile 到pfile
[oracle@yutian1 ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 05:24:28 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/oracle/db_1/dbs/inityutian1.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 71305632 bytes
Database Buffers 79691776 bytes
Redo Buffers 6303744 bytes
RMAN> restore spfile to pfile '/u01/oracle/db_1/dbs/inityutian1.ora' from '/u01/backup/03q38t6v_1_1';
Starting restore at 03-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/backup/03q38t6v_1_1
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 03-APR-15
看一下我们刚才恢复的参数文件:
[oracle@yutian1 dbs]$ pwd
/u01/oracle/db_1/dbs
[oracle@yutian1 dbs]$ ls
ab_+ASM1.dat hc_+ASM1.dat hc_yutian1.dat init+ASM1.ora initdw.ora init.ora inityutian1.ora orapw+ASM1
[oracle@yutian1 dbs]$ cat inityutian1.ora
dcw.__db_cache_size=58720256
dcw.__java_pool_size=50331648
dcw.__large_pool_size=4194304
dcw.__shared_pool_size=163577856
dcw.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/adump'
*.background_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2.0/oradata/dcw/control01.ctl','/u01/app/oracle/product/10.2.0/oradata/dcw/control02.ctl','/u01/app/oracle/product/10.2.0/oradata/dcw/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dcw'
*.db_recovery_file_dest='/u01/app/oracle/product/10.2.0/db_1/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dcwXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/backup'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10.2.0/db_1/admin/dcw/udump'
这里面都是单实例的参数,我们需要把这个改成RAC的参数。
2.3.2 查看ASM 实例的相关目录信息
export ORACLE_SID=+ASM1
SQL> select state,redundancy,total_mb,free_mb,name,failgroup from v$asm_disk;
STATE REDUNDA TOTAL_MB FREE_MB NAME FAILGROUP
---------- ------- ---------- ---------- --------------- --------------------
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 101 0
NORMAL UNKNOWN 2047 2011 VOL1 VOL1
NORMAL UNKNOWN 2047 2017 VOL2 VOL2
NORMAL UNKNOWN 2047 2016 VOL3 VOL3
8 rows selected.
SQL> select group_number,name,state,type,total_mb,free_mb,unbalanced from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB U
------------ --------------- ---------- ------ ---------- ---------- -
1 DATA MOUNTED EXTERN 6141 6044 N
[oracle@yutian1 dbs]$ asmcmd
ASMCMD> help
asmcmd [-p] [command]
The environment variables ORACLE_HOME and ORACLE_SID determine the
instance to which the program connects, and ASMCMD establishes a
bequeath connection to it, in the same manner as a SQLPLUS / AS
SYSDBA. The user must be a member of the SYSDBA group.
Specifying the -p option allows the current directory to be displayed
in the command prompt, like so:
ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >
[command] specifies one of the following commands, along with its
parameters.
Type "help [command]" to get help on a specific ASMCMD command.
commands:
--------
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
2.3.3 修改初始话参数
注意事项:
(1)RMAN 异机恢复的db_name 必须和备份的一致,如果说想改成其他名称,可以等还原之后,在用nid 命令修改。
(2)控制文件需要指定到共享设备上
(3)检查audit_file_dest,background_dump_dest, core_dump_dest,log_archive_dest_1,user_dump_dest等参数的位置。 如果2个节点和共享位置没有对应的目录,先把目录建好。
[oracle@yutian1 u01]$ cd admin/
[oracle@yutian1 admin]$ pwd
/u01/admin
[oracle@yutian1 admin]$ mkdir yutian
[oracle@yutian1 admin]$ ls
+ASM yutian
[oracle@yutian1 admin]$ cd yutian/
[oracle@yutian1 yutian]$ mkdir adump
[oracle@yutian1 yutian]$ mkdir bdump
[oracle@yutian1 yutian]$ mkdir cdump
[oracle@yutian1 yutian]$ mkdir dpdump
[oracle@yutian1 yutian]$ mkdir hdump
[oracle@yutian1 yutian]$ mkdir udump
[oracle@yutian1 yutian]$ mkdir pfile
[oracle@yutian1 yutian]$ ll
total 24
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 adump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 bdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 cdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:55 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:56 hdump
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 06:56 pfile
官网文档说明需要添加如下参数:
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
<SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
<SID1>.instance_name=<SID1>
<SID1>.instance_number=1
<SID1>.thread=1
<SID1>.local_listener=<LISTENERNAME>_<HOSTNAME1>
<SID2>.instance_name=<SID2>
<SID2>.instance_number=2
<SID2>.local_listener=<LISTENERNAME>_<HOSTNAME2>
<SID2>.thread=2
<SID2>.undo_tablespace=UNDOTBS2
<SID2>.cluster_database = TRUE
<SID2>.cluster_database_instances = 2
最终我们修改之后的pfile 文件如下:
[oracle@yutian1 dbs]$ vim inityutian1.ora
*.audit_file_dest='/u01/admin/yutian/adump'
*.background_dump_dest='/u01/admin/yutian/bdump'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='10.2.0.4.0'
*.control_files='+DATA/control01.ctl','+DATA/control02.ctl'
*.core_dump_dest='/u01/admin/yutian/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='yutian'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racXDB)'
yutian1.instance_name='yutian1'
yutian2.instance_name='yutian2'
yutian1.instance_number=1
yutian2.instance_number=2
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/backup'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='exclusive'
*.sga_target=285212672
yutian1.thread=1
yutian2.thread=2
*.undo_management='AUTO'
yutian1.undo_tablespace='UNDOTBS1'
yutian2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/u01/admin/yutian/udump'
2.3.4 用修改的pfile 来创建spfile,注意放在共享设备上
[oracle@yutian1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 10:25:16 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/spfileyutian.ora' from pfile='/u01/oracle/db_1/dbs/inityutian1.ora';
File created.
SQL> exit
Disconnected
[oracle@yutian1 ~]$ export ORACLE_SID=+ASM1
[oracle@yutian1 ~]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
spfileyutian.ora
在所有节点上,修改pfile内容,将其指向共享设备上的spfile:
[oracle@yutian1 dbs]$ echo "SPFILE='+DATA/spfileyutian.ora'" > /u01/oracle/db_1/dbs/inityutian1.ora
[oracle@yutian1 dbs]$ cat inityutian1.ora
SPFILE='+DATA/spfileyutian.ora'
[oracle@yutian2 dbs]$ echo "SPFILE='+DATA/spfileyutian.ora'" > /u01/oracle/db_1/dbs/inityutian2.ora
[oracle@yutian2 dbs]$ cat inityutian2.ora
SPFILE='+DATA/spfileyutian.ora'
2.4 创建口令文件
在所有节点执行:
[oracle@yutian1 dbs]$ orapwd file=/u01/oracle/db_1/dbs/orapwyutian1 password=oracle
[oracle@yutian2 dbs]$ orapwd file=/u01/oracle/db_1/dbs/orapwyutian2 password=oracle
2.5 还原控制文件
在其中一个节点上执行。
2.5.1 用spfile,将DB 启动到nomount 状态
[oracle@yutian1 yutian]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 10:56:22 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfileyutian.ora
2.5.2 RMAN 执行对控制文件的恢复
[oracle@yutian1 yutian]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 11:03:27 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: yutian (not mounted)
RMAN> restore controlfile from '/u01/backup/03q38t6v_1_1';
Starting restore at 03-APR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 instance=yutian1 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
output filename=+DATA/control01.ctl
output filename=+DATA/control02.ctl
Finished restore at 03-APR-15
这个位置是我们在初始化参数里指定的,到ASM 实例里验证下:
[oracle@yutian1 backup]$ export ORACLE_SID=+ASM1
[oracle@yutian1 backup]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> cd YUTIAN
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
current.257.876049549
current.258.876049551
ASMCMD> cd
usage: cd <dir>
ASMCMD> ls
current.257.876049549
current.258.876049551
ASMCMD> pwd
+DATA/YUTIAN/CONTROLFILE
ASMCMD> cd ../
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd ../
ASMCMD> ls
DB_UNKNOWN/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
ASMCMD> cd DB_UNKNOWN
ASMCMD> ls
PARAMETERFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls
SPFILE.256.876047125
这里除了我们指定的,还自动生成了一个,这个control02.ctl 是我们current.258.876049551 文件的一个别名。 我们确认一下:
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
Y YUTIAN/
N control01.ctl => +DATA/YUTIAN/CONTROLFILE/current.257.876049549
N control02.ctl => +DATA/YUTIAN/CONTROLFILE/current.258.876049551
N spfileyutian.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.876047125
在创建db时系统自动创建的几个表空间(system,undotbs,sysaux,users)对应的都是真实的数据文件,即ASM 文件默认的命名格式。而且这个信息都写到了控制文件里。如果我们使用别名的话,
会方便很多。 对于这些创建数据库时自动创建的表空间,我们要他们使用别名,除了手工创建对应别名外,还需要重建控制文件,并且在重建时,datafile 里写别名的信息。 这样数据库也就使用
别名了。
对这个现象的一个说明: ASM 有自己的文件固定格式,如果我们在指定参数或者文件名时,指定了具体的文件名,那么这个文件名会以别名的方式指向ASM中的文件。
2.6 restore数据库
在其中一个节点执行
2.6.1 将数据库启动到MOUNT状态
[oracle@yutian1 yutian]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 11:15:20 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'DCW' in control file is not 'YUTIAN'
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string yutian
db_unique_name string yutian
global_names boolean FALSE
instance_name string yutian1
lock_name_space string
log_file_name_convert string
service_names string yutian
SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
中间把/u01/oracle/db_1/dbs/inityutian1.ora.1中的数据库名字改为dcw
SQL> create spfile='+DATA/spfileyutian.ora' from pfile='/u01/oracle/db_1/dbs/inityutian1.ora.1';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string dcw
db_unique_name string dcw
global_names boolean FALSE
instance_name string yutian1
lock_name_space string
log_file_name_convert string
service_names string dcw
SQL> alter database mount;
Database altered.
2.6.2 查看源库数据文件存储位置信息
注意一点,我们单实例和RAC 实例存储数据文件的位置不同,如果我们使用rman 的duplicate,那么我们使用log_file_name_convert 和 db_file_name_convert来进行转换,在这个实验中,
我们使用的是RMAN的异机恢复,所以只能在restore的时候用set newname来进行转换。
SQL> set line 80
SQL> col file_name for a60
SQL> select file_id,file_name from dba_data_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
4 /u01/app/oracle/product/10.2.0/oradata/dcw/users01.dbf
3 /u01/app/oracle/product/10.2.0/oradata/dcw/sysaux01.dbf
2 /u01/app/oracle/product/10.2.0/oradata/dcw/undotbs01.dbf
1 /u01/app/oracle/product/10.2.0/oradata/dcw/system01.dbf
SQL> select file_id,file_name from dba_temp_files;
FILE_ID FILE_NAME
---------- ------------------------------------------------------------
1 /u01/app/oracle/product/10.2.0/oradata/dcw/temp01.dbf
2.6.3 在RAC上restore 数据文件
run {
set newname for datafile 1 to '+DATA/system01.dbf';
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 3 to '+DATA/sysaux01.dbf';
set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
注意:
(1)我们datafile 3 和 4的区别,datafile 4 我没有指定具体的文件名,那么在还原时会使用ASM 自己的命名方式来命名datafile4. 其他的文件会创建别名。
(2)对switch的说明:
对于nocatalog 模式下,rman备份的信息是保存在控制文件里的,包括文件的路径信息。 这里的switch的作用,就是更新控制文件里的信息。
(3)restore 的时候不会对temp 表空间进行restore。所以等restore 之后,我们需要手工创建temp表空间。
不过在这个测试里,我们还是对tempfile 进行了指定。 但是这个操作只更新控制文件,不恢复数据文件。
run {
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 1 to '+DATA/system01.dbf';
set newname for datafile 2 to '+DATA/undotbs01.dbf';
set newname for datafile 3 to '+DATA/sysaux01.dbf';
5> set newname for datafile 4 to '+DATA';
set newname for tempfile 1 to '+DATA/temp01.dbf';
7> restore database;
8> switch datafile all;
9> switch tempfile all;
10> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 03-APR-15
Starting implicit crosscheck backup at 03-APR-15
allocated channel: ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck backup at 03-APR-15
Starting implicit crosscheck copy at 03-APR-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-APR-15
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA/system01.dbf
restoring datafile 00002 to +DATA/undotbs01.dbf
restoring datafile 00003 to +DATA/sysaux01.dbf
restoring datafile 00004 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/backup/02q38t40_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/02q38t40_1_1 tag=TAG20150401T042847
channel ORA_DISK_1: restore complete, elapsed time: 00:03:47
Finished restore at 03-APR-15
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=876052048 filename=+DATA/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=876052049 filename=+DATA/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=876052049 filename=+DATA/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=876052049 filename=+DATA/dcw/datafile/users.262.876051821
renamed temporary file 1 to +DATA/temp01.dbf in control file
-- 从这里可以看到,对tempfile 仅仅是rename。即更新控制文件。
RMAN>
[oracle@yutian1 dbs]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd DATA
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
undotbs01.dbf
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
Y DCW/
Y YUTIAN/
N control01.ctl => +DATA/YUTIAN/CONTROLFILE/current.257.876049549
N control02.ctl => +DATA/YUTIAN/CONTROLFILE/current.258.876049551
N spfileyutian.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.256.876051011
N sysaux01.dbf => +DATA/DCW/DATAFILE/SYSAUX.261.876051819
N system01.dbf => +DATA/DCW/DATAFILE/SYSTEM.259.876051819
N undotbs01.dbf => +DATA/DCW/DATAFILE/UNDOTBS1.260.876051819
ASMCMD> cd DCW
ASMCMD> ls
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.261.876051819
SYSTEM.259.876051819
UNDOTBS1.260.876051819
USERS.262.876051821
ASMCMD> cd ../
ASMCMD> ls
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
SYSAUX.261.876051819
SYSTEM.259.876051819
UNDOTBS1.260.876051819
USERS.262.876051821
ASMCMD> cd ../../
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
undotbs01.dbf
ASMCMD> cd YUTIAN
ASMCMD> ls
CONTROLFILE/
ASMCMD> cd CONTROLFILE
ASMCMD> ls
current.257.876049549
current.258.876049551
2.8 recover 数据库
在执行restore的节点执行,因为备份文件在该节点上。
RMAN> recover database;
Starting recover at 03-APR-15
using channel ORA_DISK_1
starting media recovery
unable to find archive log
archive log thread=1 sequence=36
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/03/2015 11:59:40
RMAN-06054: media recovery requesting unknown log: thread 1 seq 36 lowscn 940781
最后一行的错误说明:
RMAN-06054: media recovery requesting unknown log: thread 1 seq 36 lowscn 940781
这里是提醒恢复到一个未知的scn号。在alter database mount之后,通过set until scn或者set until time命令设置恢复到的scn号或时间。就可以避免这个错误。
2.9 处理online redo
因为此时另一个节点还没有配好,所以我们只能在执行恢复的节点上处理redo。
SQL> set wrap off
SQL> select * from v$logfile;
rows will be truncated
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo03.log
2 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo02.log
1 ONLINE /u01/app/oracle/product/10.2.0/oradata/dcw/redo01.log
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo01.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo02.log' to '+DATA';
Database altered.
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/oradata/dcw/redo03.log' to '+DATA';
Database altered.
SQL> select * from v$logfile;
rows will be truncated
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -----------------------------------------------------
3 ONLINE +DATA
2 ONLINE +DATA
1 ONLINE +DATA
2.10 open resetlogs 打开DB
在恢复的节点执行该操作。
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
DCW READ WRITE
SQL> select comp_name,version,status from sys.dba_registry;
rows will be truncated
rows will be truncated
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Workspace Manager
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
Oracle Expression Filter
Oracle Data Mining
Oracle Text
Oracle XML Database
Oracle Rule Manager
COMP_NAME
--------------------------------------------------------------------------------
Oracle interMedia
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Spatial
Oracle Enterprise Manager
17 rows selected.
2.11 检查并修改几个初始化参数
SQL> select * from v$option where parameter='Real Application Clusters';
PARAMETER VALUE
---------------------------------------------------------------- ---------------
Real Application Clusters TRUE
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL> show parameter thread
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
SQL> show parameter instance_number
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
由上述返回结果可知,RAC特性是支持的,不过尚未启用集群数据库,因此接下来首先要改的,就是enable CLUSTER DATABASE,操作如下:
修改2个节点的配置参数:
SQL> alter system set instance_number=1 scope=spfile sid='yutian1';
System altered.
SQL> alter system set instance_number=2 scope=spfile sid='yutian2';
System altered.
SQL> alter system set thread=1 scope=spfile sid='yutian1';
System altered.
SQL> alter system set thread=2 scope=spfile sid='yutian2';
System altered.
2.12 创建节点2的undo 表空间
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
这个是rac1上节点的信息,我们在之前的在spfile参数指定的rac2节点的undo 空间是UNDOTBS2.
所以这里我们创建该undo 表空间,并指定相关参数。
SQL> create undo tablespace UNDOTBS2 datafile '+DATA/undotbs02.dbf' size 200M;
Tablespace created.
--这里我们直接指定别名
ASMCMD> ls
DB_UNKNOWN/
DCW/
YUTIAN/
control01.ctl
control02.ctl
spfileyutian.ora
sysaux01.dbf
system01.dbf
temp01.dbf
undotbs01.dbf
undotbs02.dbf
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='yutian2';
System altered.
2.13 添加rac2 节点的redo 文件
rac 的redo 是接其他节点来的,我们之前的rac1上已经有3组,所以我们这里从4开始,在添加2组给rac2,使用thread 2.
SQL> alter database add logfile thread 2 group 4 '+DATA' size 100M;
Database altered.
SQL> alter database add logfile thread 2 group 5 '+DATA' size 100M;
Database altered.
SQL> alter database enable thread 2;
Database altered.
2.14 重启节点1和节点2 上的实例,使相关参数生效
节点1:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 100664792 bytes
Database Buffers 176160768 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
节点2:
[oracle@yutian2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 12:51:50 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2083368 bytes
Variable Size 88081880 bytes
Database Buffers 188743680 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
最后确认:
SQL> select instance_number,instance_name,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- -----------------------------------------------
1 yutian1 yutian1.localdomain
2 yutian2 yutian2.localdomain
到现在已经是集群了,不过还有一些收尾的工作要做。
2.15 执行catclust.sql脚本来创建相关视图
$ORACLE_HOME/rdbms/admin/catclust.sql to create cluster database specific views within the existing instance
SQL>@$ORACLE_HOME/rdbms/admin/catclust.sql
2.16 重建Temp 表空间
在恢复的时候,会自动创建TEMP 表空间。和一个前辈聊到过这个问题,他说这个temp 文件好像没有起到作用。 尤其是数据量大的时候,他建议重建添加temp 数据文件。 这个还是小心为好,
反正重建一些也不费什么事。但是万一出问题,就是大麻烦。
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp01.dbf
SQL> alter tablespace temp add tempfile '+DATA/temp02.dbf' size 100M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp01.dbf
+DATA/temp02.dbf
SQL> alter database tempfile '+DATA/temp01.dbf' offline;
Database altered.
SQL> alter database tempfile '+DATA/temp01.dbf' drop including datafiles;
Database altered.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/temp02.dbf
rac2:
SQL> select instance_number,instance_name,host_name from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------------------------------------------------
2 yutian2 yutian2.localdomain
2.17 添加集群的监听
2.18 将其他信息注册到CRS里
不过这时候通过crs_stat -t查看的时候,还看不到新创建的repdb数据库信息:
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
[oracle@yutian1 dbs]$ srvctl add database -d dcw -o $ORACLE_HOME -p +DATA/spfileyutian.ora
[oracle@yutian1 dbs]$ srvctl add instance -d dcw -i yutian1 -n yutian1
[oracle@yutian1 dbs]$ srvctl add instance -d dcw -i yutian2 -n yutian2
[oracle@yutian1 dbs]$ srvctl modify instance -d dcw -i yutian1 -s +ASM1
[oracle@yutian1 dbs]$ srvctl modify instance -d dcw -i yutian2 -s +ASM2
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.dcw.db application OFFLINE OFFLINE
ora....n1.inst application OFFLINE OFFLINE
ora....n2.inst application OFFLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
这里并没有启动,因为我们的服务是刚添加上来的,还没有同步。 我们启动一下就ok了。
[oracle@yutian1 dbs]$ srvctl start database -d dcw
[oracle@yutian1 dbs]$ srvctl start -d dcw
Usage: srvctl <command> <object> [<options>]
[oracle@yutian1 dbs]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.dcw.db application ONLINE ONLINE yutian1
ora....n1.inst application ONLINE ONLINE yutian1
ora....n2.inst application ONLINE ONLINE yutian2
ora....SM1.asm application ONLINE ONLINE yutian1
ora....N1.lsnr application ONLINE ONLINE yutian1
ora....an1.gsd application ONLINE ONLINE yutian1
ora....an1.ons application ONLINE ONLINE yutian1
ora....an1.vip application ONLINE ONLINE yutian1
ora....SM2.asm application ONLINE ONLINE yutian2
ora....N2.lsnr application ONLINE ONLINE yutian2
ora....an2.gsd application ONLINE ONLINE yutian2
ora....an2.ons application ONLINE ONLINE yutian2
ora....an2.vip application ONLINE ONLINE yutian2
2.19 配置RAC 的Failover 和 Loadbalance
2.20 最后创建查看一下我们开始创建的表:
SQL> set wrap off
SQL> set linesize 80
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
yutian1
SQL> select * from dcw;
NAME LOCATION
---------- --------------------
dcw yutian