Oracle数据库ASM存储异机恢复到文件系统

4.ASM下异机迁移到文件系统中

----环境准备好，创建必要的目录

mkdir -p $ORACLE_BASE/fast_recovery_area/orcl

mkdir -p $ORACLE_BASE/admin/orcl/adump

mkdir -p $ORACLE_BASE/admin/orcl/dpdump

mkdir -p $ORACLE_BASE/admin/orcl/pfile

mkdir -p $ORACLE_BASE/oradata/orcl

 

 

----在asm库上备份，备份后复制到远端

[oracle@xuan1 ~]$ asmcmd

ASMCMD> cd +data/orcl/back*

ASMCMD> pwd

+data/orcl/BACKUPSET

ASMCMD> cd 20*

ASMCMD> ls

ncsnf0_TAG20160929T221656_0.277.923869103

nnndf0_TAG20160929T221656_0.278.923869017

ASMCMD> cp * /home/oracle/.

copying +data/orcl/BACKUPSET/2016_09_29/nnndf0_TAG20160929T221656_0.278.923869017 -> /home/oracle/./nnndf0_TAG20160929T221656_0.278.923869017

ASMCMD> cp ncsnf0_TAG20160929T221656_0.277.923869103 /home/oracle/.

copying +data/orcl/BACKUPSET/2016_09_29/ncsnf0_TAG20160929T221656_0.277.923869103 -> /home/oracle/./ncsnf0_TAG20160929T221656_0.277.923869103

 

[oracle@xuan1 ~]$ scp n* oracle@192.168.11.22:/home/oracle/

oracle@192.168.11.22's password:

ncsnf0_TAG20160929T221656_0.277.923869103                                                         100% 9600KB   9.4MB/s   00:00   

nnndf0_TAG20160929T221656_0.278.923869017                                                          11%  138MB  80.1MB/s   00:13 ETA

 

4.1参数文件恢复

----直接恢复备份文件是不成功的，因为原备份是恢复到asm磁盘，这里没有

RMAN> restore spfile to '/home/oracle/ncsnf0_TAG20160929T221656_0.277.923869103';

 

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

SQL> !scp /home/oracle/pfile.ora oracle@192.168.11.22:/home/oracle/.

oracle@192.168.11.22's password:

pfile.ora                                                                                         100%  922     0.9KB/s   00:00  

 

 

----这里使用pfile启动之前改一些参数

[oracle@xuanDG2 ~]$ vim pfile.ora

orcl.__db_cache_size=394264576

orcl.__java_pool_size=4194304

orcl.__large_pool_size=16777216

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=327155712

orcl.__sga_target=729808896

orcl.__shared_io_pool_size=0

orcl.__shared_pool_size=297795584

orcl.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/orcl/controlfile01.dbf','/u01/app/oracle/oradata/orcl/controlfile02.dbf'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=10485760000

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.memory_target=1056964608

*.open_cursors=300

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.undo_tablespace='UNDOTBS1'

 

----然后启动到nomount

SQL> create spfile from pfile='/home/oracle/pfile.ora';

File created.

 

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1052233728 bytes

Fixed Size                  2259920 bytes

Variable Size             918553648 bytes

Database Buffers          125829120 bytes

Redo Buffers                5591040 bytes

 

========================================================

----11g中新的恢复方法：利用rman启动到nomount然后恢复参数文件

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/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes

Variable Size                285213576 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5517312 bytes

 

RMAN>  restore spfile from '/home/oracle/ncsnf0_TAG20160929T221656_0.277.923869103';

 

Starting restore at 2016-07-18 00:09:45

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/ncsnf0_TAG20160929T221656_0.277.923869103

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 2016-07-18 00:09:46

 

----恢复完成后，再根据需要修改参数

 

4.2控制文件恢复

----这里可以直接恢复，只要参数文件修改好后

RMAN> restore controlfile from  '/home/oracle/ncsnf0_TAG20160929T221656_0.277.923869103';

 

Starting restore at 2016-07-18 00:48:00

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=385 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/oradata/orcl/controlfile01.dbf

