rman 同机复制数据库
来源:互联网 发布:东莞金域名苑 编辑:程序博客网 时间:2024/05/21 08:38
RMAN同机复制数据库
实验平台: redhat + oracle 10g
源库的相关信息:
ORACLE_BASE: /u01/app/oracle
ORACLE_HOME:/u01/app/oracle/product/10.2/db_1
ORACLE_SID:orcl
复制的数据库实例名假设为: AUX(大写)
注意:在windows环境中需要实例 oradim -new -sid AU (aux为windows保留字符不能使用)
步骤如下:
(1)构建辅助数据库目录结构
(2)修改init.ora 初始化文件
(3)创建辅助实例口令文件
(4)RMAN 备份源库
(5)配置监听
(6)启动辅助库至nomount 状态
(7)RMAN duplicate复制实例
(8)创建spfile
一.构建辅助数据库目录结构
1.1 Oracle data目录
[oracle@db1 oradata]$ ls
orcl
[oracle@db1 oradata]$ pwd
/u01/app/oracle/oradata
[oracle@db1 oradata]$mkdir AUX
[oracle@db1 oradata]$ ls
AUX orcl
1.2其他目录
[oracle@db1 admin]$ pwd
/u01/app/oracle/admin
[oracle@db1 admin]$ mkdir AUX (注意linux区分大小写)
[oracle@db1 admin]$ ls
AUX orcl
[oracle@db1 admin]$ cd orcl
[oracle@db1 orcl]$ ls
adump bdump cdump dpdump pfile udump
[oracle@db1 AUX]$ cd ..
[oracle@db1 admin]$ cd AUX
[oracle@db1 AUX]$ mkdir bdump
[oracle@db1 AUX]$ mkdir cdump
[oracle@db1 AUX]$ mkdir pfile
[oracle@db1 AUX]$ mkdir udump
[oracle@db1 AUX]$ ls
adump bdump cdump dpdump pfile udump
二.修改init.ora初始化文件
2.1生成源库的pfile文件,默认生成位置在$ORACLE_HOME/dbs目录下:
SQL> conn / as sysdba
Connected.
SQL> create pfile from spfile;
File created.
2.2进入$ORACLE_HOME/dbs目录,将init文件copy并重命名为initAUX.ora.这个命名格式必须和实例名相同,不然数据库不能识别。
[oracle@db1 AUX]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ ls
hc_orcl.dat init.ora lkORCL snapcf_orcl.f
initdw.ora initorcl.ora orapworcl spfileorcl.ora
[oracle@db1 dbs]$ cp initorcl.ora initAUX.ora
[oracle@db1 dbs]$ ls
hc_orcl.dat initdw.ora initorcl.ora orapworcl spfileorcl.ora
initAUX.orainit.ora lkORCL snapcf_orcl.f
2.3修改initAUX.ora文件,将orcl 改成AUX
[oracle@db1 dbs]$ more initAUX.ora
orcl.__db_cache_size=171966464
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=58720256
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/AUX/adump'
*.background_dump_dest='/u01/app/oracle/admin/AUX/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='/u01/app/oracle/oradata/AUX/control01.ctl','/u01/app/oracle/oradata/AUX/control02.ctl'
,'/u01/app/oracle/oradata/AUX/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/AUX/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='AUX'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/archivelog'
*.open_cursors=300
*.pga_aggregate_target=81788928
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=246415360
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/AUX/udump'
db_file_name_convert = ('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/AUX' )
log_file_name_convert = ('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/AUX')
其中红色部分就是修改的部分. Db_file_name_convert和log_file_name_convert两个参数是我们添加的,用来转换数据文件位置和redo log位置。 在复制完成后,可以删除这2个参数。
注意:
SQL> select name from v$datafile ;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/test01.dbf
日志文件
SQL> select MEMBER from v$logfile ;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
如果有多个不同目录,你写多次
三.创建AUX实例的口令文件
[oracle@db1 dbs]$ cd $ORACLE_HOME/bin
[oracle@db1 bin]$ orapwd file=$ORACLE_HOME/dbs/orapwAUX password=oracle
[oracle@db1 bin]$ cd $ORACLE_HOME/dbs
[oracle@db1 dbs]$ ls
hc_orcl.dat initdw.ora initorcl.ora orapwAUX snapcf_orcl.f
initAUX.ora init.ora lkORCL orapworcl spfileorcl.ora
windows下oracle默认的位置是$ORACLE_HOME/database目录,文件名格式是pwdSID.ora。
linux下oracle默认的位置是$ORACLE_HOME/dbs目录,文件名格式是orapwSID。
创建完后,数据库需要重启动,新的口令文件才能生效。
四.RMAN 备份源库(orcl)
mkdir -p /u01/backup
[oracle@db1 u02]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Jul 18 10:57:53 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1248423599)
RMAN>RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP FORMAT '/u01/backup/orcl_%U_%T' skip inaccessible filesperset 5 DATABASE TAG orcl_hot_db_bk;
sql 'alter system archive log current';
BACKUP FORMAT '/u01/backup/arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL DELETE INPUT;
backup current controlfile tag='bak_ctlfile' format='/u01/backup/ctl_file_%U_%T';
backup spfile tag='spfile' format='/u01/backup/ORCL_spfile_%U_%T';
release channel c2;
release channel c1;
}
五.添加,配置监听
5.1修改listener.ora文件,添加如下内容
知道orcl和AUX 两个实例
静态注册AUX实例
cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
使用图形 netmgr实施对AUX 的静态注册
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME =AUX)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME =AUX)
)
)
lsnrctl reload生效
检查
lsnrctl status
Service "AUX" has 1 instance(s).
Instance "AUX", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
5.2修改tnsnames.ora文件,添加如下内容
AUX=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUX)
)
)
tnsping aux 正确
建议使用net manager 工具从界面来修改,这样不容易出错
六.启动辅助库到nomount状态
[oracle@db1 admin]$ export ORACLE_SID=AUX
[oracle@db1 admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 18 11:17:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=?/dbs/initAUX.ora --注意要指定pfile
ORACLE instance started.
Total System Global Area 247463936 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 7168000 bytes
注意要exit退出会话
七.RMAN 连接到目标实例和辅助实例,运行duplicate命令复制数据库
通过修改tnsnames.ora的参数,红色表注部分
# tnsnames.ora Network Configuration File: C:\oracle\product\10.1.0\db_1
\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
AMMICLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ammicly)
(UR=A)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
然后 sqlplus /nolog
SQL>alter database mount;
SQL>alter database open;
这样就可以启动数据库了;
或者用Oradim -shutdown -sid ammicly -shuttype srvc,inst -shutmode immediate -syspwd ammic;关闭数据库
在用oradim -startup -sid ammicly -starttype srvc,inst -syspwd ammic;启动数据库解决;也可以在截面上从启动数据库服务来解决;
ORA-12528:监听中的服务使用了动态服务,系统启动后,数据库没有正常的MOUNT,因此在动态模式下,就会出现这个问题,用静态的就不会有这个问题,因此上面的方法就是把监听设置为静态,或者在tnsnames.ora中追加(UR=A)或者从启动服务,三种方法解决问题;
rman target sys/oracle@orcl auxiliary sys/oracle@aux
RMAN> duplicate target database to AUX nofilenamecheck;
Starting Duplicate Db at 18-JUL-10
using target database control file instead of recovery catalog --用的是原来的控制文件
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
contents of Memory Script:
{
set until scn 697286;
set newnamefor datafile 1 to "/u01/app/oracle/oradata/AUX/system01.dbf"; --转换文件位置
set newname for datafile 2 to "/u01/app/oracle/oradata/AUX/undotbs01.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/AUX/sysaux01.dbf";
set newname for datafile 4 to "/u01/app/oracle/oradata/AUX/users01.dbf";
set newname for datafile 5 to "/u01/app/oracle/oradata/AUX/example01.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
executing command: SET NEWNAME
Starting restore at 18-JUL-10
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore --开始restore数据文件
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/AUX/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/AUX/sysaux01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/AUX/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_39lj3bmt_1_1_20100718
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/orcl_39lj3bmt_1_1_20100718 tag=ORCL_HOT_DB_BK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:47
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/AUX/system01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/AUX/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/orcl_38lj3bmt_1_1_20100718
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/orcl_38lj3bmt_1_1_20100718 tag=ORCL_HOT_DB_BK
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 18-JUL-10
sql statement:CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
--创建源库的控制文件,然后用这个控制文件进行恢复
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/AUX/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/AUX/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/AUX/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/AUX/system01.dbf'
CHARACTER SET WE8ISO8859P1
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=724679047 filename=/u01/app/oracle/oradata/AUX/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=724679047 filename=/u01/app/oracle/oradata/AUX/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=724679047 filename=/u01/app/oracle/oradata/AUX/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=724679047 filename=/u01/app/oracle/oradata/AUX/example01.dbf
contents of Memory Script:
{
set until scn 697286;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-JUL-10 --开始recover恢复数据
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK
starting media recovery
channel ORA_AUX_DISK_1: starting archive log restore to default destination
--先将归档日志还原到指定的归档目录:log_archive_dest参数指定
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=41
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_3dlj3bro_1_1_20100718
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/arch_3dlj3bro_1_1_20100718 tag=TAG20100718T110111
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=40
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/arch_3clj3bro_1_1_20100718
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/backup/arch_3clj3bro_1_1_20100718 tag=TAG20100718T110111
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/archivelog/1_40_720642866.dbf thread=1 sequence=40
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/1_40_720642866.dbf recid=2 stamp=724679053
archive log filename=/u01/archivelog/1_41_720642866.dbf thread=1 sequence=41
channel clone_default: deleting archive log(s)
archive log filename=/u01/archivelog/1_41_720642866.dbf recid=1 stamp=724679052
media recovery complete, elapsed time: 00:00:04
Finished recover at 18-JUL-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
--这里要注意的一个地方,在这一步的时候,辅助实例不能有任何session打开,即不能有有任何连接连接到AUX上,不然它会一直那个session退出后才能执行
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 247463936 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 7168000 bytes
sql statement:CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
--复制已经完成,创建新的控制文件(AUX的控制文件)
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/AUX/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/AUX/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/AUX/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/AUX/system01.dbf'
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to "/u01/app/oracle/oradata/AUX/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/undotbs01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/sysaux01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/users01.dbf";
catalog clone datafilecopy "/u01/app/oracle/oradata/AUX/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/AUX/temp01.dbf in control file
cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/AUX/undotbs01.dbf recid=1 stamp=724679599
cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/AUX/sysaux01.dbf recid=2 stamp=724679599
cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/AUX/users01.dbf recid=3 stamp=724679599
cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/AUX/example01.dbf recid=4 stamp=724679599
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=724679599 filename=/u01/app/oracle/oradata/AUX/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=724679599 filename=/u01/app/oracle/oradata/AUX/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=724679599 filename=/u01/app/oracle/oradata/AUX/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=724679599 filename=/u01/app/oracle/oradata/AUX/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 18-JUL-10
八.修改辅助库的pfile,在创建spfile
8.1删除下面2句:
db_file_name_convert = ('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/AUX')
log_file_name_convert = ('/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/AUX')
8.2创建spfile
[oracle@db1 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 18 12:11:28 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys/admin@AUX as sysdba;
Connected.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initAUX.ora';
File created.
- RMAN 同机复制数据库
- RMAN 同机复制数据库
- RMAN 同机复制数据库
- RMAN 同机复制数据库
- rman 同机复制数据库
- RMAN 同机复制数据库
- rman 同机复制数据库---侦听错误处理
- 迁移数据库——rman Duplicate 同机复制数据库
- 基于 RMAN 的同机数据库克隆
- 基于rman的同机数据库克隆
- MySQL 同机异步复制
- 使用rman复制数据库
- RMAN 异机 复制数据库
- RMAN 异机 复制数据库
- RMAN Duplicate复制数据库
- RMAN复制数据库
- RMAN 异机 复制数据库
- RMAN 异机 复制数据库
- JS复选框操作
- 15项最佳电子产品影响人类未来
- Parallel.ForEach平行线程中的实体的值在输出或打印时二次赋值是可能会串
- java.lang.Instrumentation作用,虚拟机监控
- JQuery Mobile实现动态下拉框代码
- rman 同机复制数据库
- python的 Digango框架的安装
- FFplay 整体流程图
- mysql dump binlog master状态
- rman 异机复制数据库
- 开关电源基本知识
- Android repo 切换分支
- tomcat 虚拟路径配置的几种方法
- 最短路