實現資料庫由檔案系統遷移到 ASM 磁碟組中
一、 儲存規劃
二、 建立 VG、LV
[root@aix213 /]$lspv
hdisk1 00c6d0ef0395eef2 rootvg active
hdisk0 00032fb8843fafe9 racvg active
[root@aix213 dev]$mklv -y oradata -t raw racvg 160
oradata
[root@aix213 dev]$mklv -y recovery -t raw racvg 160
recovery
[root@aix213 dev]$lsvg -l racvg
racvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
oradata raw 160 160 1 closed/syncd N/A
recovery raw 160 160 1 closed/syncd N/A
更改宿主目錄
[root@aix213 /]$chown oracle:dba /dev/roradata
[root@aix213 /]$chown oracle:dba /dev/oradata
[root@aix213 /]$chown oracle:dba /dev/recovery
[root@aix213 /]$chown oracle:dba /dev/rrecovery
三、 建立 ASM 磁碟組
1、 建立+ASM 例項的引數檔案
[oracle@aix213 dbs]$vi init+ASM.ora
"init+ASM.ora" 6 lines, 246 characters
*.background_dump_dest='/u01/app/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/app/oracle/admin/+ASM/cdump'
*.remote_login_passwordfile='EXCLUSIVE'
*.user_dump_dest='/u01/app/oracle/admin/+ASM/udump'
instance_type='asm'
large_pool_size=12M
asm_diskstring='/dev/roradata','/dev/rrecovery'
~
2、 用 root 使用者啟動 CSS 服務
[root@aix213 bin]$./localconfig delete
/etc/oracle does not exist. Creating it now.
./localconfig[715]: /etc/init.cssd: not found
[root@aix213 bin]$./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'system'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
aix213
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@aix213 bin]$
3、 建立 ASM 磁碟組
[oracle@aix213 bin]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 25 16:42:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$Instance;
STATUS
------------
STARTED
SQL> desc v$asm_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
SQL> select NAME, STATE from v$asm_diskgroup;
no rows selected
SQL> create diskgroup DATA external redundancy disk '/dev/roradata';
Diskgroup created.
SQL> create diskgroup REC external redundancy disk '/dev/rrecovery';
Diskgroup created.
SQL> select NAME, STATE from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DATA MOUNTED
REC MOUNTED
[實施步驟]
一、 將原庫控制檔案所在路徑修改為 ASM 磁碟組上的路徑
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DATA';
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
二、 用 rman 遷移目標資料庫的控制檔案和資料檔案
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
1、恢復一個最近的 0 級備份的控制檔案
RMAN> restore controlfile from '/back/mirror/zhang_c-2690774443-20140625-13.ctl';
Starting restore at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+DATA/zhang1/controlfile/current.256.851192723
Finished restore at 25-JUN-14
2、 以 COPY 的形式備份一個新的資料檔案在 ASM 磁碟上
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 25-JUN-14
Starting implicit crosscheck backup at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 41 objects
Finished implicit crosscheck backup at 25-JUN-14
Starting implicit crosscheck copy at 25-JUN-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-14
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/zhang1/system01.dbf
output filename=+DATA/zhang1/datafile/system.257.851192845 tag=TAG20140625T182724 recid=34
stamp=851192898
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/zhang1/report01.dbf
output filename=+DATA/zhang1/datafile/report.258.851192901 tag=TAG20140625T182724 recid=35
stamp=851192940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/zhang1/sysaux01.dbf
output filename=+DATA/zhang1/datafile/sysaux.259.851192945 tag=TAG20140625T182724 recid=36
stamp=851192979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/zhang1/example01.dbf
output filename=+DATA/zhang1/datafile/example.260.851192981 tag=TAG20140625T182724 recid=37
stamp=851193004
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/zhang1/undotbs01.dbf
output filename=+DATA/zhang1/datafile/undotbs1.261.851193007 tag=TAG20140625T182724 recid=38
stamp=851193011
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/zhang1/users01.dbf
output filename=+DATA/zhang1/datafile/users.262.851193015 tag=TAG20140625T182724 recid=39
stamp=851193014
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUN-14
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
3、 令資料庫中的控制檔案指向新的備份所在的位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/zhang1/datafile/system.257.851192845"
datafile 2 switched to datafile copy "+DATA/zhang1/datafile/undotbs1.261.851193007"
datafile 3 switched to datafile copy "+DATA/zhang1/datafile/sysaux.259.851192945"
datafile 4 switched to datafile copy "+DATA/zhang1/datafile/users.262.851193015"
datafile 5 switched to datafile copy "+DATA/zhang1/datafile/example.260.851192981"
datafile 6 switched to datafile copy "+DATA/zhang1/datafile/report.258.851192901"
4、對資料庫進行恢復
RMAN> recover database;
Starting recover at 25-JUN-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 133 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo01.log
archive log thread 1 sequence 134 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo02.log
archive log thread 1 sequence 135 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo03.log
archive log filename=/u01/app/oracle/oradata/zhang1/redo01.log thread=1 sequence=133
archive log filename=/u01/app/oracle/oradata/zhang1/redo02.log thread=1 sequence=134
archive log filename=/u01/app/oracle/oradata/zhang1/redo03.log thread=1 sequence=135
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-JUN-14
RMAN>
5、開庫檢視
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
四、將臨時表空間和 redo 日誌檔案也轉移到 ASM 磁碟組中
1、Temp 檔案：
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> select file# ,name,status from v$tempfile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/zhang1/temp01.dbf ONLINE
2 +DATA/zhang1/tempfile/temp.263.851193897 ONLINE
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/zhang1/temp01.dbf';
Tablespace altered.
2、Redo 日誌：
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
6 rows selected.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
刪除檔案系統上的 redo.log:
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo01.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo02.log';
Database altered.
刪除當前日誌：
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES ACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system checkpoint;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES INACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo03.log';
Database altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
五、將 spfile 轉移到 ASM 磁碟中
SQL> startup nomount pfile='/tmp/11.ora';
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
SQL> create spfile='+DATA/spfilezhang1.ora' from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL>
六、修改歸檔路徑
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /back/archive
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
SQL> show parameter log;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/back/archive
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 14306304
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set log_archive_dest_1='LOCATION=+REC/';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +REC
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
測試一下是否可以歸檔：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO INACTIVE 899420
5 1 3 52428800 1 NO CURRENT 901827
6 1 0 52428800 1 YES UNUSED 0
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 YES INACTIVE 899420
5 1 3 52428800 1 YES INACTIVE 901827
6 1 4 52428800 1 NO CURRENT 901853
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
thread_1_seq_3.257.851198147
七、刪除原來檔案系統上的資料檔案
[oracle@aix213 dbs]$cd $ORACLE_BASE
[oracle@aix213 oracle]$ls
admin flash_recovery_area oraInventory oradata product
[oracle@aix213 oracle]$cd oradata/
[oracle@aix213 oradata]$ls
zhang1
[oracle@aix213 oradata]$rm -rf zhang1
檢視磁碟平衡情況：
select * from V$ASM_OPERATION
二、 建立 VG、LV
[root@aix213 /]$lspv
hdisk1 00c6d0ef0395eef2 rootvg active
hdisk0 00032fb8843fafe9 racvg active
[root@aix213 dev]$mklv -y oradata -t raw racvg 160
oradata
[root@aix213 dev]$mklv -y recovery -t raw racvg 160
recovery
[root@aix213 dev]$lsvg -l racvg
racvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
oradata raw 160 160 1 closed/syncd N/A
recovery raw 160 160 1 closed/syncd N/A
更改宿主目錄
[root@aix213 /]$chown oracle:dba /dev/roradata
[root@aix213 /]$chown oracle:dba /dev/oradata
[root@aix213 /]$chown oracle:dba /dev/recovery
[root@aix213 /]$chown oracle:dba /dev/rrecovery
三、 建立 ASM 磁碟組
1、 建立+ASM 例項的引數檔案
[oracle@aix213 dbs]$vi init+ASM.ora
"init+ASM.ora" 6 lines, 246 characters
*.background_dump_dest='/u01/app/oracle/admin/+ASM/bdump'
*.core_dump_dest='/u01/app/oracle/admin/+ASM/cdump'
*.remote_login_passwordfile='EXCLUSIVE'
*.user_dump_dest='/u01/app/oracle/admin/+ASM/udump'
instance_type='asm'
large_pool_size=12M
asm_diskstring='/dev/roradata','/dev/rrecovery'
~
2、 用 root 使用者啟動 CSS 服務
[root@aix213 bin]$./localconfig delete
/etc/oracle does not exist. Creating it now.
./localconfig[715]: /etc/init.cssd: not found
[root@aix213 bin]$./localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'system'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
aix213
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@aix213 bin]$
3、 建立 ASM 磁碟組
[oracle@aix213 bin]$!sql
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 25 16:42:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$Instance;
STATUS
------------
STARTED
SQL> desc v$asm_diskgroup;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
UNBALANCED VARCHAR2(1)
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
SQL> select NAME, STATE from v$asm_diskgroup;
no rows selected
SQL> create diskgroup DATA external redundancy disk '/dev/roradata';
Diskgroup created.
SQL> create diskgroup REC external redundancy disk '/dev/rrecovery';
Diskgroup created.
SQL> select NAME, STATE from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DATA MOUNTED
REC MOUNTED
[實施步驟]
一、 將原庫控制檔案所在路徑修改為 ASM 磁碟組上的路徑
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DATA';
System altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
二、 用 rman 遷移目標資料庫的控制檔案和資料檔案
RMAN> startup nomount;
Oracle instance started
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
1、恢復一個最近的 0 級備份的控制檔案
RMAN> restore controlfile from '/back/mirror/zhang_c-2690774443-20140625-13.ctl';
Starting restore at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+DATA/zhang1/controlfile/current.256.851192723
Finished restore at 25-JUN-14
2、 以 COPY 的形式備份一個新的資料檔案在 ASM 磁碟上
RMAN> alter database mount
2> ;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 25-JUN-14
Starting implicit crosscheck backup at 25-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 41 objects
Finished implicit crosscheck backup at 25-JUN-14
Starting implicit crosscheck copy at 25-JUN-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUN-14
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/zhang1/system01.dbf
output filename=+DATA/zhang1/datafile/system.257.851192845 tag=TAG20140625T182724 recid=34
stamp=851192898
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/zhang1/report01.dbf
output filename=+DATA/zhang1/datafile/report.258.851192901 tag=TAG20140625T182724 recid=35
stamp=851192940
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/zhang1/sysaux01.dbf
output filename=+DATA/zhang1/datafile/sysaux.259.851192945 tag=TAG20140625T182724 recid=36
stamp=851192979
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/zhang1/example01.dbf
output filename=+DATA/zhang1/datafile/example.260.851192981 tag=TAG20140625T182724 recid=37
stamp=851193004
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/zhang1/undotbs01.dbf
output filename=+DATA/zhang1/datafile/undotbs1.261.851193007 tag=TAG20140625T182724 recid=38
stamp=851193011
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/zhang1/users01.dbf
output filename=+DATA/zhang1/datafile/users.262.851193015 tag=TAG20140625T182724 recid=39
stamp=851193014
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 25-JUN-14
RMAN-06497: WARNING: control file is not current, control file autobackup skipped
3、 令資料庫中的控制檔案指向新的備份所在的位置
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/zhang1/datafile/system.257.851192845"
datafile 2 switched to datafile copy "+DATA/zhang1/datafile/undotbs1.261.851193007"
datafile 3 switched to datafile copy "+DATA/zhang1/datafile/sysaux.259.851192945"
datafile 4 switched to datafile copy "+DATA/zhang1/datafile/users.262.851193015"
datafile 5 switched to datafile copy "+DATA/zhang1/datafile/example.260.851192981"
datafile 6 switched to datafile copy "+DATA/zhang1/datafile/report.258.851192901"
4、對資料庫進行恢復
RMAN> recover database;
Starting recover at 25-JUN-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 133 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo01.log
archive log thread 1 sequence 134 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo02.log
archive log thread 1 sequence 135 is already on disk as file
/u01/app/oracle/oradata/zhang1/redo03.log
archive log filename=/u01/app/oracle/oradata/zhang1/redo01.log thread=1 sequence=133
archive log filename=/u01/app/oracle/oradata/zhang1/redo02.log thread=1 sequence=134
archive log filename=/u01/app/oracle/oradata/zhang1/redo03.log thread=1 sequence=135
media recovery complete, elapsed time: 00:00:03
Finished recover at 25-JUN-14
RMAN>
5、開庫檢視
SQL> startup
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
四、將臨時表空間和 redo 日誌檔案也轉移到 ASM 磁碟組中
1、Temp 檔案：
SQL> alter tablespace temp add tempfile '+DATA';
Tablespace altered.
SQL> select file# ,name,status from v$tempfile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/app/oracle/oradata/zhang1/temp01.dbf ONLINE
2 +DATA/zhang1/tempfile/temp.263.851193897 ONLINE
SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/zhang1/temp01.dbf';
Tablespace altered.
2、Redo 日誌：
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> alter database add logfile '+DATA' size 50M;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/zhang1/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/zhang1/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/zhang1/redo01.log NO
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
6 rows selected.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
1 1 0 52428800 1 YES UNUSED 0
2 1 0 52428800 1 YES UNUSED 0
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
6 rows selected.
刪除檔案系統上的 redo.log:
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo01.log';
Database altered.
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo02.log';
Database altered.
刪除當前日誌：
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 NO CURRENT 898271
4 1 0 52428800 1 YES UNUSED 0
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES ACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system checkpoint;
System altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
3 1 1 52428800 1 YES INACTIVE 898271
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter database drop logfile '/u01/app/oracle/oradata/zhang1/redo03.log';
Database altered.
SQL> select GROUP# ,THREAD#,SEQUENCE# , BYTES,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
4 ONLINE +DATA/zhang1/onlinelog/group_4.264.851194637 NO
5 ONLINE +DATA/zhang1/onlinelog/group_5.265.851194653 NO
6 ONLINE +DATA/zhang1/onlinelog/group_6.266.851194693 NO
五、將 spfile 轉移到 ASM 磁碟中
SQL> startup nomount pfile='/tmp/11.ora';
ORACLE instance started.
Total System Global Area 1207959552 bytes
Fixed Size 2020456 bytes
Variable Size 318770072 bytes
Database Buffers 872415232 bytes
Redo Buffers 14753792 bytes
SQL> create spfile='+DATA/spfilezhang1.ora' from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL>
六、修改歸檔路徑
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilezhang1.ora
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /back/archive
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
SQL> show parameter log;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
log_archive_config string
log_archive_dest string
log_archive_dest_1 string LOCATION=/back/archive
log_archive_dest_10 string
log_archive_dest_2 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
log_archive_trace integer 0
log_buffer integer 14306304
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
log_file_name_convert string
logmnr_max_persistent_sessions integer 1
remote_login_passwordfile string EXCLUSIVE
SQL> alter system set log_archive_dest_1='LOCATION=+REC/';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +REC
Oldest online log sequence 2
Next log sequence to archive 2
Current log sequence 2
測試一下是否可以歸檔：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO CURRENT 899420
5 1 0 52428800 1 YES UNUSED 0
6 1 0 52428800 1 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 NO INACTIVE 899420
5 1 3 52428800 1 NO CURRENT 901827
6 1 0 52428800 1 YES UNUSED 0
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------
4 1 2 52428800 1 YES INACTIVE 899420
5 1 3 52428800 1 YES INACTIVE 901827
6 1 4 52428800 1 NO CURRENT 901853
ASMCMD> pwd
+rec/zhang1/archivelog/2014_06_25
ASMCMD> ls
thread_1_seq_2.256.851198083
thread_1_seq_3.257.851198147
七、刪除原來檔案系統上的資料檔案
[oracle@aix213 dbs]$cd $ORACLE_BASE
[oracle@aix213 oracle]$ls
admin flash_recovery_area oraInventory oradata product
[oracle@aix213 oracle]$cd oradata/
[oracle@aix213 oradata]$ls
zhang1
[oracle@aix213 oradata]$rm -rf zhang1
檢視磁碟平衡情況：
select * from V$ASM_OPERATION
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29802484/viewspace-1852002/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫從檔案系統遷移到ASM資料庫ASM
- oracle asm 儲存 a磁碟組中的資料檔案 遷移到b磁碟組實施步驟OracleASM
- 將資料庫從ASM遷移到檔案系統資料庫ASM
- 將資料庫從檔案系統遷移到ASM資料庫ASM
- oracle 將表空間下的資料檔案從檔案系統遷移到ASM磁碟組OracleASM
- 利用RMAN將資料庫從檔案系統遷移到ASM資料庫ASM
- 把檔案系統的資料檔案遷移到ASM儲存ASM
- 移動資料檔案從檔案系統到ASM磁碟組中ASM
- 從檔案系統遷移到ASM上ASM
- oralce 從檔案系統遷移到ASMASM
- 利用RMAN將資料庫從檔案系統遷移到ASM(單例項)資料庫ASM單例
- 資料庫遷移到ASM資料庫ASM
- 使用RAM將檔案系統遷移到ASMASM
- ASM下資料檔案遷移至不同磁碟組小記ASM
- 【資料遷移】RMAN遷移資料庫到ASM(一)建立ASM磁碟組資料庫ASM
- 將spfile從ASM裡遷移到檔案系統ASM
- ORACLE 資料庫 ASM磁碟組上新增控制檔案Oracle資料庫ASM
- 採用Duplicate不連線target資料庫的方式將檔案系統遷移到ASM資料庫ASM
- 利用rman將本地資料檔案遷移到asmASM
- 使用RMAN遷移檔案系統資料庫到ASM資料庫ASM
- 資料檔案遷移至其他磁碟組
- 如何遷移ASM資料檔案到檔案系統ASM
- 用rman從檔案系統遷移資料庫到asm資料庫ASM
- 將資料庫遷移到ASM的實驗記錄資料庫ASM
- 四、用rman從檔案系統遷移資料庫到asm資料庫ASM
- EAS附件表由資料庫遷移到FTP資料庫FTP
- Oracle 11g單例項ASM遷移到檔案系統Oracle單例ASM
- 遷移資料庫檔案到ASM資料庫ASM
- 遷移ASM磁碟組ASM
- 利用RMAN在檔案系統與ASM之間遷移資料庫ASM資料庫
- ASM檔案系統遷移ASM
- asm 檔案系統遷移ASM
- 資料泵實現資料遷移到異地庫
- 用oracle amdu 抽取asm磁碟組的資料檔案OracleASM
- 使用rman將資料庫遷移到ASM例項資料庫ASM
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- RAC資料庫新增ASM磁碟組(1)資料庫ASM
- 如何移動asm磁碟組內的資料檔案到另外一個磁碟組ASM