溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過輔助庫（Auxiliary）做ASM遷移案例
系統環境：
操作系統：RedHat EL55
Oracle ： Oracle 10gR2
通過輔助庫建立ASM遷移，可以很方便將文件系統的存儲異機遷移；并且可減少數據庫的停機時間，本案例為測試案例，目標庫和輔助庫都在同一臺機器上。
1、建立ASM實例
[oracle@rh65 ~]$export ORACLE_SID=+ASM
[oracle@rh65 ~]$sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 11:39:49 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
11:39:50 SYS@ +ASM>startup nomount
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
11:39:57 SYS@ +ASM>select name ,state from v$asm_diskgroup;
NAME STATE
------------------------------ -----------
DG1 DISMOUNTED
RCY1 DISMOUNTED
Elapsed: 00:00:00.24
11:40:14 SYS@ +ASM>alter diskgroup dg1 mount;
Diskgroup altered.
Elapsed: 00:00:04.88
11:40:29 SYS@ +ASM>alter diskgroup rcy1 mount;
Diskgroup altered.
Elapsed: 00:00:04.77
2、建立并配置輔助庫
Target DB：test1
Auxiliary DB: test1asm
11:41:58 SYS@ test1>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string test1
db_unique_name string test1
global_names boolean FALSE
instance_name string test1
service_names string test1
11:42:06 SYS@ test1>show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfiletest1.ora
11:42:11 SYS@ test1>create pfile from spfile;
File created.
建立輔助庫初始化參數文件：
[oracle@rh65 dbs]$cp inittest1.ora inittest1asm.ora
[oracle@rh65 dbs]$ cat inittest1asm.ora
*.background_dump_dest='$ORACLE_BASE/admin/test1asm/bdump'
*.control_files='+dg1/test1asm/controlfile/control01.ctl'
*.core_dump_dest='$ORACLE_BASE/admin/test1asm/cdump'
*.db_block_size=8192
*.db_cache_size=30M#DEMO
*.db_file_multiblock_read_count=16
*.db_name='test1asm'
*.instance_name='test1asm'
*.log_archive_dest_1='location=+rcy1'
*.log_archive_format='arch_%t_%s_%r.log'
*.optimizer_mode='choose'
*.parallel_threads_per_cpu=4#SMALL
*.pga_aggregate_target=30M#DEMO
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.sga_target=240M
*.shared_pool_size=20M#DEMO
*.star_transformation_enabled='true'
*.undo_management='auto'
*.undo_tablespace='undotbs1'
*.user_dump_dest='$ORACLE_BASE/admin/test1asm/udump'
*.db_create_file_dest='+DG1'
*.db_file_name_convert=("/u01/app/oracle/oradata/test1","+dg1/test1asm/datafile","/u01/app/oracle/oradata/test1","+dg1/test1asm/tempfile")
*.log_file_name_convert=("/u01/app/oracle/oradata/test1","+dg1/test1asm/onlinelog")
db_recovery_file_dest='+rcy1'
db_recovery_file_dest_size=2g
*.audit_file_dest='$ORACLE_BASE/admin/test1asm/adump'/bdump
建立輔助庫相關目錄：
[oracle@rh65 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/cdump
[oracle@rh65 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/udump
[oracle@rh65 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/adump
建立口令文件：
[oracle@rh65 dbs]$ orapwd file=orapwtest1asm password=oracle entries=3
建立tnsnames文件：
[oracle@rh65 admin]$ cat tnsnames.ora
TEST1ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1asm)
( UR = A )
)
)
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh65)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)
3、遷移文件系統到ASM存儲
對目標庫備份：
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/disk2/bak/test1/auto_ctl_%d_%F';
RMAN> run{
2> shutdown immediate;
3> startup force mount;
4> allocate channel ch2 device type disk;
5> backup as compressed backupset database format '/disk2/bak/test1/%d_%s.bak'
6> plus archivelog format '/disk2/bak/test1/arch_%U.bak'
7> tag='full_log';
8> release channel ch2;}
啟動輔助庫實例：
[oracle@rh65 dbs]$export ORACLE_SID=test1asm
[oracle@rh65 dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 12:17:22 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
12:17:22 SYS@ test1asm>startup nomount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 58722068 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
15:47:09 SYS@ test1asm>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/test1,
+dg1/test1asm/datafile, /u01/
app/oracle/oradata/test1, +dg1
/test1asm/tempfile
db_name string test1asm
db_unique_name string test1asm
instance_name string test1asm
log_file_name_convert string /u01/app/oracle/oradata/test1,
+dg1/test1asm/onlinelog
service_names string test1asm
遷移數據文件到ASM存儲：
export ORACLE_SID=test1
[oracle@rh65 dbs]$ rman target sys/oracle@test1 auxiliary sys/oracle@test1asm
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 15:34:03 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST1 (DBID=1174898526)
connected to auxiliary database: TEST1ASM (not mounted)
RMAN>duplicate target database to test1asm;
Starting Duplicate Db at 29-MAY-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK
contents of Memory Script:
{
set until scn 259211;
set newname for datafile 1 to
"+DG1/test1asm/datafile/system01.dbf";
set newname for datafile 2 to
"+DG1/test1asm/datafile/undotbs01.dbf";
set newname for datafile 3 to
"+DG1/test1asm/datafile/sysaux01.dbf";
set newname for datafile 4 to
"+DG1/test1asm/datafile/users01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 29-MAY-14
using channel ORA_AUX_DISK_1
skipping datafile 1; already restored to file +DG1/test1asm/datafile/system01.dbf
skipping datafile 2; already restored to file +DG1/test1asm/datafile/undotbs01.dbf
skipping datafile 3; already restored to file +DG1/test1asm/datafile/sysaux01.dbf
skipping datafile 4; already restored to file +DG1/test1asm/datafile/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 29-MAY-14
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M REUSE
DATAFILE
'+DG1/test1asm/datafile/system01.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=848849467 filename=+DG1/test1asm/datafile/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=848849467 filename=+DG1/test1asm/datafile/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=848849467 filename=+DG1/test1asm/datafile/users01.dbf
contents of Memory Script:
{
set until scn 259211;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 29-MAY-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK
starting media recovery
archive log thread 1 sequence 17 is already on disk as file /disk1/arch_test1/arch_1_17_797856158.log
archive log thread 1 sequence 18 is already on disk as file /disk1/arch_test1/arch_1_18_797856158.log
archive log filename=/disk1/arch_test1/arch_1_17_797856158.log thread=1 sequence=17
archive log filename=/disk1/arch_test1/arch_1_18_797856158.log thread=1 sequence=18
media recovery complete, elapsed time: 00:00:08
Finished recover at 29-MAY-14
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/29/2014 15:37:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
（以上錯誤信息：是因為Instance shutdown后，連接不到Instance，可以通過建立靜態監聽避免這個錯誤，這個錯誤不影響數據的遷移）
OPEN輔助庫：
5:39:01 SYS@ test1asm>startup mount;
ORACLE instance started.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 58722068 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
15:39:28 SYS@ test1asm>select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------
+DG1/test1asm/datafile/system01.dbf
+DG1/test1asm/datafile/undotbs01.dbf
+DG1/test1asm/datafile/sysaux01.dbf
+DG1/test1asm/datafile/users01.dbf
Elapsed: 00:00:00.20
15:39:39 SYS@ test1asm>select name from v$controlfile;
NAME
--------------------------------------------------------------------------------------------------------
+DG1/test1asm/controlfile/control01.ctl
Elapsed: 00:00:00.04
15:39:47 SYS@ test1asm>select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------
+DG1/test1asm/onlinelog/redo03a.log
+DG1/test1asm/onlinelog/redo02a.log
+DG1/test1asm/onlinelog/redo01a.log
Elapsed: 00:00:00.05
Open database：
15:41:39 SYS@ test1asm>alter database open resetlogs;
Database altered.
Elapsed: 00:00:59.79
查看數據信息：
15:42:47 SYS@ test1asm>select count(*) from scott.emp1;
COUNT(*)
----------
28
Elapsed: 00:00:00.11
15:43:20 SYS@ test1asm>select count(*) from scott.dept1;
COUNT(*)
----------
4
Elapsed: 00:00:00.06
15:43:29 SYS@ test1asm>
建立臨時表空間：
15:46:46 SYS@ test1asm>create temporary tablespace temp;
Tablespace created.
Elapsed: 00:00:03.13
15:47:05 SYS@ test1asm>select name from v$tempfile;
NAME
--------------------------------------------------------------------------------------------------------
+DG1/test1asm/tempfile/temp.265.848850423
4、數據遷移告警日志
alert 日志：
Starting background process ASMB
ASMB started with pid=16, OS id=6507
Starting background process RBAL
RBAL started with pid=17, OS id=6511
Loaded ASM Library - Generic Linux, version 2.0.4 (KABI_V2) library for asmlib interface
Thu May 29 15:36:00 2014
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
Thu May 29 15:36:03 2014
The input backup piece /disk2/bak/test1/TEST1_8.bak is in compressed format.
SUCCESS: diskgroup DG1 was mounted
Thu May 29 15:36:21 2014
Full restore complete of datafile 4 to datafile copy +DG1/test1asm/datafile/users01.dbf. Elapsed time: 0:00:18
checkpoint is 256751
Thu May 29 15:36:33 2014
Full restore complete of datafile 2 to datafile copy +DG1/test1asm/datafile/undotbs01.dbf. Elapsed time: 0:00:30
checkpoint is 256751
Full restore complete of datafile 1 to datafile copy +DG1/test1asm/datafile/system01.dbf. Elapsed time: 0:00:38
checkpoint is 256751
Thu May 29 15:36:56 2014
SUCCESS: diskgroup DG1 was dismounted
Full restore complete of datafile 3 to datafile copy +DG1/test1asm/datafile/sysaux01.dbf. Elapsed time: 0:00:53
checkpoint is 256751
Thu May 29 15:36:58 2014
CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M REUSE
DATAFILE
'+DG1/test1asm/datafile/system01.dbf'
CHARACTER SET ZHS16GBK
Thu May 29 15:36:58 2014
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
Thu May 29 15:37:03 2014
SUCCESS: diskgroup DG1 was mounted
Thu May 29 15:37:03 2014
Setting recovery target incarnation to 1
Thu May 29 15:37:03 2014
Successful mount of redo thread 1, with mount id 891011546
Thu May 29 15:37:03 2014
Completed: CREATE CONTROLFILE REUSE SET DATABASE "TEST1ASM" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DG1/test1asm/onlinelog/redo01a.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DG1/test1asm/onlinelog/redo02a.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DG1/test1asm/onlinelog/redo03a.log' ) SIZE 100 M REUSE
DATAFILE
'+DG1/test1asm/datafile/system01.dbf'
CHARACTER SET ZHS16GBK
Switch of datafile 2 complete to datafile copy
checkpoint is 256751
Switch of datafile 3 complete to datafile copy
checkpoint is 256751
Switch of datafile 4 complete to datafile copy
checkpoint is 256751
Thu May 29 15:37:07 2014
alter database recover datafile list clear
Completed: alter database recover datafile list clear
Thu May 29 15:37:07 2014
alter database recover datafile list
1 , 2 , 3 , 4
Completed: alter database recover datafile list
1 , 2 , 3 , 4
Thu May 29 15:37:07 2014
alter database recover if needed
start until change 259211 using backup controlfile
Media Recovery Start
ORA-279 signalled during: alter database recover if needed
start until change 259211 using backup controlfile
...
Thu May 29 15:37:07 2014
alter database recover logfile '/disk1/arch_test1/arch_1_17_797856158.log'
Thu May 29 15:37:07 2014
Media Recovery Log /disk1/arch_test1/arch_1_17_797856158.log
ORA-279 signalled during: alter database recover logfile '/disk1/arch_test1/arch_1_17_797856158.log'...
Thu May 29 15:37:10 2014
alter database recover logfile '/disk1/arch_test1/arch_1_18_797856158.log'
Thu May 29 15:37:10 2014
Media Recovery Log /disk1/arch_test1/arch_1_18_797856158.log
Thu May 29 15:37:10 2014
Incomplete Recovery applied until change 259211
Thu May 29 15:37:10 2014
Media Recovery Complete (test1asm)
Completed: alter database recover logfile '/disk1/arch_test1/arch_1_18_797856158.log'
Shutting down instance: further logons disabled
@至此，從文件系統遷移數據到ASM存儲完成，通過ASM存儲，可以提升數據庫的I/O性能，并提高對數據的保護能力。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
