利用冷备份创建一个相同DBID的Oracle数据库

来源：互联网发布：循环冗余检查算法编辑：程序博客网时间：2024/04/30 17:37

主数据库M

测试数据库T

操作系统

RHEL 5 update 8 X86_64

主机名

oraedu

IP地址/子网掩码

192.168.127.11/24

192.168.127.12/24

/etc/hosts

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.127.11 oraedu

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

192.168.127.12 oraedu

~/.bash_porfile

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

EDITOR=vi

export PATH EDITOR

ORACLE_BASE=/u01/app/oracle

ORACLE_SID=orcl

export ORACLE_BASE ORACLE_SID

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

EDITOR=vi

export PATH EDITOR

ORACLE_BASE=/u01/app/oracle

ORACLE_SID=orcl

export ORACLE_BASE ORACLE_SID

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

安装oracle软件的用户

oracle

Oracle软件版本

oracle 10g 10.2.0.1.0 X86_64

$ORACLE_BASE

/u01/app/oracle/

$ORACLE_HOME

/u01/app/oracle/product/10.2.0/db_1/

$ORACLE_SID

orcl

表格1

当Oracle数据库关闭时，我们可以复制数据文件、参数文件、控制文件、日志文件到另外一台主机相同中，用来创建与关闭状态时一模一样的数据库用于测试目的。

测试数据库已安装数据库软件，但不安装数据库，oracle软件的安装位置与主数据库相同，两台数据库的基本设置如表格1。以下是实验步骤：

步骤一、关闭主数据库M

[oracle@oraedu ~]$ lsnrctlstatus ---查看侦听状态

LSNRCTLfor Linux: Version 10.2.0.1.0 - Production on 28-OCT-2012 14:36:59

Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraedu)(PORT=1521)))

STATUSof the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version10.2.0.1.0 - Production

StartDate 28-OCT-2012 14:34:45

Uptime 0 days 0 hr. 2 min. 14 sec

TraceLevel off

Security ON: Local OS Authentication

SNMP OFF

ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

ListenerLog File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

ListeningEndpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraedu)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

ServicesSummary...

Service"PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...

Service"orcl" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Service"orclXDB" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Service"orcl_XPT" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Thecommand completed successfully

--------------------------------------------

[oracle@oraedu ~]$ lsnrctlstop ---关闭侦听

LSNRCTLfor Linux: Version 10.2.0.1.0 - Production on 28-OCT-2012 14:37:46

Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraedu)(PORT=1521)))

Thecommand completed successfully

-------------------------------------------------

[oracle@oraedu~]$ sqlplus / as sysdba

SQL*Plus:Release 10.2.0.1.0 - Production on Sun Oct 28 14:38:12 2012

Connectedto:

OracleDatabase 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

Withthe Partitioning, OLAP and Data Mining options

SYS@orcl>select statusfrom v$instance; ---查看数据库状态

STATUS

------------

OPEN

SYS@orcl>select dbid,name,log_modefrom v$database; ---查看数据库的DBID，是否启用归档

DBID NAME LOG_MODE

------------------------------ ------------ ------------

1320402727 ORCL ARCHIVELOG

SQL> select ename,salfrom scott.emp; ---查看scott的emp表数据

ENAME SAL

--------------------

SMITH 800

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

SCOTT 6000

KING 5000

TURNER 1500

ADAMS 1100

ENAME SAL

--------------------

JAMES 950

FORD 3000

MILLER 1300

14rows selected.

SYS@orcl>selectFILE_NAME,TABLESPACE_NAME from dba_data_files ---查看数据文件位置

FILE_NAME TABLESPACE_NAME

----------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/users01.dbf USERS

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE

/u01/app/oracle/oradata/orcl/statsinfo01.dbf STATSINFO

/u01/app/oracle/oradata/orcl/toms01.dbf TOMS

/u01/app/oracle/oradata/orcl/tomss01.dbf TOMSS

8rows selected.

SYS@orcl>selectSTATUS,name from v$controlfile ---查看控制文件位置

