oracle克隆一个用户,基于用户管理的同机数据库克隆

[sql]

-->演示环境

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

--a、创建目标数据库目录

[oracle@linux3 database]$ 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

[oracle@linux3 database]$ ./sybo4.sh

--b、创建目标数据库密码文件

$ orapwd file=$ORACLE_HOME/dbs/orapwsybo4 password=oracle entries=10

--c、创建目标数据库参数文件

--从原数据库生成目标数据库的初始化参数文件

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'

--d、备份原数据库并复制备份文件到目标数据库

--创建一个临时表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;

--e、启动目标数据库到nomount状态并创建控制文件

$ export ORACLE_SID=sybo4

$ sqlplus / as sysdba

SQL> startup nomount pfile=/u01/oracle/db_1/dbs/initsybo4.ora;

ORACLE instance started.

SQL> get sybo4ctl.sql

1 CREATE CONTROLFILE SET DATABASE "sybo4" RESETLOGS ARCHIVELOG

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，如下我们收到了错误提示

alter database mount

*

ERROR at line 1:

ORA-01100: database already mounted

--上面我们修改了控制文件脚本，使用了set database以及resetlogs方式来创建数据库

--f、恢复目标数据库

SQL> set logsource '/u01/database/sybo3/flash_recovery_area/SYBO3/archivelog/2013_07_24';

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: {=suggested | filename | AUTO | CANCEL}

/u01/database/sybo3/redo/redo01.log

Log applied.

Media recovery complete.

--g、打开目标数据库

SQL> alter database open resetlogs;

Database altered.

--h、校验数据库及添加临时数据文件

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

--Author : Robinson

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;