热备下的测试库搭建

2023-10-31 15:31:58

热备下的测试库搭建

blog文档结构图：

其实在某些情况下，测试库的搭建也作为备份的一种方式，本节就来看在有热备的情况下如何搭建测试库。

冷备下的测试库搭建：http://blog.itpub.net/26736162/viewspace-1352243/

采用duplicate搭建测试库（asm--》os文件）： http://blog.itpub.net/26736162/viewspace-1224861/

采用duplicate复制活动数据来搭建测试库： http://blog.itpub.net/26736162/viewspace-1223247/

在只有rman备份的情况下如何搭建测试库：http://blog.itpub.net/26736162/viewspace-1223253/

1 基础知识

热备份是当数据库打开并对用户有效是的操作系统级的数据备份。热备份只能用于ARCHIVELOG方式的数据库。在数据文件备份之前，对应的表空间必须通过使用ALTER TABLESPACE …… BEGIN BACKUP以备份方式放置。然后组成表空间的数据文件可以使用类似冷备份的操作系统命令进行拷贝。在数据文件用操作系统命令拷贝后，应使用ALTER TABLESPACE …… END BACKUP命令使表空间脱离热备份方式。

2 本次实验环境简介

项目	source	target
IP地址	192.168.59.130	192.168.59.10
ORACLE_HOME	/u01/app/oracle/product/11.2.0/dbhome_1	/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID	utf8test	utf8test
是否归档	Y	Y
hostname	rhel6_lhr	testdb

3 源库生成备份文件

源库执行热备脚本：

set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/home/oracle/oracle_bk/hotbak'

define script = '/tmp/hotbak_tb.sql'

spool &script

select 'alter tablespace '|| tablespace_name ||' begin backup ;' ||

chr(10)||'ho cp ' || file_name || ' &dir ' ||

chr(10)||'alter tablespace '|| tablespace_name || ' end backup;'

from dba_data_files order by tablespace_name;

spool off

alter system switch logfile;

start &script

alter system switch logfile;

alter database backup controlfile to '&dir/controlbak.ctl';

alter database backup controlfile to trace as '&dir/controlbak.sql';

create pfile = '&dir/initorcl.ora' from spfile;

[oracle@rhel6_lhr ~]$ echo $ORACLE_SID

utf8test

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 17 16:00:47 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> ho more /tmp/hotbak.sql

set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/home/oracle/oracle_bk/hotbak'

define script = '/tmp/hotbak_tb.sql'

spool &script

select 'alter tablespace '|| tablespace_name ||' begin backup ;' ||

chr(10)||'ho cp ' || file_name || ' &dir ' ||

chr(10)||'alter tablespace '|| tablespace_name || ' end backup;'

from dba_data_files order by tablespace_name;

spool off

alter system switch logfile;

start &script

alter system switch logfile;

alter database backup controlfile to '&dir/controlbak.ctl';

alter database backup controlfile to trace as '&dir/controlbak.sql';

create pfile = '&dir/initorcl.ora' from spfile;

SQL> @/tmp/hotbak.sql;

alter tablespace SYSAUX begin backup ;

ho cp /u01/app/oracle/oradata/utf8test/sysaux01.dbf /home/oracle/oracle_bk/hotbak

alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup ;

ho cp /u01/app/oracle/oradata/utf8test/system01.dbf /home/oracle/oracle_bk/hotbak

alter tablespace SYSTEM end backup;

alter tablespace UNDOTBS1 begin backup ;

ho cp /u01/app/oracle/oradata/utf8test/undotbs01.dbf /home/oracle/oracle_bk/hotbak

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup ;

ho cp /u01/app/oracle/oradata/utf8test/users01.dbf /home/oracle/oracle_bk/hotbak

alter tablespace USERS end backup;

SQL> ho ls -l /home/oracle/oracle_bk/hotbak

total 1391972

-rw-r----- 1 oracle asmadmin 9748480 Jan 17 16:03 controlbak.ctl

-rw-r--r-- 1 oracle asmadmin 6810 Jan 17 16:03 controlbak.sql

-rw-r--r-- 1 oracle asmadmin 889 Jan 17 16:03 initorcl.ora

