ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 备份与恢复--一个表空间能否被多个数据库同时打开？

备份与恢复--一个表空间能否被多个数据库同时打开？

原创 Oracle 作者：jolly10 时间：2009-01-22 11:54:37 0 删除 编辑

一个表空间能否被多个数据库同时打开？我也来试试。

这个问题其实是来自论坛上的一个帖子：http://www.itpub.net/showthread.php?s=&threadid=658187。
参考：http://yangtingkun.itpub.net/post/468/228632

[@more@]

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
JGLU ONLINE
TEST ONLINE

8 rows selected.


SQL> SELECT 'ALTER TABLESPACE ' || TABLESPACE_NAME || ' BEGIN BACKUP;'
2 FROM DBA_TABLESPACES
3 WHERE CONTENTS != 'TEMPORARY'
4 AND TABLESPACE_NAME != 'USERS';

'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'
-------------------------------------------------------------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE EXAMPLE BEGIN BACKUP;
ALTER TABLESPACE JGLU BEGIN BACKUP;
ALTER TABLESPACE TEST BEGIN BACKUP;

6 rows selected.

SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE EXAMPLE BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE JGLU BEGIN BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE TEST BEGIN BACKUP;

Tablespace altered.
SQL> select 'host cp '|| file_name || ' /u01/app/oracle/oradata/jolly'
2 from
3 (
4 select file_name from dba_data_files where tablespace_name !='USERS'
5 union all
6 select file_name from dba_temp_files
7 );

'HOSTCP'||FILE_NAME||'/u01/app/oracle/oradata/jolly'
--------------------------------------------------------------------------------
host cp /u01/app/oracle/oradata/orcl/test02.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/jglu02.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/jglu01.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/jolly
host cp /u01/app/oracle/oradata/orcl/temp01.dbf /u01/app/oracle/oradata/jolly

8 rows selected.

SQL> host cp /u01/app/oracle/oradata/orcl/test02.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/jglu02.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/jglu01.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/undotbs01.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/oradata/orcl/temp01.dbf /u01/app/oracle/oradata/jolly


SQL> SELECT 'ALTER TABLESPACE ' || TABLESPACE_NAME || ' END BACKUP;'
2 FROM DBA_TABLESPACES
3 WHERE CONTENTS != 'TEMPORARY'
4 AND TABLESPACE_NAME != 'USERS';

'ALTERTABLESPACE'||TABLESPACE_NAME||'ENDBACKUP;'
-----------------------------------------------------------
ALTER TABLESPACE SYSTEM END BACKUP;
ALTER TABLESPACE UNDOTBS1 END BACKUP;
ALTER TABLESPACE SYSAUX END BACKUP;
ALTER TABLESPACE EXAMPLE END BACKUP;
ALTER TABLESPACE JGLU END BACKUP;
ALTER TABLESPACE TEST END BACKUP;

6 rows selected.

SQL> ALTER TABLESPACE SYSTEM END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSAUX END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE EXAMPLE END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE JGLU END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE TEST END BACKUP;

Tablespace altered.


下面开始新建一个数据库

