如何建立與現有資料庫相同的空資料庫

       前段時間被問到怎麼在現有資料庫基礎上,建立一個相同配置的空庫;用標題在網上搜尋也沒有答案,今天無意之間看到網上dbca建立模板的方法,試著建立之後，竟然是想要的效果。

      方法是先建立現有資料庫的模板，然後透過該模板建立新資料庫。語句如下：

dbca -silent -createTemplateFromDB -sourceDB rac04:1521:rac04 -sysDBAUserName sys -sysDBAPassword oracle -templateName racdb_template

 

dbca -silent -createDatabase -templateName racdb_template.dbt -gdbname tempdb -sid tempdb1 -datafileDestination /app/oracle/oradata2 -responseFile NO_VALUE -characterset ZHS16GBK

 

測試的環境是，源資料庫為10.2.0.4，新建立的資料庫為11.2.0.3；

步驟一：源資料庫10.2.0.4上建立模板

[oracle@rac04 templates]$ dbca -silent -createTemplateFromDB -sourceDB rac04:1521:rac04 -sysDBAUserName sys -sysDBAPassword oracle -templateName racdb_template

Creating a template from the database

10% complete

20% complete

30% complete

40% complete

50% complete

60% complete

70% complete

80% complete

90% complete

100% complete

Look at the log file "/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/silent10.log" for further details.

 

步驟二：將新建立的模板，複製到目標伺服器db1(192.168.1.20)上

[oracle@rac04 ~]$cd /app/oracle/product/10.2.0/db_1/assistants/dbca/templates/

[oracle@rac04 templates]$ scp racdb_template.dbt 192.168.1.20:/app/oracle/

oracle@192.168.1.20's password:

racdb_template.dbt                                   100%   20KB  20.2KB/s   00:00   

 

步驟三：設定環境變數

db1伺服器上是已經安裝了11.2.0.3 rac的資料庫節點；需要為新建立的資料庫指定環境變數；如果沒有安裝oracle software則需要配置.bash_profile，然後安裝軟體；

 

[oracle@db1 oracle]$ cat ~/.bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

 

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=racdb2

export OMS_HOME=$ORACLE_BASE/oms10g

export AGENT_HOME=$ORACLE_BASE/agent10g

export ORACLE_HOSTNAME=db1

 

export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:${PATH}:$HOME/bin

export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin:$ORACLE_HOME/jdk/bin

export ORACLE_TERM=xterm

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib

export CLASSPATH=$ORACLE_HOME/jdk/jre

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib

export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib

export THREADS_FLAG=native

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export TEMP=/tmp

export TMPDIR=/tmp

export SQLPATH=/u01/app/oracle/login

umask 022

 

alias sqlplus='rlwrap sqlplus'

alias rman='rlwrap rman'

[oracle@db1 oracle]$

[oracle@db1 oracle]$ export ORACLE_BASE=/app/oracle

[oracle@db1 oracle]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@db1 oracle]$ export ORACLE_SID=tempdb1

 

步驟四：建立新資料庫；

[oracle@db1 oracle]$ dbca -silent -createDatabase -templateName /app/oracle/racdb_template.dbt -gdbname tempdb -sid tempdb1 -datafileDestination /app/oracle/oradata -responseFile NO_VALUE -characterset ZHS16GBK

Enter SYS user password:

 

Enter SYSTEM user password:

 

Creating and starting Oracle instance

2% complete

3% complete

9% complete

Creating database files

10% complete

11% complete

12% complete

13% complete

14% complete

15% complete

19% complete

Creating data dictionary views

21% complete

24% complete

27% complete

28% complete

29% complete

30% complete

31% complete

32% complete

33% complete

34% complete

35% complete

36% complete

42% complete

45% complete

47% complete

48% complete

50% complete

Adding Oracle Text

51% complete

52% complete

57% complete

60% complete

Adding Enterprise Manager Repository

65% complete

69% complete

Registering database with Oracle Restart

74% complete

Completing Database Creation

77% complete

80% complete

82% complete

91% complete

100% complete

Look at the log file "/app/oracle/cfgtoollogs/dbca/tempdb/tempdb.log" for further details.

成功完成