資料庫異機冷備恢復

具體為以下幾個步驟：
一：在新的機器安裝系統，建立oralce使用者及目錄和賦相應的許可權，配置目的機器的oracle安裝路徑跟原機器一致
二：關閉原資料庫，拷貝資料庫所有目錄下的檔案到另外一臺機器的對應目錄下
三：sqlplus /nolog startup啟動資料庫

###################OS Installing###################################
1:在linux5u2裡安裝虛擬機器，在虛擬機器裡安裝red hat 5u2 64bit作業系統
  [oracle@test-vm-5u2-64 ~]$ cat /proc/cpuinfo | grep processor
  processor       : 0
  processor       : 1
  [oracle@test-vm-5u2-64 ~]$ cat /proc/meminfo | awk -F: '/MemTotal/ {print $2/1024/1024}'
  4.83774
  [oracle@test-vm-5u2-64 ~]$ getconf LONG_BIT
  64
[oracle@test-vm-5u2-64 ~]$
2:安裝完畢後掛5塊盤，及格式化，mount,在/etc/rc.local中寫入，具體步驟
  如下：
  建立目錄：
  mkdir /oracle
  mkdir /oraredo
  mkdir /oraundo
  mkdir /oradataa
  mkdir /oradatab
  格式化分割槽：
  mkfs.ext3 /dev/sdb
  mkfs.ext3 /dev/sdc
  mkfs.ext3 /dev/sdd
  mkfs.ext3 /dev/sde
  mkfs.ext3 /dev/sdf
  掛載裝置：
  mount /dev/sdb /oracle
  mount /dev/sdc /oraredo
  mount /dev/sdd /oradataa
  mount /dev/sde /oraundo
  mount /dev/sdf /oradatab

[oracle@test-vm-5u2-64 ~]$ vi /etc/rc.local
#!/bin/sh
#
# This script. will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style. init stuff.

touch /var/lock/subsys/local

mount /dev/sdb /oraredo
mount /dev/sdc /oraundo
mount /dev/sdd /oradataa
mount /dev/sde /oradatab
mount /dev/sdf /oracle

################OS Parameter Set######################################
echo 'set kernel parameter'

 echo "#   settings for ora10g"  >>  /etc/sysctl.conf
 echo "fs.file-max=327679"  >>  /etc/sysctl.conf
 echo "kernel.msgmni=2878"  >>  /etc/sysctl.conf
 echo "kernel.msgmax=8192"  >>  /etc/sysctl.conf
 echo "kernel.msgmnb=65536"  >>  /etc/sysctl.conf
 echo "kernel.sem=250 32000 100 142"  >>  /etc/sysctl.conf
 echo "kernel.shmmni=4096"  >>  /etc/sysctl.conf
 echo "kernel.shmall=3279547"  >>  /etc/sysctl.conf
 echo "#   set to a value half the size of physical memory"  >>  /etc/sysctl.conf
 echo "kernel.shmmax=2147483648"  >>  /etc/sysctl.conf
 echo "kernel.sysrq=1"  >>  /etc/sysctl.conf
 echo "#   rmem_max can be tuned based on workload to balance performance vs lowmem usage"  >>  /etc/sysctl.conf
 echo "net.core.rmem_default=262144"  >>  /etc/sysctl.conf
 echo "net.core.rmem_max=2097152"  >>  /etc/sysctl.conf
 echo "net.core.wmem_default=262144"  >>  /etc/sysctl.conf
 echo "net.core.wmem_max=262144"  >>  /etc/sysctl.conf
 echo "fs.aio-max-nr=3145728"  >>  /etc/sysctl.conf
 echo "vm.lower_zone_protection=100"  >>  /etc/sysctl.conf
 echo "net.ipv4.ip_local_port_range=1024 65000"  >>  /etc/sysctl.conf
 sysctl -p

  echo "    # depending on size of db, these may need to be larger" >>  /etc/security/limits.conf
 echo "oracle  soft    nofile  131072" >>  /etc/security/limits.conf
 echo "oracle  hard    nofile  131072" >>  /etc/security/limits.conf
 echo "oracle  soft    nproc   131072" >>  /etc/security/limits.conf
 echo "oracle  hard    nproc   131072" >>  /etc/security/limits.conf
 echo "oracle  soft    core    unlimited" >>  /etc/security/limits.conf
 echo "oracle  hard    core    unlimited" >>  /etc/security/limits.conf
 echo "    # set to allow oracle to use hugepages" >>  /etc/security/limits.conf
 echo "oracle  soft    memlock 50000000" >>  /etc/security/limits.conf
 echo "oracle  hard    memlock 50000000" >>  /etc/security/limits.conf
 
 echo 'set kernel parameter success!'

