非歸檔模式下遷移10g單機庫到新的儲存上
原庫為10.2.0.4單機，檔案系統方式，非歸檔模式
第一步：資料遷移之前，先進行原環境檢查
原環境建立了一個使用者tt，並建立測試表tt
建立了一個使用者tt作為測試用
SQL> create tablespace tt datafile '/home/oracle/oradata/fsdb/tt.dbf' size 30m;
Tablespace created.
SQL> create user tt identified by tt default tablespace tt;
User created.
SQL> grant dba to tt;
Grant succeeded.
SQL> connect tt/tt;
Connected.
SQL> create table tt as select * from dba_objects;
Table created.
SQL> insert into tt select * from tt;
50616 rows created.
SQL> /
101232 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from tt;
COUNT(*)
----------
202464
原資料庫及庫檔案的儲存位置如下
[oracle@fshost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 16:16:58 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 30
Current log sequence 32
SQL>
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/example01.dbf
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@fshost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 16:16:58 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/product/10.2.0/db_1/dbs/arch
Oldest online log sequence 30
Current log sequence 32
SQL>
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/control01.ctl
/home/oracle/oradata/fsdb/control02.ctl
/home/oracle/oradata/fsdb/control03.ctl
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/example01.dbf
/home/oracle/oradata/fsdb/tt.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/redo03.log
/home/oracle/oradata/fsdb/redo02.log
/home/oracle/oradata/fsdb/redo01.log
SQL>
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/temp01.dbf
SQL>
原ORACLE_HOME資訊如下
[oracle@fshost ~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_SID=fsdb;
export ORACLE_BASE=/home/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export PATH=$ORACLE_HOME/bin:${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$
{JAVA_HOME}/bin;
PATH=$PATH:$HOME/bin
export PATH
從以上資訊可以看到，oracle資料庫除了庫檔案在儲存上以外，其他的ORACLE軟體目錄都存在主機本地磁碟上
第二步：將新儲存接入主機，並劃分分割槽，建立掛載點目錄，並設定掛載點隨主機重啟而自動掛載
[root@fshost ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 3263 26105625 8e Linux LVM
Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
[root@fshost ~]# fdisk /dev/sdb
The number of cylinders for this disk is set to 1305.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):
Using default value 1305
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
You have new mail in /var/spool/mail/root
[root@fshost ~]# fdisk -l
Disk /dev/sda: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 3263 26105625 8e Linux LVM
Disk /dev/sdb: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1305 10482381 83 Linux
然後在對應分割槽上建立檔案系統
[root@fshost ~]# mkfs -t ext3 -c /dev/sdb1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
1310720 inodes, 2620595 blocks
131029 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2684354560
80 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632
Checking for bad blocks (read-only test): done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
修改/etc/fstab使分割槽隨主機啟動而啟動
[root@fshost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
/dev/sdb1 /newdata ext3 defaults 1 2
最終檢視檔案系統如下
[root@fshost ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
22G 11G 11G 50% /
/dev/sda1 99M 13M 82M 13% /boot
tmpfs 768M 0 768M 0% /dev/shm
/dev/scd0 2.9G 2.9G 0 100% /media/RHEL_5.5 i386 DVD
/dev/sdb1 9.9G 151M 9.2G 2% /newdata
第三步：現在將 ORACLE軟體目錄保持不變，然後將所有的庫檔案都移到新儲存掛點 /newdat上
[root@fshost fsdb]# su - oracle
[oracle@fshost ~]$ cd $ORACLE_HOME/dbs
[oracle@fshost dbs]$ pwd
/home/oracle/product/10.2.0/db_1/dbs
[oracle@fshost dbs]$ ls
hc_fsdb.dat initfsdb.ora lkFSDB spfilefsdb.ora
initdw.ora init.ora orapwfsdb spfilefsdb.ora.bak
[oracle@fshost dbs]$ ls -l
total 52
-rw-rw---- 1 oracle dba 1544 Mar 5 15:07 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 746 Feb 9 14:38 initfsdb.ora
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-r----- 1 oracle dba 24 Feb 9 12:34 lkFSDB
-rw-r----- 1 oracle dba 1536 Feb 9 12:36 orapwfsdb
-rw-r----- 1 oracle dba 2560 Mar 5 15:07 spfilefsdb.ora
-rw-r----- 1 oracle dba 2560 Feb 9 15:02 spfilefsdb.ora.bak
檢視引數檔案內容
[oracle@fshost dbs]$ more initfsdb.ora
aq_tm_processes=0
audit_file_dest=/home/oracle/admin/fsdb/adump
background_dump_dest=/home/oracle/admin/fsdb/bdump
compatible=10.2.0.1.0
control_files=/home/oracle/oradata/fsdb/control01.ctl, /home/oracle/oradata/fsdb
/control02.ctl, /home/oracle/oradata/fsdb/control03.ctl
core_dump_dest=/home/oracle/admin/fsdb/cdump
db_block_size=8192
db_domain=""
db_file_multiblock_read_count=16
db_name=fsdb
dispatchers="(PROTOCOL=TCP) (SERVICE=fsdbXDB)"
job_queue_processes=10
nls_length_semantics=BYTE
open_cursors=300
pga_aggregate_target=149946368
processes=150
remote_login_passwordfile=EXCLUSIVE
resource_manager_plan=""
sga_target=452984832
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDOTBS1
user_dump_dest=/home/oracle/admin/fsdb/udump
第四步：正式遷移
1：首先執行 lsnrctl stop 關閉監聽
2：以shutdown immediate方式關閉資料庫
[oracle@fshost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 17:05:32 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
3：新建資料夾，並修改相關許可權
[root@fshost ~]# cd /newdata/
[root@fshost newdata]# mkdir fsdb
[root@fshost newdata]# pwd
/newdata
[root@fshost newdata]# ls
fsdb lost+found
[root@fshost newdata]# chown -R oracle:dba /newdata/fsdb/
[root@fshost newdata]#
[root@fshost newdata]# chmod -R 777 /newdata/fsdb/
[root@fshost newdata]#
再次檢查確認相關目錄的屬主是否為oracle使用者，dba組
4：然後將原庫檔案複製到新儲存掛載點 /newdata/fsdb目錄下
[oracle@fshost fsdb]$ nohup cp -R /home/oracle/oradata/fsdb/* /newdata/fsdb &
[1] 5794
[oracle@fshost fsdb]$ nohup: appending output to `nohup.out'
複製完成之後，檢查檔案的個數是否一致，檔案的大小是否一致
[oracle@fshost fsdb]$ ls -l |wc -l
15
注意：undo表空間對應的資料檔案大小可能不一致，不影響
5：備份原pfile和spfile檔案
[oracle@fshost fsdb]$ cd $ORACLE_HOME/dbs
[oracle@fshost dbs]$ ls -l
total 48
-rw-rw---- 1 oracle dba 1544 Mar 5 17:05 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 746 Feb 9 14:38 initfsdb.ora
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-r----- 1 oracle dba 24 Feb 9 12:34 lkFSDB
-rw-r----- 1 oracle dba 1536 Feb 9 12:36 orapwfsdb
-rw-r----- 1 oracle dba 2560 Mar 5 15:07 spfilefsdb.ora
[oracle@fshost dbs]$ cp spfilefsdb.ora spfilefsdb.ora.bak
[oracle@fshost dbs]$
[oracle@fshost dbs]$ cp initfsdb.ora initfsdb.ora.bak
[oracle@fshost dbs]$
然後刪除spfile檔案 spfilefsdb.ora
6：修改pfile檔案initfsdb.ora，將控制檔案的路徑改為最新的路徑 ，其他的路徑不變
修改後的 initfsdb.ora 檔案內容如下
[oracle@fshost dbs]$ cat initfsdb.ora
aq_tm_processes=0
audit_file_dest=/home/oracle/admin/fsdb/adump
background_dump_dest=/home/oracle/admin/fsdb/bdump
compatible=10.2.0.1.0
control_files=/newdata/fsdb/control01.ctl, /newdata/fsdb/control02.ctl, /newdata/fsdb/control03.ctl
core_dump_dest=/home/oracle/admin/fsdb/cdump
db_block_size=8192
db_domain=""
db_file_multiblock_read_count=16
db_name=fsdb
dispatchers="(PROTOCOL=TCP) (SERVICE=fsdbXDB)"
job_queue_processes=10
nls_length_semantics=BYTE
open_cursors=300
pga_aggregate_target=149946368
processes=150
remote_login_passwordfile=EXCLUSIVE
resource_manager_plan=""
sga_target=452984832
undo_management=AUTO
undo_retention=900
undo_tablespace=UNDOTBS1
user_dump_dest=/home/oracle/admin/fsdb/udump
7：將資料庫啟動到mount狀態
[oracle@fshost ~]$ cd $ORACLE_HOME/dbs
[oracle@fshost dbs]$ ls -l
total 52
-rw-rw---- 1 oracle dba 1544 Mar 5 17:05 hc_fsdb.dat
-rw-r--r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 711 Mar 5 22:37 initfsdb.ora
-rw-r----- 1 oracle dba 746 Mar 5 22:33 initfsdb.ora.bak
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-r----- 1 oracle dba 24 Feb 9 12:34 lkFSDB
-rw-r----- 1 oracle dba 1536 Feb 9 12:36 orapwfsdb
-rw-r----- 1 oracle dba 2560 Mar 5 22:33 spfilefsdb.ora.bak
[oracle@fshost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 5 22:40:29 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount pfile='/home/oracle/product/10.2.0/db_1/dbs/initfsdb.ora';
ORACLE instance started.
Total System Global Area 452984832 bytes
Fixed Size 1268028 bytes
Variable Size 130025156 bytes
Database Buffers 318767104 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL>
檢視到控制檔案記錄的資料檔案、日誌檔案、臨時檔案還是原來的位置
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/system01.dbf
/home/oracle/oradata/fsdb/undotbs01.dbf
/home/oracle/oradata/fsdb/sysaux01.dbf
/home/oracle/oradata/fsdb/users01.dbf
/home/oracle/oradata/fsdb/example01.dbf
/home/oracle/oradata/fsdb/tt.dbf
6 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/home/oracle/oradata/fsdb/redo03.log
/home/oracle/oradata/fsdb/redo02.log
/home/oracle/oradata/fsdb/redo01.log
8：然後將資料檔案、日誌檔案、臨時檔案的位置改到最新位置
修改資料檔案位置
SQL> alter database rename file '/home/oracle/oradata/fsdb/system01.dbf' to '/newdata/fsdb/system01.dbf';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/undotbs01.dbf' to '/newdata/fsdb/undotbs01.dbf';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/sysaux01.dbf' to '/newdata/fsdb/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/users01.dbf' to '/newdata/fsdb/users01.dbf';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/example01.dbf' to '/newdata/fsdb/example01.dbf';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/tt.dbf' to '/newdata/fsdb/tt.dbf';
Database altered.
修改日誌檔案位置
SQL> alter database rename file '/home/oracle/oradata/fsdb/redo03.log' to '/newdata/fsdb/redo03.log';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/redo02.log' to '/newdata/fsdb/redo02.log';
Database altered.
SQL> alter database rename file '/home/oracle/oradata/fsdb/redo01.log' to '/newdata/fsdb/redo01.log';
Database altered.
SQL>
修改臨時檔案位置
SQL> alter database rename file '/home/oracle/oradata/fsdb/temp01.dbf' to '/newdata/fsdb/temp01.dbf';
Database altered.
9：將資料庫啟動到open狀態
SQL> alter database open;
Database altered.
10：檢查庫檔案是否都已經在新的儲存上
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/newdata/fsdb/users01.dbf
/newdata/fsdb/sysaux01.dbf
/newdata/fsdb/undotbs01.dbf
/newdata/fsdb/system01.dbf
/newdata/fsdb/example01.dbf
/newdata/fsdb/tt.dbf
6 rows selected.
、
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/newdata/fsdb/control01.ctl
/newdata/fsdb/control02.ctl
/newdata/fsdb/control03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/newdata/fsdb/redo03.log
/newdata/fsdb/redo02.log
/newdata/fsdb/redo01.log
SQL>
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/newdata/fsdb/temp01.dbf
SQL>
11：驗證tt表裡的業務資料
SQL> connect tt/tt;
Connected.
SQL> select count(*) from tt;
COUNT(*)
----------
202464
SQL>
業務資料結果一致，資料遷移成功
12：老儲存上解除LUN對主機的對映
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722291/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE 10G ASM非歸檔模式下使用RMAN遷移一例Oracle 10gASM模式
- rac歸檔模式/非歸檔模式下檔案建至本地儲存修復方法-ORA-01157模式
- 【ARCHIVE】單機環境修改資料庫為非歸檔模式Hive資料庫模式
- ORACLE 10g RAC 遷移共享儲存Oracle 10g
- ORACLE RAC模式下歸檔模式和非歸檔模式的切換方法Oracle模式
- 非歸檔模式下恢復資料庫兩例模式資料庫
- Oracle歸檔模式和非歸檔模式Oracle模式
- 非歸檔模式改為歸檔模式模式
- 歸檔模式與非歸檔模式的切換模式
- 【轉】雙節點RAC下將資料庫從非歸檔模式更改到歸檔模式資料庫模式
- 非歸檔模式下的資料備份模式
- Oracle歸檔模式和非歸檔模式的區別Oracle模式
- rman 非歸檔模式下停庫備份與恢復模式
- Oracle 歸檔與非歸檔模式的更改Oracle模式
- 非歸檔模式恢復資料庫模式資料庫
- 儲存遷移方案
- Oracle歸檔模式與非歸檔模式設定Oracle模式
- 恢復之非歸檔模式下的恢復模式
- 儲存所有歷史提交資料下遷移git倉庫Git
- rman 非歸檔模式下open庫備份與mount恢復模式
- 使用RMAN遷移單庫到RAC
- 非歸檔模式下非當前日誌檔案的丟失的恢復模式
- 非歸檔模式下的資料檔案路徑修改模式
- asm 儲存線上遷移ASM
- RAC歸檔配置方案：使用NFS共享儲存儲存歸檔檔案NFS
- 資料儲存(歸檔解檔，沙河儲存)
- 遷移資料庫檔案到ASM資料庫ASM
- oracle10g 歸檔模式和非歸檔模式的轉換Oracle模式
- Oracle 歸檔和非歸檔模式之間的切換Oracle模式
- 修改歸檔模式的通用步驟(非RAC 資料庫)模式資料庫
- geoserver資料儲存遷移Server
- HA(FAILSAFE)模式下ORACLE 10g 設為歸檔模式AI模式Oracle 10g
- 遷移檔案系統管理下的db到asm下ASM
- 簡單檔案的上傳與儲存
- 【ARCHIVE】單機環境修改資料庫為歸檔模式Hive資料庫模式
- rman在歸檔與非歸檔時備份資料庫的簡單示例資料庫
- aix 下 oracle 11.2 rac 在同一臺機器上把資料遷移到新儲存AIOracle
- 小儲存容量手機有救了！Android 新歸檔功能可釋放 60% 儲存空間Android