-rw-r----- 1 oracle oinstall 608182272 Jan 17 16:02 sysaux01.dbf

-rw-r----- 1 oracle oinstall 744497152 Jan 17 16:03 system01.dbf

-rw-r----- 1 oracle oinstall 52436992 Jan 17 16:03 undotbs01.dbf

-rw-r----- 1 oracle oinstall 10493952 Jan 17 16:03 users01.dbf

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 8

Next log sequence to archive 10

Current log sequence 10

SQL>

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

1 1 10 52428800 512 1 NO CURRENT 1705635 2015-01-17 17:27:28 2.8147E+14

2 1 8 52428800 512 1 YES INACTIVE 1705446 2015-01-17 17:21:27 1705565 2015-01-17 17:26:31

3 1 9 52428800 512 1 YES INACTIVE 1705565 2015-01-17 17:26:31 1705635 2015-01-17 17:27:28

SQL> select SEQUENCE#,NAME,RESETLOGS_CHANGE#,FIRST_CHANGE# from v$archived_log order by SEQUENCE#;

SEQUENCE# NAME RESETLOGS_CHANGE# FIRST_CHANGE#

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

1 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_869231211.dbf 1658549 1658549

2 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_2_869231211.dbf 1658549 1678937

3 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_3_869231211.dbf 1658549 1679206

4 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_4_869231211.dbf 1658549 1679279

4 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_4_869137332.dbf 1591683 1638296

5 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_869231211.dbf 1658549 1700469

5 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_5_869137332.dbf 1591683 1654983

6 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_869137332.dbf 1591683 1656602

6 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_6_869231211.dbf 1658549 1702565

7 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_869137332.dbf 1591683 1656674

7 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_869231211.dbf 1658549 1702640

8 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_8_869231211.dbf 1658549 1705446

9 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_9_869231211.dbf 1658549 1705565

14 rows selected.

SQL>

SQL> exit

4 传输备份文件到target库

4.1 传输数据文件

[oracle@rhel6_lhr hotbak]$ pwd

/home/oracle/oracle_bk/hotbak

[oracle@rhel6_lhr hotbak]$ scp -r /home/oracle/oracle_bk/hotbak oracle@192.168.59.10:/home/oracle/oracle_bk/

oracle@192.168.59.10's password:

undotbs01.dbf 100% 50MB 50.0MB/s 00:00

controlbak.ctl 100% 9520KB 9.3MB/s 00:00

initorcl.ora 100% 889 0.9KB/s 00:00

sysaux01.dbf 100% 580MB 24.2MB/s 00:24

users01.dbf 100% 10MB 10.0MB/s 00:00

controlbak.sql 100% 6810 6.7KB/s 00:00

system01.dbf 100% 710MB 25.4MB/s 00:28

You have new mail in /var/spool/mail/oracle

[oracle@rhel6_lhr hotbak]$

4.2 传输归档文件

[oracle@rhel6_lhr dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@rhel6_lhr dbs]$ ll *.dbf

-rw-r----- 1 oracle asmadmin 82432 Jan 17 13:50 arch1_1_869231211.dbf

-rw-r----- 1 oracle asmadmin 61440 Jan 17 13:51 arch1_2_869231211.dbf

-rw-r----- 1 oracle asmadmin 32768 Jan 17 15:22 arch1_3_869231211.dbf

-rw-r----- 1 oracle asmadmin 34149888 Jan 17 11:08 arch1_4_869137332.dbf

-rw-r----- 1 oracle asmadmin 1465856 Jan 17 15:22 arch1_4_869231211.dbf

-rw-r----- 1 oracle asmadmin 1265152 Jan 17 13:06 arch1_5_869137332.dbf

-rw-r----- 1 oracle asmadmin 416256 Jan 17 16:02 arch1_5_869231211.dbf

-rw-r----- 1 oracle asmadmin 32256 Jan 17 13:06 arch1_6_869137332.dbf

-rw-r----- 1 oracle asmadmin 36864 Jan 17 16:03 arch1_6_869231211.dbf

-rw-r----- 1 oracle asmadmin 1580544 Jan 17 13:06 arch1_7_869137332.dbf

