備份與恢復--一個表空間能否被多個資料庫同時開啟？

jolly10發表於2009-01-22
資料庫

一個表空間能否被多個資料庫同時開啟？我也來試試。

這個問題其實是來自論壇上的一個帖子：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部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-1016381/，如需轉載，請註明出處，否則將追究法律責任。

相關文章