echo 'all the env setting success!'

###################OS裁剪##########################################

chkconfig --level 3 acpid off
chkconfig --level 3 anacron off
chkconfig --level 3 apmd off
chkconfig --level 3 canna off
chkconfig --level 3 mdmonitor off
chkconfig --level 3 openibd off
chkconfig --level 3 xinetd off
chkconfig --level 3 sendmail off
chkconfig --level 3 rpcgssd off
chkconfig --level 3 cups off
chkconfig --level 3 rawdevices off
chkconfig --level 3 messagebus off
chkconfig --level 3 atd off
chkconfig --level 3 arptables_jf off
chkconfig --level 3 iiim off
chkconfig --level 3 gpm off
chkconfig --level 3 autofs off
chkconfig --level 3 cpuspeed off
chkconfig --level 3 haldaemon off
chkconfig --level 3 nfslock off
chkconfig --level 3 portmap off
chkconfig --level 3 cups-config-daemon off
chkconfig --level 3 lm_sensors off
chkconfig --level 3 rhnsd off
chkconfig --level 3 xfs off
chkconfig --level 3 netfs off
chkconfig --level 3 iptables off
chkconfig --level 3 smartd off
chkconfig --level 3 ip6tables off
chkconfig --level 3 isdn off
chkconfig --level 3 rpcidmapd off
chkconfig --level 3 microcode_ctloff
chkconfig --level 3 microcode_ctl off
chkconfig --level 3 pcmcia off
chkconfig --level 3 hpoj off
chkconfig --level 3 network on
chkconfig --level 3 syslog on
chkconfig --level 3 sshd on
chkconfig --level 3 netfs on
chkconfig --level 3 sysstat on
chkconfig --level 3 irqbalance on
chkconfig --level 3 kudzu on
chkconfig --level 3 messagebus on
chkconfig --level 3 ntpd on
chkconfig --level 3 crond on