-rw-r----- 1 oracle asmadmin 2521088 Jan 17 17:21 arch1_7_869231211.dbf

-rw-r----- 1 oracle asmadmin 14336 Jan 17 17:26 arch1_8_869231211.dbf

-rw-r----- 1 oracle asmadmin 32768 Jan 17 17:27 arch1_9_869231211.dbf

[oracle@rhel6_lhr dbs]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_7_869* oracle@192.168.59.10:/home/oracle/oracle_bk/

oracle@192.168.59.10's password:

arch1_7_869137332.dbf 100% 1544KB 1.5MB/s 00:00

arch1_7_869231211.dbf 100% 2462KB 2.4MB/s 00:00

[oracle@rhel6_lhr dbs]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_8_869* oracle@192.168.59.10:/home/oracle/oracle_bk/

oracle@192.168.59.10's password:

arch1_8_869231211.dbf 100% 14KB 14.0KB/s 00:00

[oracle@rhel6_lhr dbs]$ scp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_9_869* oracle@192.168.59.10:/home/oracle/oracle_bk/

oracle@192.168.59.10's password:

arch1_9_869231211.dbf 100% 32KB 32.0KB/s 00:00

[oracle@rhel6_lhr dbs]$

5 修改target库的pfile文件并生成pfile文件中的路径

热备文件中包含了pfile文件，修改后：

[oracle@testdb hotbak]$ mkdir -p /u01/app/oracle/admin/utf8test/adump

[oracle@testdb hotbak]$ mkdir -p /u01/app/oracle/oradata/utf8test/

[oracle@testdb hotbak]$ vi initutf8test.ora

[oracle@testdb hotbak]$ more initutf8test.ora

*.audit_file_dest='/u01/app/oracle/admin/utf8test/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/utf8test/control01.ctl','/u01/app/oracle/oradata/utf8test/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='utf8test'

*.diagnostic_dest='/u01/app/oracle'

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

*.memory_target=500956224

*.open_cursors=300

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=335

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

[oracle@testdb hotbak]$

6 启动target数据库到nomount状态

[oracle@testdb hotbak]$ ORACLE_SID=utf8test

