【資料遷移】RMAN遷移資料庫到ASM(三)遷移onlinelog等到ASM

yuntui發表於2016-11-03
資料庫ASM
RMAN遷移資料庫到ASM（三）遷移online log等到ASM

Migrating Databases To and From ASM with Recovery Manager

使用RMAN將檔案系統中oracle 10g資料庫檔案遷移到ASM磁碟組。
文章分為三個部分：一、建立ASM磁碟組 二、切換資料檔案到ASM 三、遷移臨時檔案、online log等到ASM
博文連結：http://blog.itpub.net/29475508/viewspace-1296477/

【實驗環境】
作業系統     : AIX5.3
資料庫版本：Oracle 10.2.0
儲存裝置   ：SUN T3 光纖陣列

【文章目錄】


【實驗過程】

11、遷移臨時檔案


The new tempfiles are created when you open the database.

12、修改快跟蹤檔案到ASM



Thu Aug 21 14:41:06 2014

alter database disable block change tracking

Thu Aug 21 14:41:07 2014

Deleted file /u01/app/oracle/oradata/prod/block_change.log

Completed: alter database disable block change tracking

Thu Aug 21 14:42:10 2014

alter database enable block change tracking using file '+DATA'

Thu Aug 21 14:42:12 2014

Block change tracking file is current.

Completed: alter database enable block change tracking using file '+DATA'

13、開庫


【跟蹤日誌】

Thu Aug 21 14:43:11 2014

alter database open

Thu Aug 21 14:43:11 2014

Block change tracking file is current.

Thu Aug 21 14:43:12 2014

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=23, OS id=663638

Thu Aug 21 14:43:12 2014

ARC0: Archival started

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC1 started with pid=24, OS id=655432

Thu Aug 21 14:43:13 2014

Thread 1 opened at log sequence 152

  Current log# 4 seq# 152 mem# 0: /u01/app/oracle/oradata/prod/redo04.log

Successful open of redo thread 1

Thu Aug 21 14:43:13 2014

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Thu Aug 21 14:43:13 2014

ARC0: STARTING ARCH PROCESSES

Thu Aug 21 14:43:13 2014

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

Thu Aug 21 14:43:13 2014

ARC2: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

ARC2 started with pid=25, OS id=639074

Thu Aug 21 14:43:14 2014

Starting background process CTWR

CTWR started with pid=26, OS id=659544

Block change tracking service is active.

Thu Aug 21 14:43:15 2014

SMON: enabling cache recovery

Thu Aug 21 14:43:16 2014

Successfully onlined Undo Tablespace 1.

Thu Aug 21 14:43:16 2014

SMON: enabling tx recovery

Thu Aug 21 14:43:17 2014

Re-creating tempfile +DATA as +DATA/prod/tempfile/temp.265.856190597

Database Characterset is ZHS16GBK

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

QMNC started with pid=27, OS id=667732

Thu Aug 21 14:43:25 2014

Completed: alter database open


14、遷移online log到ASM

根據官方文件提供的指令碼 改寫

declare

   cursor rlc is

      select group# grp, thread# thr, bytes/1024 bytes_k from v$log order by 1;

   stmt     varchar2(2048);

   swtstmt  varchar2(1024) := 'alter system switch logfile';

   ckpstmt  varchar2(1024) := 'alter system checkpoint global';

begin

   for rlcRec in rlc loop

         stmt := 'alter database add logfile thread ' ||

                 rlcRec.thr || ' ''+LOG'' size ' ||

                 rlcRec.bytes_k || 'K';

         execute immediate stmt;

         begin

            stmt := 'alter database drop logfile group ' || rlcRec.grp;

            dbms_output.put_line(stmt);

            execute immediate stmt;

         exception

            when others then

               execute immediate swtstmt;

               execute immediate ckpstmt;

               execute immediate stmt;

         end;

   end loop;

end;

/





跟蹤日誌

Thu Aug 21 15:36:52 2014

alter database add logfile thread 1 '+LOG' size 51200K

Thu Aug 21 15:36:55 2014

SUCCESS: diskgroup LOG was mounted

Thu Aug 21 15:37:53 2014

SUCCESS: diskgroup LOG was dismounted

Starting control autobackup

Thu Aug 21 15:38:05 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0a'

Completed: alter database add logfile thread 1 '+LOG' size 51200K

Thu Aug 21 15:38:05 2014

alter database drop logfile group 1

Starting control autobackup

Thu Aug 21 15:38:18 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0b'

Completed: alter database drop logfile group 1

Thu Aug 21 15:38:18 2014

alter database add logfile thread 1 '+LOG' size 51200K

SUCCESS: diskgroup LOG was mounted

Thu Aug 21 15:39:24 2014

SUCCESS: diskgroup LOG was dismounted

Starting control autobackup

Thu Aug 21 15:40:01 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0c'

Completed: alter database add logfile thread 1 '+LOG' size 204800K

Thu Aug 21 15:40:01 2014

alter database drop logfile group 2

Starting control autobackup

Thu Aug 21 15:40:16 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0d'

Completed: alter database drop logfile group 2

Thu Aug 21 15:40:16 2014

alter database add logfile thread 1 '+LOG' size 51200K

SUCCESS: diskgroup LOG was mounted

Thu Aug 21 15:41:24 2014

SUCCESS: diskgroup LOG was dismounted

Thu Aug 21 15:41:34 2014

Starting control autobackup

Thu Aug 21 15:41:58 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0e'

Completed: alter database add logfile thread 1 '+LOG' size 51200K

Thu Aug 21 15:41:58 2014

alter database drop logfile group 3

Starting control autobackup

Thu Aug 21 15:42:13 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-0f'

Completed: alter database drop logfile group 3

Thu Aug 21 15:42:13 2014

alter database add logfile thread 1 '+LOG' size 51200K

SUCCESS: diskgroup LOG was mounted

Thu Aug 21 15:46:28 2014

SUCCESS: diskgroup LOG was dismounted

Starting control autobackup

Thu Aug 21 15:47:05 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-10'

Completed: alter database add logfile thread 1 '+LOG' size 51200K

Thu Aug 21 15:47:05 2014

alter database drop logfile group 4

ORA-1623 signalled during: alter database drop logfile group 4...

Thu Aug 21 15:47:09 2014

SUCCESS: diskgroup LOG was mounted

Thread 1 advanced to log sequence 153

  Current log# 1 seq# 153 mem# 0: +LOG/prod/onlinelog/group_1.257.856207363

Thu Aug 21 15:47:23 2014

alter database drop logfile group 4

Thu Aug 21 15:47:27 2014

Starting control autobackup

Thu Aug 21 15:47:41 2014

Control autobackup written to DISK device

        handle '/arch/ctl_c-267173497-20140821-11'

Completed: alter database drop logfile group 4


15、設定歸檔目錄到ASM

15.1、備份歸檔

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

15.2、修改歸檔目錄

SYS@ prod>shutdown immediate;

SYS@ prod>startup nomount;

SYS@ prod>alter system set log_archive_dest_1='LOCATION=+ARCH';

SYS@ prod>alter database mount;

SYS@ prod>archive log list;


15.3、切日誌組檢視

SYS@ prod>alter system switch logfile;

SYS@ prod>alter system switch logfile;

SYS@ prod>alter system switch logfile;

SYS@ prod>alter system switch logfile;



16、db_create_file_dest引數修改




【參考資料】
oracle 10G 官方文件
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmasm.htm#i1016581


呂星昊
2014.10.12


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29475508/viewspace-1296477/，如需轉載，請註明出處，否則將追究法律責任。

相關文章