STATUS NAME

---------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control03.ctl

/u01/app/oracle/oradata/orcl/control04.ctl

SYS@orcl>selectGROUP#,STATUS,TYPE,MEMBER from v$logfile order by group#; ---查看online redo log文件位置

GROUP# STATUS TYPE MEMBER

-------------------- ------- --------------------------------------------------

1 ONLINE /u01/app/oracle/oradata/orcl/redo101.log

1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log

2 ONLINE /u01/app/oracle/oradata/orcl/redo202.log

2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log

3 ONLINE /u01/app/oracle/oradata/orcl/redo303.log

3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log

4 ONLINE /u01/app/oracle/oradata/orcl/redo04.log

4 ONLINE /u01/app/oracle/oradata/orcl/redo404.log

8rows selected.

SYS@orcl>show parameterdump_dest; ---查看dump文件位置

NAME TYPE VALUE

----------------------------------------------- ------------------------------

background_dump_dest string /u01/app/oracle/admin/orcl/bdump

core_dump_dest string /u01/app/oracle/admin/orcl/cdump

user_dump_dest string /u01/app/oracle/admin/orcl/udump

SYS@orcl>show parameterrecovery_file_dest ---查看恢复文件保存位置

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_recovery_file_dest string /u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 8G

SYS@orcl>show parameterlog_archive_dest ---查看归档日志保存位置，值全部为空，说明归档日志保存在db_recovery_file_dest

NAME TYPE VALUE

----------------------------------------------- ------------------------------

log_archive_dest string

log_archive_dest_1 string

log_archive_dest_10 string

log_archive_dest_2 string

log_archive_dest_3 string

log_archive_dest_4 string

log_archive_dest_5 string

log_archive_dest_6 string

log_archive_dest_7 string

log_archive_dest_8 string

log_archive_dest_9 string

log_archive_dest_state_1 string enable

log_archive_dest_state_10 string enable

log_archive_dest_state_2 string enable

log_archive_dest_state_3 string enable

log_archive_dest_state_4 string enable

log_archive_dest_state_5 string enable

log_archive_dest_state_6 string enable

log_archive_dest_state_7 string enable

log_archive_dest_state_8 string enable

log_archive_dest_state_9 string enable

SYS@orcl>shutdownimmediate; ---关闭数据库

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SYS@orcl>selectstatus from v$instance;

selectstatus from v$instance

ERRORat line 1:

ORA-01034:ORACLE not available

SYS@orcl>exit --退出sqlplus

Disconnectedfrom Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bitProduction

Withthe Partitioning, OLAP and Data Mining options

[oracle@oraedu ~]$ emctlstop dbconsole ---关闭em

TZset to PRC

OracleEnterprise Manager 10g Database Control Release 10.2.0.1.0

http://oraedu:1158/em/console/aboutApplication

StoppingOracle Enterprise Manager 10g Database Control ...

... Stopped.

步骤二、复制主数据库的文件到测试数据库

[oracle@oraedu ~]$ scp -r$ORACLE_BASE/admin oracle@192.168.127.12:/u01/app/oracle ---复制dump文件

[oracle@oraedu ~]$ scp -r$ORACLE_BASE/oradata/ oracle@192.168.127.12:/u01/app/oracle ---复制数据文件、控制文件、日志文件，如果数据文件保存在不同的目录，那么要把其他目录中的数据文件全部复制过去，本例中数据文件全部在$ORACLE_BASE/oradata/

[oracle@oraedu ~]$ scp -r/u01/app/oracle/flash_recovery_area/ oracle@192.168.127.12:/u01/app/oracle ---复制flash recovery area中的文件，包含归档日志文件

[oracle@oraedu ~]$ scp -r$ORACLE_HOME/dbs oracle@192.168.127.12:/u01/app/oracle/product/10.2.0/db_1 ---复制参数文件

[oracle@oraedu ~]$ scp -r/u01/app/oracle/product/10.2.0/db_1/network/adminoracle@192.168.127.12:/u01/app/oracle/product/10.2.0/db_1 ---复制侦听文件