[oracle@testdb hotbak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 17 16:20:06 2015

Connected to an idle instance.

SQL> startup pfile='/home/oracle/oracle_bk/hotbak/initutf8test.ora' nomount;

ORACLE instance started.

Total System Global Area 501059584 bytes

Fixed Size 2229744 bytes

Variable Size 327158288 bytes

Database Buffers 163577856 bytes

Redo Buffers 8093696 bytes

SQL> create spfile from pfile='/home/oracle/oracle_bk/hotbak/initutf8test.ora';

File created.

SQL>

7 开始创建控制文件

从热备的控制文件文本中得到如下控制文件的创建脚本：

CREATE CONTROLFILE REUSE DATABASE "UTF8TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/utf8test/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/utf8test/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/utf8test/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/utf8test/system01.dbf',

'/u01/app/oracle/oradata/utf8test/sysaux01.dbf',

'/u01/app/oracle/oradata/utf8test/undotbs01.dbf',

'/u01/app/oracle/oradata/utf8test/users01.dbf'

CHARACTER SET AL32UTF8

;

7.1 第一步，首先移动相应的数据文件到相应的控制文件记录的目录中

[oracle@testdb hotbak]$ ll

total 1391976

-rw-r-----. 1 oracle oinstall 9748480 Jan 17 16:06 controlbak.ctl

-rw-r--r--. 1 oracle oinstall 6810 Jan 17 16:06 controlbak.sql

-rw-r--r--. 1 oracle oinstall 889 Jan 17 16:06 initorcl.ora

-rw-r--r--. 1 oracle oinstall 532 Jan 17 16:19 initutf8test.ora

-rw-r-----. 1 oracle oinstall 608182272 Jan 17 16:06 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 744497152 Jan 17 16:07 system01.dbf

-rw-r-----. 1 oracle oinstall 52436992 Jan 17 16:06 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 10493952 Jan 17 16:06 users01.dbf

[oracle@testdb hotbak]$ cp *.dbf /u01/app/oracle/oradata/utf8test/

[oracle@testdb hotbak]$

7.2 第二步，重新创建控制文件，控制文件创建完成后自动mount

[oracle@testdb hotbak]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 17 16:29:24 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

CREATE CONTROLFILE REUSE DATABASE "UTF8TEST" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/utf8test/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oracle/oradata/utf8test/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oracle/oradata/utf8test/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/utf8test/system01.dbf',

'/u01/app/oracle/oradata/utf8test/sysaux01.dbf',

'/u01/app/oracle/oradata/utf8test/undotbs01.dbf',

'/u01/app/oracle/oradata/utf8test/users01.dbf'

CHARACTER SET AL32UTF8

18 ;

Control file created.

SQL> ho ls -l /u01/app/oracle/oradata/utf8test/cont*

-rw-r-----. 1 oracle oinstall 10076160 Jan 17 17:37 /u01/app/oracle/oradata/utf8test/control01.ctl

-rw-r-----. 1 oracle oinstall 10076160 Jan 17 17:37 /u01/app/oracle/oradata/utf8test/control02.ctl

SQL>

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL>

8 用rman注册一下

[oracle@testdb hotbak]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 17 17:39:53 2015

connected to target database: UTF8TEST (DBID=2518944702, not open)

RMAN> catalog start with '/home/oracle/oracle_bk/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /home/oracle/oracle_bk/

List of Files Unknown to the Database

=====================================

File Name: /home/oracle/oracle_bk/arch1_9_869231211.dbf

File Name: /home/oracle/oracle_bk/hotbak/undotbs01.dbf

File Name: /home/oracle/oracle_bk/hotbak/controlbak.ctl

File Name: /home/oracle/oracle_bk/hotbak/initutf8test.ora

File Name: /home/oracle/oracle_bk/hotbak/initorcl.ora

File Name: /home/oracle/oracle_bk/hotbak/sysaux01.dbf

File Name: /home/oracle/oracle_bk/hotbak/users01.dbf

File Name: /home/oracle/oracle_bk/hotbak/controlbak.sql

File Name: /home/oracle/oracle_bk/hotbak/system01.dbf

File Name: /home/oracle/oracle_bk/arch1_7_869137332.dbf

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853869892_2.bak

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853870080_5.bak

File Name: /home/oracle/oracle_bk/orcl/control_c-1381650135-20140801-01.bak

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853869894_4.bak

File Name: /home/oracle/oracle_bk/orcl/control_c-1381650135-20140801-00.bak

File Name: /home/oracle/oracle_bk/orcl/control_c-1381650135-20140725-00.bak

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853869894_3.bak

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853869892_1.bak

File Name: /home/oracle/oracle_bk/arch1_7_869231211.dbf

File Name: /home/oracle/oracle_bk/arch1_8_869231211.dbf

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /home/oracle/oracle_bk/arch1_9_869231211.dbf

File Name: /home/oracle/oracle_bk/hotbak/undotbs01.dbf

File Name: /home/oracle/oracle_bk/hotbak/controlbak.ctl

File Name: /home/oracle/oracle_bk/hotbak/sysaux01.dbf

File Name: /home/oracle/oracle_bk/hotbak/users01.dbf

File Name: /home/oracle/oracle_bk/hotbak/system01.dbf

File Name: /home/oracle/oracle_bk/arch1_7_869137332.dbf

File Name: /home/oracle/oracle_bk/arch1_7_869231211.dbf

File Name: /home/oracle/oracle_bk/arch1_8_869231211.dbf

List of Files Which Where Not Cataloged

=======================================

File Name: /home/oracle/oracle_bk/hotbak/initutf8test.ora

RMAN-07517: Reason: The file header is corrupted

File Name: /home/oracle/oracle_bk/hotbak/initorcl.ora

RMAN-07517: Reason: The file header is corrupted

File Name: /home/oracle/oracle_bk/hotbak/controlbak.sql

RMAN-07517: Reason: The file header is corrupted

File Name: /home/oracle/oracle_bk/orcl/full_ORCLxxxx_20140725_853869892_2.bak