oracleバックアップの異機種へのリストア方法
48714 ワード
- :
-
-
-
- ,MSSQLSERVER , ,oracle 、 ,
-
- ; , , , , ! , RMAN
-
-
-
-
-
-
-
-
-
- -- :
- (1).RMAN ,db_name 。
-
- , , nid 。
- , , , 。
-
-
- (2). , restore set ,
- switch datafile all 。
-
-
-
-
- :
-
- A, E, E ;
-
-
- B, F .
-
-
-
-
-
-
-
- ------------------------------------
-
- 、
-
- ------------------------------------
-
- --1. DBID
-
-
-
- SQL> select name,dbid from v$database;
-
- NAME DBID
- --------- ----------
- ORCL 1320546556
-
-
-
- --2. DB
-
- run {
- configure retention policy to recovery window of 14 days;
- configure controlfile autobackup on; --
- configure controlfile autobackup format for device type disk to 'E:\backup\controlfile\bak_%F';
- allocate channel c1 device type disk format 'E:\backup\data\bak_%u';
- allocate channel c2 device type disk format 'E:\backup\data\bak_%u';
- sql 'alter system archive log current';
- backup incremental level=0 database skip inaccessible
- plus archivelog filesperset 20
- delete all input;
- release channel c1;
- release channel c2;
- }
- allocate channel for maintenance device type disk;
- crosscheck backupset;
- delete noprompt obsolete;
-
-
- --
-
- run {
- configure retention policy to recovery window of 14 days;
- allocate channel c1 device type disk format 'E:\backup\data\bak_%u';
- allocate channel c2 device type disk format 'E:\backup\data\bak_%u';
- sql 'alter system archive log current';
- backup incremental level=0 database skip inaccessible
- plus archivelog filesperset 20
- delete all input;
- --
- backup current controlfile tag='bak_ctrollfile' format='E:\backup\controlfile\bak_ctl_file_%U_%T';
- backup spfile tag='bak_spfile' format='E:\backup\controlfile\bak_spfile_%U_%T';
- release channel c1;
- release channel c2;
- }
- allocate channel for maintenance device type disk;
- crosscheck backupset;
- delete noprompt obsolete;
-
-
-
- --3. spfile
-
- create pfile='E:\backup\inittest.ora' from spfile;
-
-
- ------------------------------------
-
- 、 :
-
- ------------------------------------
-
- --- 1.
-
- -- .
-
- orapwd file=F:\app\Administrator\product\11.2.0\dbhome_1\database\PWDorcl.ora password=password
-
-
-
- -- 2.
-
-
- -- 1. spfile B
-
-
- \\192.168.2.25\e$\backup\inittest.ORA
-
- E:\bk\inittest.ORA
-
-
-
-
-
-
- -- 3.
-
- ,
-
- eg:
- - *_DUMP_DEST
- - LOG_ARCHIVE_DEST*
- - CONTROL_FILES
-
-
- --- :
-
- orcl.__db_cache_size=939524096
- orcl.__java_pool_size=16777216
- orcl.__large_pool_size=16777216
- orcl.__oracle_base='F:\app\Administrator'#
- orcl.__pga_aggregate_target=855638016
- orcl.__sga_target=2550136832
- orcl.__shared_io_pool_size=0
- orcl.__shared_pool_size=1526726656
- orcl.__streams_pool_size=16777216
- *.audit_file_dest='F:\app\Administrator\admin\orcl\adump' #
- *.audit_trail='db'
- *.compatible='11.2.0.0.0'
- #
- *.control_files='F:\app\Administrator\oradata\orcl\control01.ctl','F:\app\Administrator\oradata\orcl\control02.ctl','F:\app\Administrator\oradata\orcl\control03.ctl'
- *.db_block_size=8192
- *.db_domain=''
- *.db_name='orcl'
- *.db_recovery_file_dest='F:\arch' #
- *.db_recovery_file_dest_size=6442450944
- *.diagnostic_dest='F:\app\Administrator' #
- *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
- *.log_archive_dest_1='location=f:\arch' #
- *.nls_date_format='yyyy-mm-dd hh:mi:ss'
- *.open_cursors=300
- *.optimizer_capture_sql_plan_baselines=TRUE
- *.pga_aggregate_target=845152256
- *.processes=150
- *.remote_login_passwordfile='EXCLUSIVE'
- *.sga_target=2536505344
- *.skip_unusable_indexes=TRUE
- *.undo_tablespace='UNDOTBS1'
-
-
-
-
-
- -- 4. ,
-
- pfile B nomout
-
- rman target /
-
- startup nomount pfile='e:\bk\inittest.ora'
-
-
-
-
-
-
- -- 5. ,
-
-
-
- rman target /
-
-
- set dbid 1320546556
-
-
- restore controlfile from 'e:\bk\controlfile\BAK_CTL_FILE_6LO6SON4_1_1_20130412';
-
-
- restore 2013-04-11 13:58:58
-
- : ORA_DISK_1
- ORA_DISK_1: SID=129 =DISK
-
- ORA_DISK_1:
- ORA_DISK_1: , : 00:00:01
- =F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
- =F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL
- =F:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL
- restore 2013-04-11 13:59:00
-
-
-
-
- -- 6. DB mout
-
- alter database mount;
-
-
- : ORA_DISK_1
-
-
- --
-
- CATALOG START WITH 'E:\bk';
-
- E:\bk
-
-
- ====================================
- : E:\bk\controlfile\BAK_C-1320546556-20130411-03
- : E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
- : E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
- : E:\bk\data\BAK_60O6QIGV
- : E:\bk\data\BAK_61O6QIGV
- : E:\bk\data\BAK_62O6QIH1
- : E:\bk\data\BAK_63O6QIH1
- : E:\bk\data\BAK_64O6QILM
-
- ( YES NO)? yes
- ...
-
-
-
- ======================
- : E:\bk\controlfile\BAK_C-1320546556-20130411-03
- : E:\bk\controlfile\BAK_CTL_FILE_65O6QILO_1_1_20130411
- : E:\bk\controlfile\BAK_SPFILE_66O6QILQ_1_1_20130411
- : E:\bk\data\BAK_60O6QIGV
- : E:\bk\data\BAK_61O6QIGV
- : E:\bk\data\BAK_62O6QIH1
- : E:\bk\data\BAK_63O6QIH1
- : E:\bk\data\BAK_64O6QILM
-
-
-
-
-
-
- -- 7.restore
-
-
- 7.1 :
-
-
-
-
- Target :
- SQL> select trim(file_id),trim(file_name) from dba_data_files ORDER BY FILE_ID ASC;
-
- 1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
- 2 E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
- 3 E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF
- 4 E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF
- 6 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02.DBF
- 7 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03.DBF
- 8 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04.DBF
- 9 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05.DBF
- 11 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06.DBF
- 12 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07.DBF
- 13 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08.DBF
- 14 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09.DBF
- 15 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10.DBF
- 16 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01.DBF
- 17 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11.DBF
- 18 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12.DBF
- 19 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13.DBF
- 20 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14.DBF
- 21 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15.DBF
- 22 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16.DBF
- 23 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17.DBF
- 24 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18.DBF
- 25 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19.DBF
- 26 E:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20.DBF
-
-
-
-
- SQL>select trim(file_id),trim(file_name) from dba_temp_files;
-
- 1 E:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
-
-
-
-
-
- select 'SET NEWNAME FOR DATAFILE'|| ' '||file_id||' '||'TO'|| ' '||''''||trim(file_name)||'''' ||';'
- from dba_data_files ORDER BY FILE_ID ASC;
-
- --- :
-
-
- RUN
- {
- # rename the datafiles
- SET NEWNAME FOR DATAFILE 1 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF';
- SET NEWNAME FOR DATAFILE 2 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF';
- SET NEWNAME FOR DATAFILE 3 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF';
- SET NEWNAME FOR DATAFILE 4 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF';
- SET NEWNAME FOR DATAFILE 6 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY02.DBF';
- SET NEWNAME FOR DATAFILE 7 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY03.DBF';
- SET NEWNAME FOR DATAFILE 8 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY04.DBF';
- SET NEWNAME FOR DATAFILE 9 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY05.DBF';
- SET NEWNAME FOR DATAFILE 11 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY06.DBF';
- SET NEWNAME FOR DATAFILE 12 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY07.DBF';
- SET NEWNAME FOR DATAFILE 13 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY08.DBF';
- SET NEWNAME FOR DATAFILE 14 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY09.DBF';
- SET NEWNAME FOR DATAFILE 15 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY10.DBF';
- SET NEWNAME FOR DATAFILE 16 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY01.DBF';
- SET NEWNAME FOR DATAFILE 17 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY11.DBF';
- SET NEWNAME FOR DATAFILE 18 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY12.DBF';
- SET NEWNAME FOR DATAFILE 19 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY13.DBF';
- SET NEWNAME FOR DATAFILE 20 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY14.DBF';
- SET NEWNAME FOR DATAFILE 21 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY15.DBF';
- SET NEWNAME FOR DATAFILE 22 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY16.DBF';
- SET NEWNAME FOR DATAFILE 23 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY17.DBF';
- SET NEWNAME FOR DATAFILE 24 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY18.DBF';
- SET NEWNAME FOR DATAFILE 25 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY19.DBF';
- SET NEWNAME FOR DATAFILE 26 TO 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\VPAY20.DBF';
- SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO01.LOG''
- TO ''F:\app\Administrator\oradata\orcl\REDO01.LOG'' ";
- SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO02.LOG''
- TO ''F:\app\Administrator\oradata\orcl\REDO02.LOG'' ";
- SQL "ALTER DATABASE RENAME FILE ''E:\app\Administrator\oradata\orcl\REDO03.LOG''
- TO ''F:\app\Administrator\oradata\orcl\REDO03.LOG'' ";
- RESTORE DATABASE;
- SWITCH DATAFILE ALL;
- }
-
-
-
-
- switch datafile all :
- -- nocatalog ,rman , 。 switch datafile all , 。
-
-
- -- 8.recover
-
- RECOVER DATABASE;
-
-
- /*** :
-
- =1 =1696
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: recover ( 04/11/2013 15:18:07 )
- RMAN-06054: 1 1696 SCN 240793
- 29
-
-
- --- :
-
- RMAN> recover database until scn 24159677;
-
- recover 2013-04-11 15:29:05
- ORA_DISK_1
-
-
- , : 00:00:02
-
- recover 2013-04-11 15:29:11
-
- ***/
-
-
-
-
-
- ---- 9.open...resetlogs
-
- alter database open resetlogs;
-
-
-
-
- /******* :
-
- RMAN> alter database open resetlogs;
-
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: alter db ( 04/11/2013 15:38:25 )
- ORA-00392: 1 ( 1) ,
- ORA-00312: 1 1: 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG'
-
-
-
- ---- :
-
-
- SQL> select group#,bytes/1024/1024||'M',status from v$log;
-
- GROUP# BYTES/1024/1024||'M' STATUS
- ---------- ----------------------------------------- ----------------
- 1 50M CLEARING_CURRENT
- 3 50M CLEARING
- 2 50M CLEARING
-
-
-
- SQL> alter database clear logfile group 1; --
-
-
-
- SQL> alter database clear logfile group 2;
-
-
-
- SQL> alter database clear logfile group 3;
-
-
- SQL> select group#,bytes/1024/1024||'M',status from v$log;
-
- GROUP# BYTES/1024/1024||'M' STATUS
- --------- ----------------------------------------- ----------------
- 1 50M CURRENT
- 3 50M UNUSED
- 2 50M UNUSED
-
-
-
-
-
- *******/
-
-
-
- --- 10: temp
-
- sqlplus / as sysdba
-
-
- SQL> select name from v$tempfile;
-
- NAME
-
- --------------------------------------------------------------------------------
-
- E:\app\Administrator\oradata\orcl\TEMP01.DBF -- E
-
-
-
- --A offline
-
- SQL> alter database tempfile 'E:\app\Administrator\oradata\orcl\TEMP01.DBF' offline;
-
- Database altered.
-
-
-
- -- OS temp B
-
- SQL> mv E:\app\Administrator\oradata\orcl\TEMP01.DBF F:\app\Administrator\oradata\orcl\TEMP01.DBF
-
-
-
- -- temp
-
- SQL> alter database rename file 'E:\app\Administrator\oradata\orcl\TEMP01.DBF' to 'F:\app\Administrator\oradata\orcl\TEMP01.DBF';
-
-
-
-
-
- --temp online
-
- SQL> alter database tempfile 'F:\app\Administrator\oradata\orcl\TEMP01.DBF' online;
-
- Database altered.
-
-
-
- --
-
- SQL> select name from v$tempfile;
-
- NAME
-
- --------------------------------------------------------------------------------
-
- F:\app\Administrator\oradata\orcl\TEMP01.DBF
-
-
-
-
-