output file name=/u01/app/oracle/oradata/orcl/controlfile02.dbf

Finished restore at 2016-07-18 00:48:02

 

 

RMAN> alter database mount;

Database altered.

 

 

4.3数据文件恢复

----先注册备份文件，因为原备份文件在asm中，直接restore是找不到备份文件的

RMAN> catalog start with '/home/oracle/';

 

run{

set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users01.dbf';

set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/example01.dbf';

set newname for datafile 6 to '/u01/app/oracle/oradata/orcl/t_shall_01.dbf';

set newname for datafile 7 to '/u01/app/oracle/oradata/orcl/t_shall_02.dbf';

restore database;

switch datafile all;

}

 

----如果恢复报错，确定上面转换路径是否写正确。

 

+++++++++++++++++++++++++++++++++++++++++++++++++++

----修改数据文件目录： alter database rename file '+DATA/orcl/datafile/system.256.923865265' to '/home/oracle/system01.dbf';

----将数据文件离线：alter database datafile 1 offline;

----控制文件备份：alter database backup controlfile to trace as '/home/oracle/ctl.trc';

----重建控制文件：

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 (

    '/u01/app/oracle/oradata/orcl/redo01'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 2 (

    '/u01/app/oracle/oradata/orcl/redo02'

  ) SIZE 50M BLOCKSIZE 512,

  GROUP 3 (

    '/u01/app/oracle/oradata/orcl/redo03'

  ) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf',

'/u01/app/oracle/oradata/orcl/t_shall_01.dbf',

'/u01/app/oracle/oradata/orcl/t_shall_02.dbf'

CHARACTER SET ZHS16GBK

;

 

SQL> select instance_name,status from v$instance;

 

 

4.4执行Recover

run{

set newname for datafile 1 to '/u01/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile 2 to '/u01/app/oracle/oradata/orcl/undotbs01.dbf';

set newname for datafile 3 to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';

set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users01.dbf';

set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/example01.dbf';

set newname for datafile 6 to '/u01/app/oracle/oradata/orcl/t_shall_01.dbf';

set newname for datafile 7 to '/u01/app/oracle/oradata/orcl/t_shall_02.dbf';

recover database;

switch datafile all;

}

 

unable to find archived log

archived log thread=1 sequence=11

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 07/18/2016 01:57:02

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN of 1041100

 

RMAN> recover database until scn 1041100;

 

Starting recover at 2016-07-18 01:58:12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 2016-07-18 01:58:13

 

SQL> alter database open resetlogs;

 

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

orcl             OPEN

 

SQL> col name for a50

SQL> set linesize 999

SQL> set pagesize 999

SQL> set linesize 200

SQL> select file#,name,status from v$datafile;

     FILE# NAME                                               STATUS

---------- -------------------------------------------------- -------

         1 /u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM

         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE

         3 /u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE

         4 /u01/app/oracle/oradata/orcl/users01.dbf           ONLINE

         5 /u01/app/oracle/oradata/orcl/example01.dbf         ONLINE

         6 /u01/app/oracle/oradata/orcl/t_shall_01.dbf        ONLINE

         7 /u01/app/oracle/oradata/orcl/t_shall_02.dbf        ONLINE

7 rows selected.

 

SQL> select file#,name,status from v$tempfile;

     FILE# NAME                                               STATUS

---------- -------------------------------------------------- -------

         1 +DATA/orcl/tempfile/temp.268.923865379             ONLINE

----这里需要修改下tempfile位置：

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME

------------------------------

TEMP

SQL> alter database tempfile 1 offline;

Database altered.

SQL> alter database tempfile 1 drop;

Database altered.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 100m;

Tablespace altered.

SQL> select file#,name,status from v$tempfile;

     FILE# NAME                                               STATUS

---------- -------------------------------------------------- -------

         1 /u01/app/oracle/oradata/orcl/temp01.dbf            ONLINE

 

----最后是配置好监听、密码文件、tns等等

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2125751/