[oracle@oraedu ~]$scp-r $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_oraedu_orcloracle@192.168.127.12:/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee ---复制启动em所需的文件

[oracle@oraedu ~]$scp -r $ORACLE_HOME/oraedu_orcl oracle@192.168.127.12:/u01/app/oracle/product/10.2.0/db_1

步骤三、启动测试数据库的侦听和数据库

做此步要保证环境变量和主机名如表格1中的设置。

[oracle@oraedu ~]$ lsnrctlstart ---启动侦听

LSNRCTLfor Linux: Version 10.2.0.1.0 - Production on 28-OCT-2012 15:43:39

Starting/u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNRfor Linux: Version 10.2.0.1.0 - Production

Systemparameter file is/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

Logmessages written to/u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

Listeningon: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraedu)(PORT=1521)))

Listeningon: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraedu)(PORT=1521)))

STATUSof the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version10.2.0.1.0 - Production

StartDate 28-OCT-2012 15:43:40

Uptime 0 days 0 hr. 0 min. 0 sec

TraceLevel off

Security ON: Local OS Authentication

SNMP OFF

ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

ListenerLog File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

ListeningEndpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraedu)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

ServicesSummary...

Service"PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...

Thecommand completed successfully

[oracle@oraedu db_1]$ sqlplus / as sysdba

SQL*Plus:Release 10.2.0.1.0 - Production on Sun Oct 28 15:43:52 2012

Connectedto an idle instance.

SQL> startup ---启动数据库

ORACLEinstance started.

TotalSystem Global Area 314572800 bytes

FixedSize 2020480 bytes

VariableSize 121637760 bytes

DatabaseBuffers 188743680 bytes

RedoBuffers 2170880 bytes

Databasemounted.

Databaseopened.

SQL> select dbid,namefrom v$database; ---查看数据库的DBID，可以发现和主数据库的DBID一样

DBID NAME

-------------------

1320402727ORCL

SQL> select ename,salfrom scott.emp; ---查看scott的emp表，检查数据是否正确

ENAME SAL

--------------------

SMITH 800

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

SCOTT 6000

KING 5000

TURNER 1500

ADAMS 1100

ENAME SAL

--------------------

JAMES 950

FORD 3000

MILLER 1300

14rows selected.

SQL>exit

[oracle@oraedu ~]$ lsnrctlstatus ---查看侦听状态

LSNRCTLfor Linux: Version 10.2.0.1.0 - Production on 28-OCT-2012 15:55:53

Connectingto (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraedu)(PORT=1521)))

STATUSof the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version10.2.0.1.0 - Production

StartDate 28-OCT-2012 15:43:40

Uptime 0 days 0 hr. 12 min. 13 sec

TraceLevel off

Security ON: Local OS Authentication

SNMP OFF

ListenerParameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

ListenerLog File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log

ListeningEndpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraedu)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

ServicesSummary...

Service"PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", statusUNKNOWN, has 1 handler(s) for this service...

Service"orcl" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Service"orclXDB" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Service"orcl_XPT" has 1 instance(s).

Instance "orcl", status READY, has1 handler(s) for this service...

Thecommand completed successfully

[oracle@oraedu ~]$ emctlstart dbconsole ---启动em

TZset to PRC

OracleEnterprise Manager 10g Database Control Release 10.2.0.1.0

http://oraedu:1158/em/console/aboutApplication

StartingOracle Enterprise Manager 10g Database Control ...............................started.

------------------------------------------------------------------

Logsare generated in directory/u01/app/oracle/product/10.2.0/db_1/oraedu_orcl/sysman/log

这样我们就创建了一个和主数据库一模一样的测试数据库，其实还有种方法是测试数据库配置好安装oracle的软件环境后不安装oracle软件，直接把主数据库安装好的oracle软件和数据库复制过来。但是oracle软件文件非常多，又很小，通过网络的方法复制没有上面的方法快。