单实例ASM备份集在另一台普通文件管理的机器恢复

最新推荐文章于 2022-11-08 15:46:38 发布
最新推荐文章于 2022-11-08 15:46:38 发布
阅读量281 收藏
点赞数
文章标签： 数据库 运维 网络

环境:
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 --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14385647/viewspace-749828/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14385647/viewspace-749828/

congkai0287
关注
Oracle DB 管理ASM实例
sdfsdkk的博客
11-04 790
分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.youkuaiyun.com/jiangjunshow也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！                • 描述使用ASM 的
使用RMAN恢复RAC备份到单实例
wang_san_shi的专栏
03-25 3011
本文记录了使用RAC数据库的RMAN备份，恢复到单实例数据库上的过程。 1、测试环境 目标环境：Oralce linux 6.5 + Oracle 12.1.0.1   hostname：oracletest  SID:orarac RAC环境：Oracle linux 6.5 + OGI 12.1.0.1 + Oracle 12.1.0.1 + ASM   hostname：r
oracle 10gR2 RAC+ASM恢复到单实例
12-15 84
RAC asm恢复到单实例 rac源服务器： rac1：192.168.100.201 rac2：192.168.100.202 目标服务器： 192.168.8.201 恢复的主要步骤如下： 恢复初始化参数文件->修改参数文件->恢复控制文件->resotre数据文件->recover数据文件 1、正确安装单实例数据库 1）查看rac文件环境变量...
ASM的备份集在文件系统上恢复测试
weixin_34352449的博客
09-03 202
背景：最近时常有客户咨询这类问题，其实很简单一个操作，但由于每个人的理解差异，也容易出现各种问题或者误解，本文主要总结下这个过程以及常遇到的问题处理。 环境：Site A(Oracle RAC 11.2.0.4) + Site B(Single Instance DB 11.2.0.4，只安装了DB软件) 需求：使用Site A的备份，恢复数据到Site B环境。 1.备份数据库和控制文件 2....
使用ASM FTP工具，将ASM文件从一个库转移到另一个库
文档搬运工
04-11 607
在看《Oracle Database 11g RAC手册》这本书的时候。提到ASM有个FTP工具，可以像普通文件那样，将ASM文件从一个库转移到另一个库。测试了下，可以。参考文档： How to configure XDB for using ftp and http protocols with ASM (文档 ID 357714.1)前提条件，配置FTP（略）配置相关端口：SYS@racdb2...
oracle数据库数据备份到asm,普通库迁移至ASM存储 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 134296487...
weixin_42318030的博客
04-12 235
--查看当前库文件位置SQL> select name from v$datafile2 union3 select member from v$logfile4 union5 select name from v$controlfile6 union7 select name from v$tempfile;NAME-------------------------------...
oracle_asm_管理_中文.pdf
11-19
Oracle ASM支持包括数据文件、日志文件、控制文件、归档日志和RMAN备份集等多种Oracle数据库文件类型。此外，ASM为使用Oracle RAC的用户提供了无需第三方集群逻辑卷管理器或集群文件系统即可实现的数据共享功能。 ...
ASM磁盘管理
01-03
- **RMAN备份集**: RMAN工具用于数据库备份与恢复，备份集是其中的一种备份格式。 - **映像副本**: 是数据库数据文件的完整副本，可用于快速恢复。 - **控制文件备份集**: 专门用于备份控制文件的信息。 - **Data...
error:slave communication error with ASM
cizhiyou2391的博客
09-12 320
1.将asm上面的数据库恢复到filesystem上面的时候，一直遇到如题的错误。 很郁闷。 2.在baidu之后发现restore controlfile需要crosscheck backup才可...
Linux Oracle12 数据库迁移问题手记
清风弄影
10-19 901
物理机上安装好了oracle 12C 将虚拟机的数据库迁移到物料主机上，使用scp -r /nasbackup oracle@192.168.0.35:/u01/nasbackup 将备份文件拷贝到物理机 然后在虚拟机通过rman备份方式还原到物理机，其中遇到几个问题： 1.环境变量问题 将密码文件和spfile文件拷贝到了物理机，数据库实例startup无法启动，并没有按启动顺序加载，按理应该...
ERROR: slave communication error with ASM
cizhiyou2391的博客
03-09 446
今天做dataguard的时候出现了如下错误：[@more@] primary database使用的是asm，standby database 使用的系统文件 ERROR: slav...
oracle+rac+asm+冷备,oracle rac 在asm下的备份与恢复
weixin_32938207的博客
04-11 617
oracle 10g rac+asm 的备份与恢复，因为asm 磁盘管理是两个实例都可以访问的，并且是共享的，归档日志文件也是放在asm磁盘上，所以备份与恢复的操作和单实例差不多，只需要在一个节点上做可以了，以下是测试过程：SQL> show parameter log_archive_dest_1NAME TYPE...
oracle rman丢失归档备份集的不完全恢复
冰封郁金香
07-22 1880
如果说要想改成其他的实例名，可以在恢复成功后，用nid命令修改。----------------分割线-------------------------上面看到备份集的SCN是234189095，因为归档备份丢失，所以只能恢复到这个SCN。-----如果是路径不一致要用下面的脚本指定新路径---------------控制文件里，所以如果在恢复的时候，如果实例名不一致，恢复的时候会报错。-----如果是路径相同直接恢复---------------成功启动数据库，且启动过程中alert日志无报错。...
oracle11数据库冷备迁移,oracle备份与恢复实验之冷备份--由ASM冷备迁移数据库到文件系统...
weixin_31720623的博客
04-04 282
实验环境如下：第一台机器模拟源数据库，第二台机器模拟目标数据库数据库版本：oracle 11.2.0.1.0操作系统:rhel5.6测试目的:第一台机器为ASM环境，第二台机器为文件系统，将asm中的数据文件通过冷备份的方式迁移到文件系统中。注明：本例在测试环境完成，在安装grid时未单独创建grid用户，可以使用oracle提供的oraenv命令来切换环境变量在本例中，为区别迁移的数据库，备份的...
Oracle RMAN 口令加密测试
Hehuyi_In的博客
11-08 1455
业务希望对一些不由DBA运维的数据库配置备份设置加密，密码仅由业务同事保存，必须输入密码才能进行数据恢复。考虑业务同事需要保存密码，而基于wallet的加密配置较复杂、DBA又难以接触到相关服务器，。设置方法口令务必要记得，否则后续无法会恢复数据。
生产案例：开发不小心把某个表数据清了，没有逻辑备份，有物理备份
weixin_30853329的博客
12-08 140
刚到公司坐下，开发人员就匆匆跑过来跟我说：“由于不知道哪一天不小心把某某表的数据清空了，能否帮我恢复一下。” 第一反应，只要备份，那很简单，所以下面是我的解决办法。 思路一：利用闪回找回 select * from INFO_FEEDBACK_ORCLE as of timestamp to_timestamp('2016-12-06 12:00:30','YYYY-M...
oracle备份与恢复实验之冷备份--由ASM冷备迁移数据库到文件系统
cnm5002的专栏
04-16 267
实验环境如下： 第一台机器模拟源数据库，第二台机器模拟目标数据库 数据库版本：oracle 11.2.0.1.0 操作系统:rhel5.6 测试目的:第一台机器为ASM环境，第二台机器为文件系统，将asm中的数据...
Oracle11g备份恢复详解：手工与物理备份、闪回技术
而在Oracle中，RMAN提供了更高级的备份和恢复功能，能够自动备份ASM文件，并支持备份集和映像副本等高级特性。 Oracle11g的备份恢复是一个综合性的过程，包括计划、执行和验证，需要DBA深入理解和熟练掌握。这份...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值