oracle数据库克隆总结

oracle数据库克隆总结

1． 本例中原数据库名为TEST备份后数据库名为TESTBAK。

2． 察看物理存储，内容包括：数据文件、连机日志文件、控制文件的存储位置及其名称信息

a) COLUMN NAME FORMAT A40;

b) SELECT FILE#,NAME,STATUS FROM V$DATAFILE;

c) SELECT FILE#,NAME,STATUS FROM V$TEMPFILE;

d) COLUMN MEMBER FORMAT A40;

e) SELECT GROUP#,MEMBER,STATUS FROM V$LOGFILE;

f) SELECT NAME,STATUS FROM V$CONTROLFILE;

3． 查询日志运行方式。如要备份数据库不能停机，数据库必须处于ARCHIVELOG归档模式

a) ARCHIVE LOG LIST;

4． 记录样本数据库初始化参数的详细设置

a) CREATE PFILE=’存放路径/inittest.ora’ FROM SPFILE;

5． 生成样本控制文件的创建脚本，此时脚本记录在最后一个.trc文件中

a) Alter database backup controlfile to trace;

b) Show parameter user_dump_dest;

6． 关闭状态下的存储转移

a) 可在操作系统下将上面查找到的文件备份到新位置。或者是用sql脚本《物理备份数据库文件.sql》其中的CLONE_DIR和CP_SCRIPT要根据实际情况具体修改，运行前应确保目标目录已存在并具有访问权限。（在linux系统下这里最好使用oracle用户进行备份否则还需要重新修改文件的权限）

7． 打开状态下的存储转移,在这种情况下重新运行克隆数据库的过程中需要数据库恢复操作（RECOVER DATABASE UNTILE CANCEL）

a) ARCHIVE LOG LIST;

b) SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;

c) ALTER TABLESPACE ‘tablespace_name’ BEGIN BACKUP;

d) HOST 操作系统命令拷贝表空间文件

e) ALTER TABLESPACE ‘tablespace_name’ END BACKUP;

f) ALTER SYSTEM SWITCH LOGFILE;

8． 修改初始化“init文件名.ORA”文件中的内容：

a) DB_DOMAIN=

b) DB_NAME=

c) CONTROL_FILES=(‘’)

d) BACKGROUND_DUMP_DEST=

e) USER_DUMP_DEST=

f) CORE_DUMP_DEST=

g) LOG_ARCHIVE_DEST=

9． 在linux系统下需要使用oracle用户操作，将ORACLE_SID=\" \"的值改为新克隆的数据的名称。使用操作系统认证的方式连接数据库

a) Sqlplus ‘/as sysdba’

10． 在window系统下需要先建立实例，再以dba身份连接实例

a) ORADIM –NEW –SID TESTDB –INTPWD 12345 –STARTMODE MANUAL

b) Set Oracle_Sid = (填写数据库服务ID)

c) SQLPLUS /NOLOG

d) CONNECT SYS/123456 AS SYSDBA;

11． 从保存的初始化参数文件创建新spfile初始化参数，并启动到NOMOUNT转状态

a) CREATE SPFILE FROM PFILE=’ 存放路径/inittest.ora’;

b) STARTUP NOMOUNT;

c) 如果上一步提示“bad value '/home/oracle/admin/…/udump' for parameter user_dump_dest.”说明此目录不存在需要手工创建或到admin目录下将原数据库中的目录复制一份

12． 创建新的控制文件，修改该.trc文件中控制文件创建脚本部分内容，并在sqlplus中运行脚本

a) 删除元命令中的REUSE，在其位置添加SET

b) 将原数据库名更改为新数据库名

c) 将关键字NORESETLOGS更改为RESETLOGS，更改数据库名后此项必须设置

d) 修改数据为鉴和日志文件字句中的存储位置包括存储路径和文件名

e) 创建时可能会出现“控制文件已经存在的错误提示”，删除控制文件即可

f) 如果出现“…./orapw”这个错误提示，可以使用orapw命令重新创建orapw文件即可，具体使用方法使用orapw help察看

13． 如果创建控制文件成功此时，数据库应处于mount状态，进行数据库恢复

a) RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

b) 期间可能会出现提示需要用户提供重做日志文件的位置，需要当前日志文件，这是可多进行几次尝试。

14． 打开数据库，并重置日志

a) ALTER DATABASE OPEN RESETLOGS;

15． 此时临时数据文件仍处于脱机状态需要将他连机

a) Select name,status from v$tempfile;

b) Select tablespace_name,status,contents from dba_tablespaces;

c) Alter tablespace “TEMP” add tempfile’../temp01.ora’ size 10M reuse;

16． 至此数据库克隆完成。下面进行网络配置。 17． 通过netmgr命令调出网络配置管理器

a) Local->listeners->LISTENER->Database Services中填加新克隆的数据库

b) 此时如果lsnrctl如果正在运行需要将他重起，否则下面测试的时候可能找不到相应的数据库

c) Local->Service Naming中填加新克隆数据库，这里注意主机名最好添地址，测试的时候可能需要更改测试的用户名和口令，这个是原数据库的口令

d) 保存配置并退出

18． 至此克隆全部完成 【test_ora_5684.trc】

/home/oracle/admin/test/udump/test_ora_5684.trc

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 - Production

ORACLE_HOME = /home/oracle/OraHomel

System name: Linux

Node name: test

Release: 2.4.21-20.EL

Version: #1 Wed Aug 18 20:58:25 EDT 2004

Machine: i686

Instance name: test

Redo thread mounted by this instance: 1

Oracle process number: 10

Unix process pid: 5684, image: oracle@test (TNS V1-V3)

