通过辅助库（Auxiliary）做ASM迁移案例
通过辅助库（Auxiliary）做ASM迁移案例 系统环境： 操作系统：RedHat EL55 Oracle ： Oracle 10gR2 通过辅助库建立ASM迁移，可以很方便将文件系统的存储异机迁移；并且可减少数据库的停机时间，本案例为测试案例，目标库和辅助库都在同一台机器上。 1、建立A
通过辅助库（Auxiliary）做ASM迁移案例
系统环境：
操作系统：RedHat EL55
Oracle ： Oracle 10gR2
通过辅助库建立ASM迁移，可以很方便将文件系统的存储异机迁移；并且可减少数据库的停机时间，本案例为测试案例，目标库和辅助库都在同一台机器上。
1、建立ASM实例
[oracle@rh55 ~]$export ORACLE_SID=+ASM
[oracle@rh55 ~]$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@rh55 dbs]$cp inittest1.ora inittest1asm.ora
[oracle@rh55 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@rh55 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/cdump
[oracle@rh55 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/udump
[oracle@rh55 dbs]$ mkdir -p $ORACLE_BASE/admin/test1asm/adump
建立口令文件：
[oracle@rh55 dbs]$ orapwd file=orapwtest1asm password=oracle entries=3
建立tnsnames文件：
[oracle@rh55 admin]$ cat tnsnames.ora
TEST1ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1asm)
( UR = A )
)
)
TEST1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rh55)(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 ch1 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 ch1;}
启动辅助库实例：
[oracle@rh55 dbs]$export ORACLE_SID=test1asm
[oracle@rh55 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@rh55 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性能，并提高对数据的保护能力。
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1378
52
How to migrate WeChat chat history to a new phone
Mar 26, 2024 pm 04:48 PM
1. Open the WeChat app on the old device, click [Me] in the lower right corner, select the [Settings] function, and click [Chat]. 2. Select [Chat History Migration and Backup], click [Migrate], and select the platform to which you want to migrate the device. 3. Click [Select chats to be migrated], click [Select all] in the lower left corner, or select chat records yourself. 4. After selecting, click [Start] in the lower right corner to log in to this WeChat account using the new device. 5. Then scan the QR code to start migrating chat records. Users only need to wait for the migration to complete.
Linux and Docker: How to migrate and synchronize containers across hosts?
Jul 29, 2023 pm 02:52 PM
Linux and Docker: How to migrate and synchronize containers across hosts? Summary: Docker is a popular containerization technology that provides a lightweight virtualization solution. In a multi-host environment, it is a very common requirement to migrate and synchronize containers across hosts. This article will introduce how to use Linux and Docker to implement cross-host migration and synchronization of containers, and provide some sample code for reference. Introduction The rise of containerization technology makes application deployment and migration more flexible and efficient. on multiple hosts
How to migrate and integrate projects in GitLab
Oct 27, 2023 pm 05:53 PM
How to migrate and integrate projects in GitLab Introduction: In the software development process, project migration and integration is an important task. As a popular code hosting platform, GitLab provides a series of convenient tools and functions to support project migration and integration. This article will introduce the specific steps for project migration and integration in GitLab, and provide some code examples to help readers better understand. 1. Project migration Project migration is to migrate the existing code base from a source code management system to GitLab
How to transfer WeChat chat history to another mobile phone
May 08, 2024 am 11:20 AM
1. On the old device, click "Me" → "Settings" → "Chat" → "Chat History Migration and Backup" → "Migrate". 2. Select the target platform device to be migrated, select the chat records to be migrated, and click "Start". 3. Log in with the same WeChat account on the new device and scan the QR code to start chat record migration.
Cloud Modernization with C++: Migrating Legacy Applications to the Cloud
Jun 01, 2024 am 09:21 AM
The best way to move legacy C++ applications to the cloud: Re-platform: Move the application code to a cloud-native platform (such as Kubernetes) and leverage cloud services. Cloudization: Deploy applications on cloud platforms and utilize cloud services without code refactoring.
How to transfer WeChat chat history to another phone
Mar 26, 2024 pm 04:50 PM
By opening WeChat on the old device, selecting "Chat History Migration and Backup" and following the prompts, users can migrate their chat history to the new device. The migration process involves selecting the chats that need to be migrated, scanning the QR code and waiting for the migration to complete.
How to perform server migration in Linux system
Jun 18, 2023 pm 10:01 PM
In enterprise IT systems, server migration is a regular task, which can help enterprises improve server utilization, meet business needs, update hardware equipment, etc. Although there are certain challenges in server migration in Linux systems, with reasonable planning and implementation, this work can be made simple and efficient. Below, we will introduce some key steps for server migration in Linux systems. Preparation work Before performing server migration, some preparations need to be made on the source server and target server.
How to connect iPhone 15's USB C to a mouse?
Oct 23, 2023 pm 11:13 PM
Apple has finally added USBC connections to the iPhone 15 and 15 Pro series, allowing a large number of computer accessories to be used on the iPhone. Some users may be wondering, is it possible to connect a keyboard and mouse to use the iPhone like a computer? 1: First enter settings, assisted use 2: Select touch 3: Select auxiliary touch 4: Activate auxiliary touch 5: After starting, you will see dots like when using the trackpad on iPad, and then set the settings for each button. To define different functions, such as home screen keys, auxiliary keys, etc., you can enter the following steps and go to "Auxiliary Usage" in "Settings". 6: Select touch 7: Assistive touch 8: Select device 9: Select the name of the mouse you connected 10: Add button, then click the button you want to set on the mouse 11: Then
