-->演示环境
SQL> ho cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> select name,log_mode,open_mode from v$database;
NAME LOG_MODE OPEN_MODE
--------------------------- ------------ --------------------
SYBO3 ARCHIVELOG READ WRITE
--原数据库名 : sybo3
--目标数据库名: sybo4
--原数据库目录:/u01/database/sybo3
--目标数据库目录:/u01/database/sybo4
- 创建目标数据库目录
$ more sybo4.sh
#!/bin/sh
mkdir -p /u01/database
mkdir -p /u01/database/sybo4/adump
mkdir -p /u01/database/sybo4/controlf
mkdir -p /u01/database/sybo4/flash_recovery_area
mkdir -p /u01/database/sybo4/oradata
mkdir -p /u01/database/sybo4/redo
mkdir -p /u01/database/sybo4/dpdump
mkdir -p /u01/database/sybo4/pfile
mkdir -p /u01/database/sybo4/db_broker
$ ./sybo4.sh
- 创建目标数据库密码文件
$ orapwd file=$ORACLE_HOME/dbs/orapwsybo4 password=oracle entries=10
- 创建目标数据库参数文件
--从原数据库生成目标数据库的初始化参数文件
SQL> create pfile='/u01/oracle/db_1/dbs/initsybo4.ora' from spfile;
--修改目标数据库参数文件
$ sed -i 's/sybo3/sybo4/g' $ORACLE_HOME/dbs/initsybo4.ora
$ grep sybo3 $ORACLE_HOME/dbs/initsybo4.ora -->校验是否还存在sybo3相关字符
--最终的目标数据库参数文件
$ more $ORACLE_HOME/dbs/initsybo4.ora
sybo4.__db_cache_size=117440512
sybo4.__java_pool_size=4194304
sybo4.__large_pool_size=4194304
sybo4.__oracle_base='/u01/oracle'#ORACLE_BASE set from environment
sybo4.__pga_aggregate_target=150994944
sybo4.__sga_target=226492416
sybo4.__shared_io_pool_size=0
sybo4.__shared_pool_size=92274688
sybo4.__streams_pool_size=0
*.audit_file_dest='/u01/database/sybo4/adump/'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/database/sybo4/controlf/control01.ctl','/u01/database/sybo4/controlf/control02.ctl'
*.db_block_size=8192
*.db_domain='orasrv.com'
*.db_name='sybo4'
*.db_recovery_file_dest='/u01/database/sybo4/flash_recovery_area/'
*.db_recovery_file_dest_size=4039114752
*.dg_broker_config_file1='/u01/database/sybo4/db_broker/dr1sybo4.dat'
*.dg_broker_config_file2='/u01/database/sybo4/db_broker/dr2sybo4.dat'
*.dg_broker_start=FALSE
*.diagnostic_dest='/u01/database/sybo4'
*.log_archive_dest_1=''
*.memory_target=374341632
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
- 备份原数据库并复制备份文件到目标数据库
--创建一个临时表t用户验证克隆是否成功
SQL> create table t(name varchar2(10),action varchar2(20));
SQL> insert into t select 'Robinson','Transfer DB' from dual;
SQL> commit;
SQL> alter system archive log current;
--准备目标数据库创建控制文件脚本,此trace file位于参数user_dump_dest目录下
SQL> alter database backup controlfile to trace resetlogs;
--备份原数据库,如果数据库文件较多,使用热备脚本来完成
SQL> alter database begin backup;
--复制数据库文件到目标数据库目录
SQL> host cp /u01/database/sybo3/oradata/* /u01/database/sybo4/oradata
SQL> alter database end backup;
- 启动目标数据库到nomount状态并创建控制文件
$ export ORACLE_SID=sybo4
$ sqlplus / as sysdba
SQL> startup nomount pfile=/u01/oracle/db_1/dbs/initsybo4.ora;
SQL> get sybo4ctl.sql
1 CREATE CONTROLFILE SET DATABASE "sybo4" RESETLOGS ARCHIVELOG
注意这里是set database,而使用原库backup to trace的文件中是reuse database, 使用reuse database sybo4创建控制文件会报错:
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ORCL in file header does not match given name of CLONE
ORA-01110: data file 1: '/u01/app/clone/oradata/clone/system01.dbf'
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/database/sybo4/redo/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/database/sybo4/redo/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/database/sybo4/redo/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/u01/database/sybo4/oradata/system01.dbf',
13 '/u01/database/sybo4/oradata/sysaux01.dbf',
14 '/u01/database/sybo4/oradata/undotbs01.dbf',
15 '/u01/database/sybo4/oradata/users01.dbf',
16 '/u01/database/sybo4/oradata/example01.dbf'
17 CHARACTER SET AL32UTF8
18* ;
SQL> @sybo4ctl.sql
Control file created.
SQL> alter database mount; -->注意创建控制文件之后,数据库已经被mount
ORA-01100: database already mounted
--上面我们修改了控制文件脚本,使用了set database以及resetlogs方式来创建数据库
- 恢复目标数据库
SQL> set logsource '/u01/database/sybo3/flash_recovery_area/SYBO3/archivelog/2013_07_24';由于归档日志位于原数据库归档位置,因此在恢复期间使用了set logsource子句用于指定归档日志所在的位置
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 847086 generated at 07/24/2013 14:42:06 needed for thread 1
ORA-00289: suggestion :
/u01/database/sybo3/flash_recovery_area/SYBO3/archivelog/2013_07_24/o1_mf_1_7_8216
17241.dbf
ORA-00280: change 847086 for thread 1 is in sequence #7
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/database/sybo3/redo/redo01.log
Log applied.
Media recovery complete.
- 打开目标数据库
SQL> alter database open resetlogs;
创建控制文件时,由于是一个新的db,因此必须使用resetlog方式,否则收到ORA-01223: RESETLOGS must be specified to set a new database name
- 校验数据库及添加临时数据文件
SQL> select * from t;
NAME ACTION
---------- --------------------
Robinson Transfer DB
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
/u01/database/sybo4/oradata/system01.dbf
/u01/database/sybo4/oradata/sysaux01.dbf
/u01/database/sybo4/oradata/undotbs01.dbf
/u01/database/sybo4/oradata/users01.dbf
/u01/database/sybo4/oradata/example01.dbf
SQL> col member format a60
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------------
/u01/database/sybo4/redo/redo03.log
/u01/database/sybo4/redo/redo02.log
/u01/database/sybo4/redo/redo01.log
SQL> select name from v$controlfile;
NAME
------------------------------------------------------------
/u01/database/sybo4/controlf/control01.ctl
/u01/database/sybo4/controlf/control02.ctl
SQL> select * from v$tempfile; ---因为新建了控制文件,内无临时表空间信息,需要重建临时文件
no rows selected
SQL> select property_name,property_value from database_properties where property_name like '%DEFAULT%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ---------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
DEFAULT_TBS_TYPE SMALLFILE
SQL> select tablespace_name from dba_tablespaces where tablespace_name='TEMP';
TABLESPACE_NAME
------------------------------
TEMP
SQL> alter tablespace temp add tempfile '/u01/database/sybo4/oradata/tempfile.dbf' size 50m autoextend on;
--建立服务器参数文件,之后建议一致性关闭数据库,备份数据库,添加数据库到/etc/oratab,配置监听器等
SQL> create spfile from pfile;
这里未使用nid来修改db_name及dbid,如果需要再修改