SQL> host cp /u01/app/oracle/oradata/orcl/*.log /u01/app/oracle/oradata/jolly

SQL> host cp /u01/app/oracle/product/10201/dbs/orapworcl

/u01/app/oracle/product/10201/dbs/orapwjolly

SQL> host mkdir -p /u01/app/oracle/admin/jolly/udump

SQL> host mkdir -p /u01/app/oracle/admin/jolly/bdump

SQL> host mkdir -p /u01/app/oracle/admin/jolly/cdump

SQL> host mkdir -p /u01/app/oracle/admin/jolly/archive

SQL> host mkdir -p /u01/app/oracle/admin/jolly/dpdump

SQL> host mkdir -p /u01/app/oracle/admin/jolly/pfile

SQL> host mkdir -p /u01/app/oracle/admin/jolly/adump

SQL> create pfile='/u01/app/oracle/admin/jolly/pfile/init.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace;

Database altered.


修改参数文件如下：

[oracle@rhel131 pfile]$ cat init.ora
orcl.__db_cache_size=251658240
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=71303168
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/jolly/adump'
*.background_dump_dest='/u01/app/oracle/admin/jolly/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/jolly/control01.ctl','/u01/app/oracle/oradata/jolly/

control02.ctl','/u01/app/oracle/oradata/jolly/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/jolly/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='jolly'
#*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/app/oracle/admin/jolly/archive'
*.open_cursors=300
*.pga_aggregate_target=111149056
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=334495744
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'


[oracle@rhel131 orcl]$ ORACLE_SID=jolly
[oracle@rhel131 orcl]$ echo $ORACLE_SID
jolly
[oracle@rhel131 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 20 00:16:20 2009

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/admin/jolly/pfile/init.ora'
ORACLE instance started.

Total System Global Area 335544320 bytes
Fixed Size 1219280 bytes
Variable Size 104858928 bytes
Database Buffers 226492416 bytes
Redo Buffers 2973696 bytes

用trace出来的controlfile手动建立控制文件，注意要修改一下内容，将REUSE DATABASE "ORCL"改成 SET

DATABASE "JOLLY"

SQL> CREATE CONTROLFILE SET DATABASE "JOLLY" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/jolly/redo01.log' SIZE 50M,
9 GROUP 2 '/u01/app/oracle/oradata/jolly/redo02.log' SIZE 50M,
10 GROUP 3 '/u01/app/oracle/oradata/jolly/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/jolly/system01.dbf',
14 '/u01/app/oracle/oradata/jolly/undotbs01.dbf',
15 '/u01/app/oracle/oradata/jolly/sysaux01.dbf',
16 '/u01/app/oracle/oradata/jolly/example01.dbf',
17 '/u01/app/oracle/oradata/jolly/jglu01.dbf',
18 '/u01/app/oracle/oradata/jolly/jglu02.dbf',
19 '/u01/app/oracle/oradata/jolly/test02.dbf'
20 CHARACTER SET WE8ISO8859P1
21 ;

Control file created.


CREATE CONTROLFILE SET DATABASE "JOLLY" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/jolly/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/jolly/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/jolly/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/jolly/system01.dbf',
'/u01/app/oracle/oradata/jolly/undotbs01.dbf',
'/u01/app/oracle/oradata/jolly/sysaux01.dbf',
'/u01/app/oracle/oradata/jolly/example01.dbf',
'/u01/app/oracle/oradata/jolly/jglu01.dbf',
'/u01/app/oracle/oradata/jolly/jglu02.dbf',
'/u01/app/oracle/oradata/jolly/test02.dbf'
CHARACTER SET WE8ISO8859P1
;

SQL> recover database using backup controlfile;
ORA-00279: change 700340 generated at 01/20/2009 00:00:56 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/admin/jolly/archive/1_4_676515900.dbf
ORA-00280: change 700340 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/jolly/redo03.log
Log applied.
Media recovery complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
EXAMPLE ONLINE
JGLU ONLINE
TEST ONLINE

8 rows selected.


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jolly/system01.dbf
/u01/app/oracle/oradata/jolly/undotbs01.dbf
/u01/app/oracle/oradata/jolly/sysaux01.dbf
/u01/app/oracle/product/10201/dbs/MISSING00004
/u01/app/oracle/oradata/jolly/example01.dbf
/u01/app/oracle/oradata/jolly/jglu01.dbf
/u01/app/oracle/oradata/jolly/jglu02.dbf
/u01/app/oracle/oradata/jolly/test02.dbf

8 rows selected.

将这个文件改名为orcl/users01.dbf,注意不是jolly/users01.dbf

SQL> alter database rename file 'MISSING00004'
2 to '/u01/app/oracle/oradata/orcl/users01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/jolly/system01.dbf
/u01/app/oracle/oradata/jolly/undotbs01.dbf
/u01/app/oracle/oradata/jolly/sysaux01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/jolly/example01.dbf
/u01/app/oracle/oradata/jolly/jglu01.dbf
/u01/app/oracle/oradata/jolly/jglu02.dbf
/u01/app/oracle/oradata/jolly/test02.dbf

8 rows selected.

SQL> alter tablespace users online;

Tablespace altered.


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/jolly/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.


SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
JOLLY


至此，通过备份新建的数据库已经加载了表空间USERS。
而源数据库显然也加载着同一个只读表空间。通过这个实验说明，对于一个只读表空间，可以同时被多个

Oracle数据库所加载。

[oracle@rhel131 orcl]$ ORACLE_SID=orcl
[oracle@rhel131 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 20 00:31:34 2009

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'USERS';

TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
ORCL

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/271283/viewspace-1016381/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 备份与恢复--nologging表无法恢复
下一篇： 备份与恢复--一个表空间能否被多个数据库读写？
请登录后发表评论 登录
全部评论
jolly10

注册时间：2008-02-20

  • 博文量
    263
  • 访问量
    826734

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593