环境:
OS:Red Hat Linux As 5
原库:11.2.0.1(ASM)
目的库:11.2.0.1(普通文件)
在ASM管理的数据库上使用RMAN备份集,将该备份集COPY到另一台机器上(普通文件),然后做恢复,过程步骤如下.
----------------------------------------原库上操作-------------------------------
1.在原库上备份
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
backup database format '/backup/rman_bak/full_%u_%T.bak';
backup spfile format '/backup/rman_bak/spfile_%u_%T.bak';
backup current controlfile format '/backup/rman_bak/ctl_%u_%T.bak';
sql 'alter system archive log current';
backup archivelog all delete input format '/backup/rman_bak/arc_%u_%T.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
2.tar备份集并传送到目的库
[oracle@hxl oracl]$tar -cvf backup.tar ./backup
3.查看原库的dbid(恢复的时候需要指定dbid)
SQL> select dbid from v$database;
DBID
----------
1839869098
4.查看原库的相应目录(以便目的库也创建相应的目录)
[oracle@hxl oracl]cd /u01/app/oracle/admin/oracl
[oracle@hxl oracl]$ ls
adump dpdump pfile
--------------------------------------目的库上操作----------------------------------
1.修改目的机器上得oracle用户的环境变量,保持跟原机器一致
export PS1='['`hostname`':$LOGIN:$PWD]$'
export EDITOR=vi
export ORACLE_SID=oracl01
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/usr/X11R6/bin:/bin:/sbin
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
2.安装oracle软件
具体步骤省略
3.创建相应目录
mkdir -p /u01/app/oracle/admin/oracl/{adump,dpdump,pfile}
4.解压缩tar文件
[oracle@test ftp]$tar -xvf rman.tar
5.创建密码文件
orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapworacl01.ora password=myoracle
密码文件的命名格式为orapw+SID
6.恢复参数文件
[oracle@test dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 28 05:39:42 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid 1839869098;
executing command: SET DBID
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1335192 bytes
Variable Size 75497576 bytes
Database Buffers 79691776 bytes
Redo Buffers 2494464 bytes
RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora' from '/ftp/rman_bak/spfile_06nm76fv_20120927.bak';
Starting restore at 2012-09-28 05:41:52
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /ftp/rman_bak/spfile_06nm76fv_20120927.bak
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2012-09-28 05:41:54
7.修改pfile文件
查看pfile的内容
[oracle@test dbs]$ more initoracl01.ora
oracl01.__db_cache_size=54525952
oracl01.__java_pool_size=4194304
oracl01.__large_pool_size=4194304
oracl01.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
oracl01.__pga_aggregate_target=113246208
oracl01.__sga_target=339738624
oracl01.__shared_io_pool_size=0
oracl01.__shared_pool_size=268435456
oracl01.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/oracl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/oracl/controlfile/current.260.794393131','+RECDATA/oracl/
controlfile/current.265.794560115'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+DATA'
*.db_domain=''
*.db_name='oracl'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracl01XDB)'
*.open_cursors=2000
*.pga_aggregate_target=113246208
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2200
*.sga_target=339738624
*.undo_retention=86400
*.undo_tablespace='UNDOTBS1'
因为目的机器的物理内存比较小,所以需要修改sga相关内存参数
*.sga_target=209715200 //根据实际内存减小为200M
oracl01.__sga_target=209715200
oracl01.__pga_aggregate_target=83886080 //根据实际内存减小为80M
*.pga_aggregate_target=83886080
*.control_files='/u01/app/oracle/oradata/oracl/control01.ctl','/u01/app/oracle/oradata/oracl/control02.ctl'
*.db_create_file_dest='/u01/app/oracle/oradata/oracl'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata/oracl'
这里的控制文件目录需要创建
mkdir -p /u01/app/oracle/oradata/oracl/
8.使用编辑好的参数文件启动数据库到nomount状态并恢复控制文件
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 201330456 bytes
Database Buffers 4194304 bytes
Redo Buffers 2375680 bytes
启动rman恢复控制文件
RMAN> restore controlfile from '/ftp/rman_bak/ctl_07nm76g1_20120927.bak';
Starting restore at 2012-09-28 06:29:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/oracl/control01.ctl
output file name=/u01/app/oracle/oradata/oracl/control02.ctl
Finished restore at 2012-09-28 06:29:13
9.启动数据库到mount状态并注册备份集
SQL> alter database mount;
Database altered.
注册备份集
RMAN> catalog start with '/ftp/rman_bak/';
released channel: ORA_DISK_1
searching for all files that match the pattern /ftp/rman_bak/
List of Files Unknown to the Database
=====================================
File Name: /ftp/rman_bak/arc_09nm76g7_20120927.bak
File Name: /ftp/rman_bak/arc_0anm76g7_20120927.bak
File Name: /ftp/rman_bak/spfile_06nm76fv_20120927.bak
File Name: /ftp/rman_bak/arc_0bnm76g8_20120927.bak
File Name: /ftp/rman_bak/arc_08nm76g7_20120927.bak
File Name: /ftp/rman_bak/full_02nm76e4_20120927.bak
File Name: /ftp/rman_bak/full_05nm76fc_20120927.bak
File Name: /ftp/rman_bak/full_03nm76e4_20120927.bak
File Name: /ftp/rman_bak/full_04nm76ee_20120927.bak
File Name: /ftp/rman_bak/full_01nm76e3_20120927.bak
File Name: /ftp/rman_bak/ctl_07nm76g1_20120927.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /ftp/rman_bak/arc_09nm76g7_20120927.bak
File Name: /ftp/rman_bak/arc_0anm76g7_20120927.bak
File Name: /ftp/rman_bak/spfile_06nm76fv_20120927.bak
File Name: /ftp/rman_bak/arc_0bnm76g8_20120927.bak
File Name: /ftp/rman_bak/arc_08nm76g7_20120927.bak
File Name: /ftp/rman_bak/full_02nm76e4_20120927.bak
File Name: /ftp/rman_bak/full_05nm76fc_20120927.bak
File Name: /ftp/rman_bak/full_03nm76e4_20120927.bak
File Name: /ftp/rman_bak/full_04nm76ee_20120927.bak
File Name: /ftp/rman_bak/full_01nm76e3_20120927.bak
File Name: /ftp/rman_bak/ctl_07nm76g1_20120927.bak
10.列出当前的所有数据文件
SQL> column name format a60
SQL> select file# as "file/grp#", name from v$datafile;
file/grp# NAME
---------- ------------------------------------------------------------
1 +DATA/oracl/datafile/system.256.794393079
2 +DATA/oracl/datafile/sysaux.257.794393079
3 +DATA/oracl/datafile/undotbs1.258.794393081
4 +DATA/oracl/datafile/users.259.794393081
5 +DATA/oracl/datafile/tps_hxl.266.794547393
11.恢复数据库
[oracle@test rman_bak]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 28 06:36:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORACL (DBID=1839869098, not open)
RMAN> run{
set newname for datafile 1 to '/u01/app/oracle/oradata/oracl/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/oracl/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/oracl/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/oracl/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/oracl/tps_hxl01.dbf';
restore database;
switch datafile all;
recover database;
}2> 3> 4> 5> 6> 7> 8> 9> 10>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 2012-09-28 06:37:26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oracl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/oracl/tps_hxl01.dbf
channel ORA_DISK_1: reading from backup piece /backup/rman_bak/full_03nm76e4_20120927.bak
channel ORA_DISK_1: errors found reading piece handle=/backup/rman_bak/full_03nm76e4_20120927.bak
channel ORA_DISK_1: failover to piece handle=/ftp/rman_bak/full_03nm76e4_20120927.bak tag=TAG20120927T010258
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oracl/system01.dbf
channel ORA_DISK_1: reading from backup piece /backup/rman_bak/full_01nm76e3_20120927.bak
channel ORA_DISK_1: errors found reading piece handle=/backup/rman_bak/full_01nm76e3_20120927.bak
channel ORA_DISK_1: failover to piece handle=/ftp/rman_bak/full_01nm76e3_20120927.bak tag=TAG20120927T010258
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:30
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oracl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oracl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /backup/rman_bak/full_02nm76e4_20120927.bak
channel ORA_DISK_1: errors found reading piece handle=/backup/rman_bak/full_02nm76e4_20120927.bak
channel ORA_DISK_1: failover to piece handle=/ftp/rman_bak/full_02nm76e4_20120927.bak tag=TAG20120927T010258
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:18
Finished restore at 2012-09-28 06:38:31
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=795163112 file name=/u01/app/oracle/oradata/oracl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=795163112 file name=/u01/app/oracle/oradata/oracl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=795163112 file name=/u01/app/oracle/oradata/oracl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=795163112 file name=/u01/app/oracle/oradata/oracl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=795163113 file name=/u01/app/oracle/oradata/oracl/tps_hxl01.dbf
Starting recover at 2012-09-28 06:38:34
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=19
channel ORA_DISK_1: reading from backup piece /ftp/rman_bak/arc_0anm76g7_20120927.bak
channel ORA_DISK_1: piece handle=/ftp/rman_bak/arc_0anm76g7_20120927.bak tag=TAG20120927T010406
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_19_794393133.dbf thread=1 sequence=19
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /ftp/rman_bak/arc_0bnm76g8_20120927.bak
channel ORA_DISK_1: piece handle=/ftp/rman_bak/arc_0bnm76g8_20120927.bak tag=TAG20120927T010406
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_20_794393133.dbf thread=1 sequence=20
unable to find archived log
archived log thread=1 sequence=21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/28/2012 06:38:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 21 and starting SCN of 907091
尝试resetlogs打开数据库
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/28/2012 07:21:18
ORA-03113: end-of-file on communication channel
Process ID: 2639
Session ID: 18 Serial number: 13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
ORA-03114: not connected to ORACLE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/28/2012 07:21:18
ORA-03113: end-of-file on communication channel
Process ID: 2639
Session ID: 18 Serial number: 13
[oracle@test ~]$
alert文件的错误如下:
alter database recover logfile '/ftp/rman_bak/arch1_22_794393133.dbf'
Media Recovery Log /ftp/rman_bak/arch1_22_794393133.dbf
ORA-279 signalled during: alter database recover logfile '/ftp/rman_bak/arch1_22_794393133.dbf'...
alter database recover logfile '/ftp/rman_bak/arch1_23_794393133.dbf'
Media Recovery Log /ftp/rman_bak/arch1_23_794393133.dbf
ORA-279 signalled during: alter database recover logfile '/ftp/rman_bak/arch1_23_794393133.dbf'...
alter database recover logfile '/ftp/rman_bak/arch1_24_794393133.dbf'
Media Recovery Log /ftp/rman_bak/arch1_24_794393133.dbf
ORA-279 signalled during: alter database recover logfile '/ftp/rman_bak/arch1_24_794393133.dbf'...
alter database recover logfile '/ftp/rman_bak/arch1_25_794393133.dbf'
Media Recovery Log /ftp/rman_bak/arch1_25_794393133.dbf
ORA-279 signalled during: alter database recover logfile '/ftp/rman_bak/arch1_25_794393133.dbf'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
Fri Sep 28 07:21:15 2012
alter database open resetlogs
ERROR: slave communication error with ASM; terminating process 2639
错误的原因是控制文件中redo是ASM磁盘管理的,resetlogs会尝试去创建这些redo,但目的库时普通文件管理的,所以报了错误,这个时候需要重建控制文件,改redo由ASM管理到普通文件管理.
12.重建控制文件
因为控制文件记录的日志文件是在ASM磁盘的上的,这里需要重建控制文件,重新定义日志文件.
SQL> alter database backup controlfile to trace;
Database altered.
在目录/u01/app/oracle/diag/rdbms/oracl/oracl01/trace(根据每个机器的目录而定)找到trc文件,找到如下内容的部分.
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/oracl/onlinelog/group_1.262.794548253',
'+RECDATA/oracl/onlinelog/group_1.259.794548255'
) SIZE 128M BLOCKSIZE 512,
GROUP 2 (
'+DATA/oracl/onlinelog/group_2.261.794548267',
'+RECDATA/oracl/onlinelog/group_2.260.794548267'
) SIZE 128M BLOCKSIZE 512,
GROUP 3 (
'+DATA/oracl/onlinelog/group_3.263.794548319',
'+RECDATA/oracl/onlinelog/group_3.261.794548321'
) SIZE 128M BLOCKSIZE 512,
GROUP 4 (
'+DATA/oracl/onlinelog/group_4.267.794547499',
'+RECDATA/oracl/onlinelog/group_4.256.794547501'
) SIZE 128M BLOCKSIZE 512,
GROUP 5 (
'+DATA/oracl/onlinelog/group_5.268.794547535',
'+RECDATA/oracl/onlinelog/group_5.257.794547535'
) SIZE 128M BLOCKSIZE 512,
GROUP 6 (
'+DATA/oracl/onlinelog/group_6.269.794547541',
'+RECDATA/oracl/onlinelog/group_6.258.794547541'
) SIZE 128M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf',
'/u01/app/oracle/oradata/oracl/tps_hxl01.dbf'
CHARACTER SET ZHS16GBK
;
将以上的日志组部分经行修改,修改完成后的内容如下:
CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/oracl/redo0101',
'/u01/app/oracle/oradata/oracl/redo0102'
) SIZE 128M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/oracl/redo0201',
'/u01/app/oracle/oradata/oracl/redo0202'
) SIZE 128M BLOCKSIZE 512,
GROUP 3 (
'/u01/app/oracle/oradata/oracl/redo0301',
'/u01/app/oracle/oradata/oracl/redo0302'
) SIZE 128M BLOCKSIZE 512,
GROUP 4 (
'/u01/app/oracle/oradata/oracl/redo0401',
'/u01/app/oracle/oradata/oracl/redo0402'
) SIZE 128M BLOCKSIZE 512,
GROUP 5 (
'/u01/app/oracle/oradata/oracl/redo0501',
'/u01/app/oracle/oradata/oracl/redo0502'
) SIZE 128M BLOCKSIZE 512,
GROUP 6 (
'/u01/app/oracle/oradata/oracl/redo0601',
'/u01/app/oracle/oradata/oracl/redo0602'
) SIZE 128M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf',
'/u01/app/oracle/oradata/oracl/tps_hxl01.dbf'
CHARACTER SET ZHS16GBK
;
在nomount状态下执行如下脚本
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 201330456 bytes
Database Buffers 4194304 bytes
Redo Buffers 2375680 bytes
SQL>set sqlblanklines on
SQL> CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/u01/app/oracle/oradata/oracl/redo0101',
'/u01/app/oracle/oradata/oracl/redo0102'
) SIZE 128M BLOCKSIZE 512,
GROUP 2 (
'/u01/app/oracle/oradata/oracl/redo0201',
'/u01/app/oracle/oradata/oracl/redo0202'
) SIZE 128M BLOCKSIZE 512,
GROUP 3 (
'/u01/app/oracle/oradata/oracl/redo0301',
'/u01/app/oracle/oradata/oracl/redo0302'
) SIZE 128M BLOCKSIZE 512,
GROUP 4 (
'/u01/app/oracle/oradata/oracl/redo0401',
'/u01/app/oracle/oradata/oracl/redo0402'
) SIZE 128M BLOCKSIZE 512,
GROUP 5 (
'/u01/app/oracle/oradata/oracl/redo0501',
'/u01/app/oracle/oradata/oracl/redo0502'
) SIZE 128M BLOCKSIZE 512,
GROUP 6 (
'/u01/app/oracle/oradata/oracl/redo0601',
'/u01/app/oracle/oradata/oracl/redo0602'
) SIZE 128M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/oracl/system01.dbf',
'/u01/app/oracle/oradata/oracl/sysaux01.dbf',
'/u01/app/oracle/oradata/oracl/undotbs01.dbf',
'/u01/app/oracle/oradata/oracl/users01.dbf',
'/u01/app/oracle/oradata/oracl/tps_hxl01.dbf'
CHARACTER SET ZHS16GBK
;
SQL> alter database open resetlogs;
Database altered.
13.验证
无意中查看某个视图报如下错误
SQL> desc dba_users;
ERROR:
ORA-04031: unable to allocate 16 bytes of shared memory ("shared
pool","DATABASE","KKTIN","kglhin: temp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select
job, nvl2(last_date, ...","SQLA","tmp")
检查初始话参数发现,shared_pool_size 大于了sga_target,这个应该是调整内存的时候漏了调整shared_pool_size导致的.
oracl01.__sga_target=209715200
oracl01.__shared_pool_size=268435456
shared_pool_size调整为
oracl01.__shared_pool_size=167772160
然后使用修改好的初始话参数文件启动数据库.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora
ORACLE instance started.
Total System Global Area 209235968 bytes
Fixed Size 1335528 bytes
Variable Size 201330456 bytes
Database Buffers 4194304 bytes
Redo Buffers 2375680 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
这个时候原以为问题已经解决了,过了一会,执行其他操作,依然报错误.
SQL> connect hxl/oracle
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select
job, nvl2(last_date, ...","SQLA","tmp")
Warning: You are no longer connected to ORACLE.
看来之前设置的shared_pool_size还是过小,查看sga_target和shared_pool_size
*.sga_target=209715200
oracl01.__sga_target=209715200
oracl01.__shared_pool_size=16777216
根据实际内存,重新分配这两个参数的大小
*.sga_target=314572800
oracl01.__sga_target=314572800
oracl01.__shared_pool_size=209715200
启动数据库
SQL> startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initoracl01.ora
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 218106968 bytes
Database Buffers 88080384 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> connect hxl/hxl
Connected.
SQL>
SQL>
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TB_TEST
SQL> desc tb_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL> select count(1) from tb_test;
COUNT(1)
----------
10000
SQL>
以上排错过程说明在迁移的时候修改内存参数的时候需要特别注意,因为目的机器的内存跟原库机器的内存不同,这就要我们做出合理的分配.
说明:
也可以在mount状态下使用如下命令更改redo日志,全部修改完成后再open resetlogs
alter database rename file '+DATA/oracl/onlinelog/group_1.262.794548253' to '/oracle/oradata/redo01.dbf'
-- The End --