###################CP oracle files#################################
cd /oracle
scp oracle@192.168.0.123:/oracle/* .
cd /oraredo
scp oracle@192.168.0.123:/oraredo/* .
cd /oraundo
scp oracle@192.168.0.123:/oraundo/* .
cd /oradataa
scp oracle@192.168.0.123:/oradataa/* .
cd /oradatab
scp oracle@192.168.0.123:/oradatab/* .

###################Create User ADD Privs Setting###################
echo 'create oracle user and group'

        groupadd dba
        groupadd oinstall
        useradd -g oinstall -G dba oracle
        echo 'please set oracle default password:'
        passwd oracle
       
echo 'create oracle directory '

        chown oracle.oinstall /oracle
        chown -R oracle.oinstall /oracle/ora10
        chmod 755 /oracle

echo 'setting directory owner'

 chown -R oracle.oinstall /oracle
 chown -R oracle.oinstall /oradataa
 chown -R oracle.oinstall /oraredo
 chown -R oracle.oinstall /oraundo
 chown -R oracle.oinstall /oradatab

################Set OraEnv############################################
# oracle 10g
export ORACLE_BASE=/oracle/ora10
export ORACLE_HOME=/oracle/ora10/product
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_OWNER=oracle
export ORACLE_SID=ora10g

unset USERNAME
umask 022

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

#
# change this NLS settings to suit your country:
# example:
# german_germany.we8iso8859p15, american_america.we8iso8859p2 etc.
#export NLS_LANG='american_america.ZHS16GBK'
export NLS_LANG='american_america.AL32UTF8'

 

# User specific environment and startup programs

PATH=$PATH:/usr/sbin:/sbin
export PATH

[root@test-vm-5u2-64 ~]# su - oracle
[oracle@test-vm-5u2-64 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 11 18:02:53 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2084264 bytes
Variable Size             486539864 bytes
Database Buffers         1073741824 bytes
Redo Buffers               14692352 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/oradatab/datab01.dbf'

SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01b.red'
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01a.red'

檢視alert日誌
[oracle@test-vm-5u2-64 bdump]$ tail -f 100 alert_ora10g.log
tail: cannot open `100' for reading: No such file or directory
==> alert_ora10g.log <==
 parallel recovery started with 2 processes
Tue Sep 11 18:03:26 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_22889.trc:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01b.red'
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01a.red'
Tue Sep 11 18:03:27 2012
Media Recovery failed with error 322
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 5  ...

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 format a45
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                        IS_
---------- ------- ------- --------------------------------------------- ---
         1         ONLINE  /oraredo/redo01a.red                          NO
         2         ONLINE  /oraredo/redo02a.red                          NO
         3         ONLINE  /oraredo/redo03a.red                          NO
         1         ONLINE  /oraredo/redo01b.red                          NO
         2         ONLINE  /oraredo/redo02b.red                          NO
         3         ONLINE  /oraredo/redo03b.red                          NO

6 rows selected.

SQL> set linesize 400
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1        251  209715200          2 NO  CURRENT                3236486 29-AUG-12
         3          1        250  209715200          2 YES INACTIVE               3136454 16-JUL-12
         2          1        249  209715200          2 YES INACTIVE               3072395 29-JUN-11
SQL> recover database until time '2012-09-09 00:00:00';
ORA-00283: recovery session canceled due to errors
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01b.red'
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/oraredo/redo01a.red'

SQL> recover database until cancel;
ORA-00279: change 3272052 generated at 09/07/2011 17:21:53 needed for thread 1
ORA-00289: suggestion : /oracle/ora10/oradata/ora10g/archive/1_251_771101551.dbf
ORA-00280: change 3272052 for thread 1 is in sequence #251

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/ora10/oradata/ora10g/archive/1_251_771101551.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/oracle/ora10/oradata/ora10g/archive/1_251_771101551.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/ora10/oradata/ora10g/system01.dbf'

SQL> recover database until cancel;
ORA-00279: change 3272052 generated at 09/07/2011 17:21:53 needed for thread 1
ORA-00289: suggestion : /oracle/ora10/oradata/ora10g/archive/1_251_771101551.dbf
ORA-00280: change 3272052 for thread 1 is in sequence #251

檢視歸檔日誌，看看是否有1_251_771101551.dbf檔案
[oracle@test-vm-5u2-64 archive]$ ll
total 407176
-rwxr-xr-x 1 oracle oinstall 205882880 Sep 11 16:39 1_249_771101551.dbf
-rwxr-xr-x 1 oracle oinstall 209581568 Sep 11 16:39 1_250_771101551.dbf
-rwxr-xr-x 1 oracle oinstall   1056768 Sep 11 16:39 block.dbf
[oracle@test-vm-5u2-64 archive]$
我們可以看到沒有這個檔案，這個檔案明顯比已歸檔的檔案號大，據估計肯定在
redo重做日誌裡，所以在下面我們用重做日誌恢復

Specify log: {=suggested | filename | AUTO | CANCEL}
/oraredo/redo01a.red 
--在這裡輸入重做日誌檔案，因為在上面我們已經查了redo01a.red是當前組檔案
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oracle/ora10/oradata/ora10g/system01.dbf'

ORA-01112: media recovery not started

在這裡我們看到，已經恢復成功！
SQL>  recover database until cancel;
Media recovery complete.

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 * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          1  209715200          2 NO  CURRENT                3275931 11-SEP-12
         2          1          0  209715200          2 YES UNUSED                       0
         3          1          0  209715200          2 YES UNUSED                       0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          1  209715200          2 YES ACTIVE                 3275931 11-SEP-12
         2          1          2  209715200          2 NO  CURRENT                3276388 11-SEP-12
         3          1          0  209715200          2 YES UNUSED                       0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
         1          1          1  209715200          2 YES ACTIVE                 3275931 11-SEP-12
         2          1          2  209715200          2 YES ACTIVE                 3276388 11-SEP-12
         3          1          3  209715200          2 NO  CURRENT                3276394 11-SEP-12

SQL>

[oracle@test-vm-5u2-64 archive]$ ll
total 524064
-rw-r----- 1 oracle oinstall    238080 Sep 11 18:26 1_1_793736516.dbf
-rwxr-xr-x 1 oracle oinstall 205882880 Sep 11 16:39 1_249_771101551.dbf
-rwxr-xr-x 1 oracle oinstall 209581568 Sep 11 16:39 1_250_771101551.dbf
-rw-r----- 1 oracle oinstall 119318528 Sep 11 18:21 1_251_771101551.dbf
-rw-r----- 1 oracle oinstall      1024 Sep 11 18:26 1_2_793736516.dbf
-rwxr-xr-x 1 oracle oinstall   1056768 Sep 11 16:39 block.dbf
[oracle@test-vm-5u2-64 archive]$
檢視重做日誌已經成功歸檔

至此，整個資料庫就已經成功啟動，下來開發人員就可以正常使用了