*** SESSION ID:(7.3) 2007-07-30 21:08:35.2

*** 2007-07-30 21:08:35.2

# The following are current System-scope REDO Log Archival related

# parameters and can be included in the database initialization file.

#

# LOG_ARCHIVE_DEST=''

# LOG_ARCHIVE_DUPLEX_DEST=''

#

# LOG_ARCHIVE_FORMAT=%t_%s.dbf

# REMOTE_ARCHIVE_ENABLE=TRUE

# LOG_ARCHIVE_MAX_PROCESSES=2

# STANDBY_FILE_MANAGEMENT=MANUAL

# STANDBY_ARCHIVE_DEST=?/dbs/arch

# FAL_CLIENT=''

# FAL_SERVER=''

#

# LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/OraHomel/dbs/arch'

# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'

# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'

# LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'

# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'

# LOG_ARCHIVE_DEST_STATE_1=ENABLE

#

# Below are two sets of SQL statements, each of which creates a new

# control file and uses it to open the database. The first set opens

# the database with the NORESETLOGS option and should be used only if

# the current versions of all online logs are available. The second

# set opens the database with the RESETLOGS option and should be used

# if online logs are unavailable.

# The appropriate set of statements can be copied from the trace into

# a script file, edited as necessary, and executed when there is a

# need to re-create the control file.

#

# Set #1. NORESETLOGS case

#

# The following commands will create a new control file and use it

# to open the database.

# Data used by the recovery manager will be lost. Additional logs may

# be required for media recovery of offline data files. Use this

# only if the current version of all online logs are available.

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE \"TESTBAK\" RESETLOGS NOARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 '/home/oracle/oradata/testbak/redo01.log' GROUP 2 '/home/oracle/oradata/testbak/redo02.log' SIZE 100M,

SIZE 100M,

GROUP 3 '/home/oracle/oradata/testbak/redo03.log' SIZE 100M

-- STANDBY LOGFILE

DATAFILE

'/home/oracle/oradata/testbak/system01.dbf',

'/home/oracle/oradata/testbak/undotbs01.dbf',

'/home/oracle/oradata/testbak/drsys01.dbf',

'/home/oracle/oradata/testbak/indx01.dbf',

'/home/oracle/oradata/testbak/tools01.dbf',

'/home/oracle/oradata/testbak/users01.dbf',

'/home/oracle/oradata/testbak/xdb01.dbf',

'/home/oracle/oradata/testbak/TEST.dbf'

CHARACTER SET ZHS16GBK

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN;

# Commands to add tempfiles to temporary tablespaces.

# Online tempfiles have complete space information.

# Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/test/temp01.dbf'

SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

# End of tempfile additions.

#

# Set #2. RESETLOGS case

#

# The following commands will create a new control file and use it

# to open the database.

# The contents of online logs will be lost and all backups will

# be invalidated. Use this only if online logs are damaged.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE \"TEST\" RESETLOGS NOARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 '/home/oracle/oradata/test/redo01.log' SIZE 100M,

GROUP 2 '/home/oracle/oradata/test/redo02.log' SIZE 100M,

GROUP 3 '/home/oracle/oradata/test/redo03.log' -- STANDBY LOGFILE

DATAFILE

'/home/oracle/oradata/test/system01.dbf',

'/home/oracle/oradata/test/undotbs01.dbf',

'/home/oracle/oradata/test/drsys01.dbf',

'/home/oracle/oradata/test/indx01.dbf',

'/home/oracle/oradata/test/tools01.dbf',

'/home/oracle/oradata/test/users01.dbf',

'/home/oracle/oradata/test/xdb01.dbf',

SIZE 100M

'/home/oracle/oradata/test/TEST.dbf'

CHARACTER SET ZHS16GBK

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

# Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

# Commands to add tempfiles to temporary tablespaces.

# Online tempfiles have complete space information.

# Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/test/temp01.dbf'

SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

# End of tempfile additions.

#

【物理备份数据库文件.sql】

SET FEEDBACK OFF HEADING OFF VERIFY OFF TRIMSPOOL OFF

SET PAGESIZE 0 LINESIZE 200

DEFINE CLONE_DIR='/home/oracle/oradata/testbak'

DEFINE CP_SCRIPT='/home/oracle/oradata/CP_TEST.SQL'

SPOOL &CP_SCRIPT

SELECT 'HOST cp '||NAME||' &CLONE_DIR' FROM V$DATAFILE ORDER BY NAME;

SELECT 'HOST cp '||NAME||' &CLONE_DIR' FROM V$TEMPFILE ORDER BY NAME;

SELECT 'HOST cp '||MEMBER||' &CLONE_DIR' FROM V$LOGFILE ORDER BY MEMBER;

SELECT 'HOST cp '||NAME||' &CLONE_DIR' FROM V$CONTROLFILE;

SPOOL OFF;

SHUTDOWN IMMEDIATE;

@&CP_SCRIPT

STARTUP

【inittest.ora】

*.background_dump_dest='/home/oracle/admin/testbak/bdump'

*.compatible='9.2.0.0.0'

*.control_files='/home/oracle/oradata/testbak/control01.ctl','/home/oracle/oradata/testbak/control02.ctl','/home/oracle/oradata/testbak/control03.ctl'

*.core_dump_dest='/home/oracle/admin/testbak/cdump'

*.db_block_size=8192

*.db_cache_size=25165824

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='testbak'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testbakXDB)'

*.fast_start_mttr_target=300

*.hash_join_enabled=TRUE

*.instance_name='testbak'

*.java_pool_size=83886080

*.large_pool_size=8388608

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=83886080

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/oracle/admin/testbak/udump'