非歸檔模式下遷移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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rac歸檔模式/非歸檔模式下檔案建至本地儲存修復方法-ORA-01157模式
- 儲存所有歷史提交資料下遷移git倉庫Git
- 資料儲存(歸檔解檔，沙河儲存)
- Solaris 10下遷移10G RAC (六)
- Solaris 10下遷移10G RAC (八)
- Solaris 10下遷移10G RAC (四)
- Solaris 10下遷移10G RAC (二)
- Solaris 10下遷移10G RAC (七)
- Solaris 10下遷移10G RAC (三)
- Solaris 10下遷移10G RAC (一)
- Solaris 10下遷移10G RAC (五)
- geoserver資料儲存遷移Server
- 小儲存容量手機有救了！Android 新歸檔功能可釋放 60% 儲存空間Android
- wsl遷移儲存位置(轉載)
- 利用rman copy的方法實現儲存上裸裝置資料檔案的遷移ITPUB
- 遷移資料庫的檔案到不同路徑(轉)資料庫
- IPFS的檔案儲存模式模式
- 簡單2步就能將大量檔案歸類儲存
- oracle RAC 更換儲存遷移資料Oracle
- 大型系統儲存層遷移實踐
- 對歸檔模式下CLEAR 未歸檔日誌後恢復資料庫的一點看法模式資料庫
- openstack下熱遷移機制
- Kubernetes 遷移節點 Kubelet 資料儲存目錄
- Elasticsearch 基於物件儲存使用快照資料遷移Elasticsearch物件
- Kubernetes怎麼進行NFS動態儲存遷移NFS
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫歸檔模式的切換ELOracle資料庫模式
- 【VMware vSphere】沒有共享儲存的ESXi主機之間如何共享本地儲存上的ISO檔案。
- 簡單介紹nacos單機本地配置檔案儲存位置方式
- 按檔案或扇區方式遷移資料到另一儲存，完成恢復工作
- 圖資料庫設計實踐 | 儲存服務的負載均衡和資料遷移資料庫負載
- MacOS系統Docker預設儲存路徑遷移方法MacDocker
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- 從 SQL Server 到 MySQL（二）：線上遷移，空中換髮動機ServerMySql
- CQRS時是否使用儲存庫模式嗎？模式
- 金倉資料庫資料遷移實戰：從MySQL到KES的順利遷移資料庫MySql
- 聚焦資料時代新儲存需求，浪潮儲存的新儲存之道
- 儲存文字到一個檔案裡
- 塊儲存 檔案儲存 物件儲存物件