拷貝linux下冷備份db到win下測試過程

拷貝linux下冷備份db到win下測試過程[@more@]

講課的時候經常會有人問到是否可以把unix/linux下的資料庫直接
拷貝到win下用，或者相反，從未測試過，隱約的感覺到應該不可以，
今天測試了一下，沒有出現問題：
測試過程是把linux下的db shutdown之後直接拷貝到win下發現沒有問題：
大致過程：
--===================================
1、首先確認linux下和win下v$transportable_platform中的ENDIAN_FORMAT都
相同：這裡都是Little
SQL> select *from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------- ------------------------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ----------------------------------- ------------------------------
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

17 rows selected.
--=======================================
linux版本：
[oracle@xys udump]$ uname -a
Linux xys 2.4.21-4.EL #1 Fri Oct 3 18:13:58 EDT 2003 i686 i686 i386 GNU/Linux
[oracle@xys udump]$
--=======================================
oracle版本：都是10.2.0.1
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
--===========================================
檢查一下linux下db_name=test oracle_sid=orcl的庫，確認其可以正常open，同時
查詢dbf,ctl,redo的位置:
[oracle@xys oracle]$ export ORACLE_SID=ORCL
[oracle@xys oracle]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 1 20:08:59 2008

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 file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/orcl/system01.dbf
/opt/app/oracle/oradata/orcl/undotbs01.dbf
/opt/app/oracle/oradata/orcl/sysaux01.dbf
/opt/app/oracle/oradata/orcl/users01.dbf
/opt/app/oracle/oradata/orcl/trans.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/orcl/control01.ctl
/opt/app/oracle/oradata/orcl/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/orcl/redo01.log
/opt/app/oracle/oradata/orcl/redo02.log
/opt/app/oracle/oradata/orcl/redo03.log

SQL>
--==================================
建立目錄結構，從linux下get檔案到win下，包括引數檔案，資料檔案，
控制檔案，聯機日誌檔案以及重建controlfile的指令碼，其實只需要引數檔案
、資料檔案以及重建控制檔案的指令碼即可，因為需要重建controlfile，所以原來的
controlfile和redo其實都沒用
E:>cd E:oracleproduct10.2.0oradataorcl

E:oracleproduct10.2.0oradataorcl>ftp 192.168.0.253
Connected to 192.168.0.253.
220 (vsFTPd 1.2.0)
User (192.168.0.253:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> pwd
257 "/home/oracle"
ftp> cd /opt/app/oracle/oradata/orcl
250 Directory successfully changed.
ftp> pwd
257 "/opt/app/oracle/oradata/orcl"
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
bak
control01.ctl
control02.ctl
dbf.sql
raw_convert_fs.sql
redo01.log
redo02.log
redo03.log
sysaux01.dbf
system01.dbf
trans.dbf
undotbs01.dbf
users01.dbf
226 Directory send OK.
ftp: 收到 167 位元組，用時 0.02Seconds 10.44Kbytes/sec.
ftp> type
Using ascii mode to transfer files.
ftp> bin
200 Switching to Binary mode.
ftp> mget control*
200 Switching to Binary mode.
mget control01.ctl? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for control01.ctl (7045120 bytes).
226 File send OK.
ftp: 收到 7045120 位元組，用時 1.17Seconds 6011.19Kbytes/sec.
mget control02.ctl? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for control02.ctl (7045120 bytes).
226 File send OK.
ftp: 收到 7045120 位元組，用時 0.77Seconds 9209.31Kbytes/sec.
ftp> mget *.log
200 Switching to Binary mode.
mget redo01.log? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for redo01.log (10486272 bytes).
226 File send OK.
ftp: 收到 10486272 位元組，用時 1.13Seconds 9321.13Kbytes/sec.
mget redo02.log? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for redo02.log (10486272 bytes).
226 File send OK.
ftp: 收到 10486272 位元組，用時 0.41Seconds 25828.26Kbytes/sec.
mget redo03.log? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for redo03.log (10486272 bytes).
226 File send OK.
ftp: 收到 10486272 位元組，用時 1.61Seconds 6513.21Kbytes/sec.
ftp> mget *.dbf
200 Switching to Binary mode.
mget sysaux01.dbf? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for sysaux01.dbf (125837312 bytes).
226 File send OK.
ftp: 收到 125837312 位元組，用時 18.48Seconds 6807.90Kbytes/sec.
mget system01.dbf? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for system01.dbf (314580992 bytes).
226 File send OK.
ftp: 收到 314580992 位元組，用時 56.33Seconds 5584.81Kbytes/sec.
mget trans.dbf?
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for trans.dbf (5251072 bytes).
226 File send OK.
ftp: 收到 5251072 位元組，用時 1.08Seconds 4871.12Kbytes/sec.
mget undotbs01.dbf? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for undotbs01.dbf (157294592 bytes).
226 File send OK.
ftp: 收到 157294592 位元組，用時 29.47Seconds 5337.63Kbytes/sec.
mget users01.dbf? y
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for users01.dbf (5251072 bytes).
226 File send OK.
ftp: 收到 5251072 位元組，用時 0.56Seconds 9343.54Kbytes/sec.
ftp> pwd
257 "/opt/app/oracle/oradata/orcl"
ftp> cd /opt/app/oracle/product/10.2.0/db_1/dbs
250 Directory successfully changed.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
hc_ORCL.dat
hc_TEST.dat
hc_lvmdb.dat
hc_orcl.dat
hc_test.dat
init.ora
initORCL.ora
initTEST.ora
initTEST.ora.fs
initTEST.ora.raw
initdw.ora
lkLVMDB
lkORCL
lkRAWDB
lkTEST
lkTEST1
lkTESTDB
orapwORCL
orapwTEST
snapcf_TEST.f
spfileORCL.ora
spfileTEST.ora
sqlnet.log
226 Directory send OK.
ftp: 收到 285 位元組，用時 0.02Seconds 17.81Kbytes/sec.
ftp> type
Using binary mode to transfer files.
ftp> ascii
200 Switching to ASCII mode.
ftp> get initORCL.ora
200 PORT command successful. Consider using PASV.
150 Opening BINARY mode data connection for initORCL.ora (960 bytes).
226 File send OK.
ftp: 收到 960 位元組，用時 0.03Seconds 30.97Kbytes/sec.
ftp>
--==============================
建立引數檔案中需要的目錄結構，修改引數檔案，建立例項
啟動例項，修改controlfile的指令碼，重建控制檔案
--====================================
SQL> CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:oracleproduct10.2.0oradataorclredo01.log' SIZE 10M,
9 GROUP 2 'E:oracleproduct10.2.0oradataorclredo02.log' SIZE 10M,
10 GROUP 3 'E:oracleproduct10.2.0oradataorclredo03.log' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:oracleproduct10.2.0oradataorclsystem01.dbf',
14 'E:oracleproduct10.2.0oradataorclundotbs01.dbf',
15 'E:oracleproduct10.2.0oradataorclsysaux01.dbf',
16 'E:oracleproduct10.2.0oradataorclusers01.dbf',
17 'E:oracleproduct10.2.0oradataorcltrans.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01158: database already mounted

--上面建立controlfile出錯是由於系統存在一個db_name=test的db，暫時shutdown之後
再建即可
SQL> /

Control file created.

SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATAORCLTRANS.DBF
E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF
E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF
E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF
E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL01.CTL
E:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL02.CTL

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------

E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG
E:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG
E:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG

SQL> create table t(id int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID
----------
1

SQL>
--=======================================
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1009941/，如需轉載，請註明出處，否則將追究法律責任。