通过辅助库（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性能，并提高对数据的保护能力。
Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AMInnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AMKey metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.
What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AMUsingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB
Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AMMySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.
MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AMMySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.
How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AMMySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.
MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AMThe MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.
Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AMMySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.
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
VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
SublimeText3 Linux new version
SublimeText3 Linux latest version
Dreamweaver CS6
Visual web development tools
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
