資料檔案位置調整
今天，在做建立ORACLE GOLDENGATE測試資料庫時，將資料庫的資料檔案目錄放在了一個空間佔用比較大的盤上，需要調整過來。下面詳細記錄這一過程。
1、關閉資料庫
shutdown immediate
2、將資料庫的資料檔案移動到新位置
mv /u01/ora10g/oradata/ggtarget /u02/ora10g/oradata
3、啟動資料庫到nomount狀態
-bash-3.2$ export ORACLE_SID=ggtarge
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on 2 14:47:28 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
4、調整control_files引數，將重啟資料庫到mount狀態
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u01/ora10g/orada
ta/ggtarge/control02.ctl, /u01
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=both;
alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl, /u02/ora10g/oradata/ggtarge/control03.ctl' scope=spfile;
System altered.
SQL> select * from v$datafile;
select * from v$datafile
*
ERROR at line 1:
ORA-01507: database not mounted
SQL>
發現直接修改spfile，會給引數中帶來單引號，考慮修改pfile解決
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u02/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u02/ora10g/orada
ta/ggtarge/control02.ctl, /u02
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
編輯引數檔案/u01/ora10g/product/10.2.0/db_1/dbs/initggtarge.ora,修改引數：
*.control_files='/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl,/u02/ora10g/oradata/ggtarge/control03.ctl'
改為*.control_files=/u02/ora10g/oradata/ggtarge/control01.ctl, /u02/ora10g/oradata/ggtarge/control02.ctl,/u02/ora10g/oradata/ggtarge/control03.ctl
再次啟動資料庫到mount狀態：
SQL> startup mount pfile=/u01/ora10g/product/10.2.0/db_1/dbs/initggtarge.ora
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u02/ora10g/oradata/ggtarge/co
ntrol01.ctl, /u02/ora10g/orada
ta/ggtarge/control02.ctl, /u02
/ora10g/oradata/ggtarge/contro
l03.ctl
SQL> create spfile from pfile;
File created.
5、修改資料檔案
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist
SQL> desc dba_datafiles;
ERROR:
ORA-04043: object dba_datafiles does not exist
SQL> desc v$datafile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
CHECKPOINT_CHANGE# NUMBER
CHECKPOINT_TIME DATE
UNRECOVERABLE_CHANGE# NUMBER
UNRECOVERABLE_TIME DATE
LAST_CHANGE# NUMBER
LAST_TIME DATE
OFFLINE_CHANGE# NUMBER
ONLINE_CHANGE# NUMBER
ONLINE_TIME DATE
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
PLUGGED_IN NUMBER
BLOCK1_OFFSET NUMBER
AUX_NAME VARCHAR2(513)
FIRST_NONLOGGED_SCN NUMBER
FIRST_NONLOGGED_TIME DATE
SQL> col name for a40 ;
SQL> select name from v$datafile;
NAME
----------------------------------------
/u01/ora10g/oradata/ggtarge/system01.dbf
/u01/ora10g/oradata/ggtarge/undotbs01.db
f
/u01/ora10g/oradata/ggtarge/sysaux01.dbf
/u01/ora10g/oradata/ggtarge/users01.dbf
SQL> alter database rename datafile '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf';
alter database rename datafile '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/system01.dbf' to '/u02/ora10g/oradata/ggtarge/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/undotbs01.dbf' to '/u02/ora10g/oradata/ggtarge/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/sysaux01.dbf' to '/u02/ora10g/oradata/ggtarge/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/users01.dbf' to '/u02/ora10g/oradata/ggtarge/users01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
----------------------------------------
/u02/ora10g/oradata/ggtarge/system01.dbf
/u02/ora10g/oradata/ggtarge/undotbs01.db
f
/u02/ora10g/oradata/ggtarge/sysaux01.dbf
/u02/ora10g/oradata/ggtarge/users01.dbf
6、修改日誌檔案
SQL> desc v$logfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
SQL> col member for a40;
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/u01/ora10g/oradata/ggtarge/redo03.log
/u01/ora10g/oradata/ggtarge/redo02.log
/u01/ora10g/oradata/ggtarge/redo01.log
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo03.log' to '/u02/ora10g/oradata/ggtarge/redo03.log';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo02.log' to '/u02/ora10g/oradata/ggtarge/redo02.log';
Database altered.
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/redo01.log' to '/u02/ora10g/oradata/ggtarge/redo01.log';
Database altered.
SQL> select member from v$logfile;
MEMBER
----------------------------------------
/u02/ora10g/oradata/ggtarge/redo03.log
/u02/ora10g/oradata/ggtarge/redo02.log
/u02/ora10g/oradata/ggtarge/redo01.log
7、修改臨時檔案
SQL> desc v$tempfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
SQL> select name from v$tempfile;
NAME
----------------------------------------
/u01/ora10g/oradata/ggtarge/temp01.dbf
SQL> alter database rename file '/u01/ora10g/oradata/ggtarge/temp01.dbf' to '/u02/ora10g/oradata/ggtarge/temp01.dbf
2 ';
Database altered.
SQL> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS
---------- ---------------- -------------- ---------- ---------- --------------
ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
-------------------- ---------- ---------- ------------ ----------
NAME
----------------------------------------
1 464714 07-1210 3 1 ONLINE
READ WRITE 0 0 20971520 8192
/u02/ora10g/oradata/ggtarge/temp01.dbf
8、測試將資料庫啟動到OPEN狀態
SQL> alter database open;
Database altered.
SQL> select sysdate from dual;
SYSDATE
--------------
07-1210
OK，移動資料檔案位置後成功開啟資料庫
9、差點忘了，已經開了歸檔了，但是沒有設定歸檔目錄
SQL> show parameter archive;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
archive_lag_target integer
0
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
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
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
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
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
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
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
enable
log_archive_duplex_dest string
log_archive_format string
%t_%s_%r.dbf
log_archive_local_first boolean
TRUE
log_archive_max_processes integer
2
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_min_succeed_dest integer
1
log_archive_start boolean
FALSE
log_archive_trace integer
0
remote_archive_enable string
true
standby_archive_dest string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
?/dbs/arch
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
調整歸檔目錄：
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;
alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=spfile;
alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
看下是我剛剛以pfile啟動資料庫因此必須要將資料庫重新以spfile啟動後修改
SQL> startup nomount
ORACLE instance started.
Total System Global Area 222298112 bytes
Fixed Size 1266680 bytes
Variable Size 75500552 bytes
Database Buffers 138412032 bytes
Redo Buffers 7118848 bytes
SQL> alter system set log_archive_dest_1='location=/u02/ora10g/oradata/archive' scope=both;
System altered.
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter log_archive_dest_1
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_1 string
location=/u02/ora10g/oradata/a
rchive
log_archive_dest_10 string
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/ora10g/oradata/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
OK，所有需要調整的引數都已經調整過來
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12350275/viewspace-688554/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【TABLESPACE】通過重建控制檔案調整表空間資料檔案位置及名稱
- 【TABLESPACE】資料庫Open狀態下調整表空間資料檔案位置及名稱資料庫
- 【TABLESPACE】資料庫Mount狀態下調整表空間資料檔案位置及名稱資料庫
- 調整資料庫的資料檔案記錄資料庫
- UITableViewCell分割線位置調整UIView
- 更改資料檔案位置或改名
- 【問題處理】通過調整資料檔案的位置解決磁碟空間緊張的問題
- AIX檔案系統調整AI
- 資料塊調整
- 更改MySQL資料檔案存放目錄位置MySql
- 更改資料庫裸裝置資料檔案的位置資料庫
- dataguard之主庫增加資料檔案--物理備庫相應的調整（一）
- Nginx 調整檔案上傳大小限制Nginx
- DBeaver如何調整sql檔案編碼SQL
- PDB資料檔案的線上位置以及檔名更改
- JavaScript方向鍵調整div元素的位置JavaScript
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- 修改資料檔案的位置的兩種方法
- RMAN 資料庫克隆檔案位置轉換方法資料庫
- dataguard之主庫增加資料檔案--邏輯備庫相應的調整（二）
- 調整資料庫引數資料庫
- 2018.3.29 DIV位置調整程式碼
- excel中如何調整橫座標的刻度位置Excel
- 2 Day DBA-介紹-資料庫檔案位置資料庫
- solaris11調整檔案系統配額
- Oracle 資料庫引數調整Oracle資料庫
- 資料庫引擎調整顧問資料庫
- oracle資料庫的效能調整Oracle資料庫
- hosts檔案位置
- win10語言欄位置調整方法，win10語言欄怎麼調位置Win10
- 來改變一下Oracle資料檔案的位置Oracle
- 更改MySQL資料檔案目錄位置MySql
- 調整CALLCENTER系統的資料庫資料庫
- oracle資料庫的效能調整(轉)Oracle資料庫
- webpack配置檔案中屬性的位置和資料結構Web資料結構
- oracle效能調優:管理oracle日誌之調整線上日誌檔案Oracle
- 更改Oracle檔案位置Oracle
- oracle 12c R1 可以線上對資料檔案重新命名或者移動資料檔案位置Oracle