實現資料庫由檔案系統遷移到 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 將spfile從ASM裡遷移到檔案系統ASM
- 資料檔案遷移至其他磁碟組
- 遷移ASM磁碟組ASM
- EAS附件表由資料庫遷移到FTP資料庫FTP
- WSL子系統檔案遷移至其他磁碟
- MySQL資料庫遷移到PostgresMySql資料庫
- 【資料庫資料恢復】Oracle資料庫ASM磁碟組掉線如何恢復資料？資料庫資料恢復OracleASM
- WSL遷移到其他磁碟
- 【資料庫資料恢復】ASM磁碟組掉線的Oracle資料庫資料恢復案例資料庫資料恢復ASMOracle
- 達夢資料庫系統表空間資料檔案遷移過程資料庫
- asm磁碟組依賴導致資料庫自啟動報錯ASM資料庫
- ASM磁碟組限制ASM
- 將ServiceLoader遷移到Java 9模組系統 - frankelJava
- Oracle資料庫 ASM磁碟線上擴容Oracle資料庫ASM
- 遷移OCR和VotingDisk並刪除原ASM磁碟組ASM
- 怎麼把達夢資料庫例項從Windows遷移到Linux系統資料庫WindowsLinux
- FastDFS檔案系統遷移和資料恢復AST資料恢復
- ORACLE RAC ASM資料檔案遷移OMF檔案報錯ORA-01276解決OracleASM
- ASM叢集檔案系統ACFS（ASM Cluster File System）ASM
- 【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結ASM
- 磁碟管理與檔案系統
- 達夢遷移工具之MySQL資料庫遷移到達夢MySql資料庫
- 你的資料庫真的需要遷移到雲嗎？資料庫
- 在ASM磁碟組中刪除歸檔日誌報ORA-15028ASM
- 達夢資料庫資料檔案遷移過程資料庫
- 輕鬆實現織夢網站資料遷移到新站點網站
- 從定位資料塊所在ASM磁碟到ASM strippingASM
- 【ASM】Oracle asm磁碟被格式化，如何掛載該磁碟組ASMOracle
- 【ASM】Oracle asm刪除磁碟組注意事項ASMOracle
- 12c複製 RAC ASM中的密碼檔案到檔案系統ASM密碼
- ASM磁碟組更換磁碟的操作方法ASM
- 帝國CMS系統資料庫配置檔案是哪個檔案？資料庫
- 檔案系統(四)：FAT32檔案系統實現原理
- [資料庫系統]儲存和檔案結構資料庫
- 檔案系統與資料庫的優缺點資料庫
- Linux磁碟管理與檔案系統（關於磁碟方面）Linux
- PayPal如何將Teradata資料倉儲遷移到BigQuery實現產品分析
- python實現圖書管理系統——通過excel檔案或者TXT檔案存放資料PythonExcel
- 04 磁碟儲存和檔案系統