拷贝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

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博客

拷贝linux下冷备份db到win下测试过程

最新文章