数据库克隆实验系列

1、成人用品店www.xfqiao.com同名克隆-131服务器上新建库PROD1，然后将其迁移至137服务器，库名仍为PROD1
1.1 dbca-131 PROD1
sys/oracle
system/oracle
uncheck all components
no EM
no Flashback
no Archiving
ORA-00845 when using dbca to create a database
Starting with Oracle Database 11g, the Automatic Memory Management feature requires more shared memory (/dev/shm)and file descriptors. The size of the shared memory should be at least the greater of MEMORY_MAX_TARGET and MEMORY_TARGET for each Oracle instance on the computer. If MEMORY_MAX_TARGET or MEMORY_TARGET is set to a non zero value, and an incorrect size is assigned to the shared memory, it will result in an ORA-00845 error at startup.


[oracle@oelr5u8-1 admin]$ df -h | grep shm
tmpfs 2.0G 400M 1.6G 20% /dev/shm


for it's only 400M and less than the parameter memory_target=1.6G
so let's modify it to 2G
[root@oelr5u8-1 ~]# vi /etc/fstab
modify:
tmpfs /dev/shm tmpfs defaults 0 0
to:
tmpfs /dev/shm tmpfs defaults,size=2G 0 0
init 6


then do the same on 192.168.182.137


export ORACLE_SID=PROD1
sqlplus / as sysdba


SYS@PROD1>select dbid from v$database;


DBID
----------
2065008095




SYS@PROD1>create pfile from spfile;


File created.


SYS@PROD1>alter database backup controlfile to trace;


Database altered.


SYS@PROD1>show parameter user_dump


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/diag/rdbms/pro
d1/PROD1/trace

cd /u01/app/oracle/diag/rdbms/prod1/PROD1/trace
[oracle@oelr5u8-1 trace]$ cp PROD1_ora_6245.trc /home/oracle/control01.txt
cd
vi control01.txt
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/PROD1/system01.dbf',
'/u01/app/oracle/oradata/PROD1/sysaux01.dbf',
'/u01/app/oracle/oradata/PROD1/undotbs01.dbf',
'/u01/app/oracle/oradata/PROD1/users01.dbf'
CHARACTER SET AL32UTF8
;


[oracle@oelr5u8-1 ~]$ scp control01.txt oel6.4-1:~


1.2 cold backup tar-131 PROD1


SYS@PROD1>select name from v$dbfile;


NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/system01.dbf
/u01/app/oracle/oradata/PROD1/sysaux01.dbf
/u01/app/oracle/oradata/PROD1/undotbs01.dbf
/u01/app/oracle/oradata/PROD1/users01.dbf




SYS@PROD1>select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo01.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo03.log


SYS@PROD1>select name from v$controlfile;


NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/oradata/PROD1/control02.ctl


SYS@PROD1>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


cd $ORACLE_BASE/oradata
tar -zcvf PROD1.tar ./PROD1
scp PROD1.tar oel6.4-1:$ORACLE_BASE/oradata


cd $ORACLE_HOME/dbs
scp initPROD1.ora oel6.4-1:/s01/app/oracle/product/11.2.0/db_1/dbs
scp orapwPROD1 oel6.4-1:/s01/app/oracle/product/11.2.0/db_1/dbs


1.3 scp cold backup tar including datafiles, control files, pfile, orapwdfile from 131->137


1.4 unzip tar from 131->137
cd $ORACLE_BASE/oradata
tar -zxvf PROD1.tar


1.5 check path and SID
vi initPROD1.ora
modify "/u01" to "/s01"
PROD1.__db_cache_size=687865856
PROD1.__java_pool_size=16777216
PROD1.__large_pool_size=16777216
PROD1.__oracle_base='/s01/app/oracle'#ORACLE_BASE set from environment
PROD1.__pga_aggregate_target=687865856
PROD1.__sga_target=1006632960
PROD1.__shared_io_pool_size=0
PROD1.__shared_pool_size=268435456
PROD1.__streams_pool_size=0
*.audit_file_dest='/s01/app/oracle/admin/PROD1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/s01/app/oracle/oradata/PROD1/control01.ctl','/s01/app/oracle/oradata/PROD1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD1'
*.diagnostic_dest='/s01/app/oracle'
*.memory_target=1686110208
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


according to :*.audit_file_dest='/s01/app/oracle/admin/PROD1/adump'
[oracle@oel6 oradata]$ mkdir -p /s01/app/oracle/admin/PROD1/adump


vi control01.txt
modify "/u01" to "/s01"
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/s01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/s01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/s01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/s01/app/oracle/oradata/PROD1/system01.dbf',
'/s01/app/oracle/oradata/PROD1/sysaux01.dbf',
'/s01/app/oracle/oradata/PROD1/undotbs01.dbf',
'/s01/app/oracle/oradata/PROD1/users01.dbf'
CHARACTER SET AL32UTF8
;


cd /s01/app/oracle/oradata/PROD1
rm control*


1.6 create spfile from pfile-137 PROD1
[oracle@oel6 PROD1]$ export ORACLE_SID=PROD1
[oracle@oel6 PROD1]$ sqlplus /nolog


SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 31 16:15:13 2014


Copyright (c) 1982, 2009, Oracle. All rights reserved.


@>connect sys/oracle as sysdba
Connected to an idle instance.
SYS@PROD1>create spfile from pfile;


File created.


SYS@PROD1>startup nomount
ORACLE instance started.


Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 989857704 bytes
Database Buffers 687865856 bytes
Redo Buffers 6987776 bytes
SYS@PROD1>CREATE CONTROLFILE REUSE DATABASE "PROD1" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/s01/app/oracle/oradata/PROD1/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/s01/app/oracle/oradata/PROD1/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/s01/app/oracle/oradata/PROD1/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/s01/app/oracle/oradata/PROD1/system01.dbf',
14 '/s01/app/oracle/oradata/PROD1/sysaux01.dbf',
15 '/s01/app/oracle/oradata/PROD1/undotbs01.dbf',
16 '/s01/app/oracle/oradata/PROD1/users01.dbf'
17 CHARACTER SET AL32UTF8
18 ;


Control file created.


SYS@PROD1>shutdown immediate
ORA-01109: database not open




Database dismounted.
ORACLE instance shut down.
SYS@PROD1>startup mount
ORACLE instance started.


Total System Global Area 1686925312 bytes
Fixed Size 2213976 bytes
Variable Size 989857704 bytes
Database Buffers 687865856 bytes
Redo Buffers 6987776 bytes
Database mounted.
SYS@PROD1>alter database open resetlogs;


Database altered.


SYS@PROD1>select dbid from v$database;


DBID
----------
2065008095
与源数据库相同的DBID