備份與恢復--一個表空間能否被多個資料庫同時開啟？
一個表空間能否被多個資料庫同時開啟？我也來試試。
這個問題其實是來自論壇上的一個帖子：http://www.itpub.net/showthread.php?s=&threadid=658187。
參考：http://yangtingkun.itpub.net/post/468/228632
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: {
/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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復--一個表空間能否被多個資料庫讀寫？資料庫
- 表空間級資料庫備份恢復資料庫
- Oracle12c多租戶資料庫備份與恢復 - 備份表空間Oracle資料庫
- Oracle12c多租戶資料庫備份與恢復 - 恢復一個PDBOracle資料庫
- Oracle12c多租戶資料庫備份與恢復 - 僅備份一個PDB資料庫Oracle資料庫
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- mysql 無備份恢復drop資料-共享表空間MySql
- 【管理篇備份恢復】rman恢復測試（一） 表空間資料檔案
- 第5章：從開啟的資料庫備份與恢復之從開啟的資料庫備份中完全恢復資料庫
- 備份與恢復：polardb資料庫備份與恢復資料庫
- DB2 使用表空間備份恢復庫DB2
- 一個備份集同時恢出dataguard的主庫&備庫
- 第5章：從開啟的資料庫備份與恢復之備份開啟的資料庫資料庫
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- Oracle12c多租戶資料庫備份與恢復 - 備份CDB下的多個PDBsOracle資料庫
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- Oracle 11g RAN恢復-表空間在只讀時做了資料庫的備份Oracle資料庫
- 資料庫（表）的邏輯備份與恢復資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- 資料庫備份與恢復----第一課資料庫
- postgresql備份與恢復資料庫SQL資料庫
- mongo資料庫備份與恢復Go資料庫
- 資料庫的備份與恢復資料庫
- Informix資料庫備份與恢復ORM資料庫
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 第5章：從開啟的資料庫備份與恢復之從開啟的資料庫備份中進行不完全恢復資料庫
- mysql無備份恢復-獨立表空間MySql
- [DB2]線上備份資料庫與表空間DB2資料庫
- 利用MySQL全備份（mysqldump），如何只恢復一個庫或者一個表？MySql
- 課時7-備份與恢復----資料庫備份策略指令碼資料庫指令碼
- RMAN恢復單個表空間或被DROP/DELETE/TRUNCATE的表delete
- rman資料庫全庫備份與恢復資料庫
- mysql的資料庫備份與恢復MySql資料庫
- oracle資料庫的備份與恢復Oracle資料庫
- 資料庫備份與恢復技術資料庫
- RMAN備份恢復整個庫
- 備份＆恢復之八：RMAN備份歸檔模式下損壞（丟失）多個資料檔案，進行整個資料庫的恢復模式資料庫