数据库冷备份及RMAN简单使用

最新推荐文章于 2024-04-23 10:32:10 发布

weixin_34113237

最新推荐文章于 2024-04-23 10:32:10 发布

阅读量100

点赞数

文章标签：数据库 java 操作系统

原文链接：http://blog.51cto.com/cswggod/1089518

版权

1.jsp调用oracle

1.安装tomcat
[root@h1 ROOT]# cd /var/App/
[root@h1 App]# unzip apache-tomcat-6.0.14.zip

2.复制ojdbc.jar

[root@h1 ~]# cd /app/oracle/product/11.2.0/dbhome_2/jdbc/lib
[root@h1 lib]# find ojdbc6.jar
ojdbc6.jar
[root@h1 lib]# cp ojdbc6.jar /var/App/apache-tomcat-6.0.14/lib/
[root@h1 lib]# cd /var/App/apache-tomcat-6.0.14/lib/
[root@h1 lib]# ls
annotations-api.jar el-api.jar      ojdbc6.jar          tomcat-i18n-fr.jar
catalina-ant.jar     jasper-el.jar   servlet-api.jar     tomcat-i18n-ja.jar
catalina-ha.jar      jasper.jar      tomcat-coyote.jar
catalina.jar         jasper-jdt.jar tomcat-dbcp.jar
catalina-tribes.jar jsp-api.jar     tomcat-i18n-es.jar

[root@h1 webapps]# cd /var/App/apache-tomcat-6.0.14/webapps/ROOT
[root@h1 ROOT]# vi 1.jsp

<%
<%@page pageEncoding='gbk'%>

out.println("Hello,Everybody");

"1.jsp" [New] 17L, 81C written

3.安装jdk，修改/etc/profile
[root@h1 ROOT]# cd /var/App/
[root@h1 APP]# ./jdk-6u27-linux-x64.bin
[root@h1 App]#vi /etc/profile

export JAVA_HOME=/var/App/jdk1.6.0_27
export PATH=$JAVA_HOME/bin:.:$PATH
export CLASSPATH=$JAVA_HOME/lib:.

"/etc/profile" 84L, 1905C written
[root@h1 App]#source /stc/profile

------------------------
windwos 下修改系统环境变量
用户环境变量添加JAVA_HOME
系统环境变量添加CLASSPATH=%JAVA_HME%\lib
系统环境变量添加PATH=.;%JAVA_HME%\bin;
4.启动tomcat

[root@h1 bin]# cd /var/App/apache-tomcat-6.0.14/bin
[root@h1 bin]# ls
bootstrap.jar catalina-tasks.xml digest.bat   service.bat       shutdown.bat startup.sh    tomcat-juli.jar       tool-wrapper.sh
catalina.bat   commons-daemon.jar digest.sh    setclasspath.bat shutdown.sh   tomcat6.exe   tomcat-native.tar.gz version.bat
catalina.sh    cpappend.bat        jsvc.tar.gz setclasspath.sh   startup.bat   tomcat6w.exe tool-wrapper.bat      version.sh
[root@h1 bin]# ./startup.sh
-bash: ./startup.sh: Permission denied

[root@h1 bin]# ./startup.sh
Using CATALINA_BASE:   /var/App/apache-tomcat-6.0.14
Using CATALINA_HOME:   /var/App/apache-tomcat-6.0.14
Using CATALINA_TMPDIR: /var/App/apache-tomcat-6.0.14/temp
Using JRE_HOME:       /var/App/jdk1.6.0_27

5.编写运行
[root@h1 ROOT]# cd /var/App/apache-tomcat-6.0.14/webapps/ROOT
[root@h1 ROOT]# vi
[root@h1 ROOT]# vi db.jsp

<%@ page contentType="text/html;charset=gbk" import="java.sql.*"%>

<%
        String url = "jdbc:oracle:thin:@192.168.5.130:1522:BITC";
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection conn = DriverManager.getConnection(url,"u01","abc");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select id ,name from t1");
        while(rs.next()){
                out.println(rs.getInt(1)+"\t"+rs.getString(2)+"<br>");
        }
%>

~
"db.jsp" 12L, 432C written
[root@h1 ROOT]#

2.冷·备份

前提：有两台服务器,一台是oracle数据库服务器SERVER130 ip=192.168.5.130，另一台是新机 Server128=192.168.5.128

现在冷备份数据库将Server130中的TEST迁移至Server128

#-------------------其中SERVER130 数据库重命名过，原名ORCL，所以按照数据库安装时生成的/app/oracle/admin/orcl/pfile/

init.ora.[num]中db_name=安装数据库时的ORCL；迁移次文件，生成的数据库是ORCL

1.SERVER1 上BITC实例关闭，监听关闭

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 12 14:42:08 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter instance;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string bitc

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@h1 ~]$ lsnrctl stop bitc

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-NOV-2012 14:44:23

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.5.130)(PORT=1522)))
The command completed successfully
[oracle@h1 ~]$

2.转移参数文件
SERVER130上下载参数文件

[oracle@h1 oracle]$ cd /app/oracle/admin/orcl/pfile
[oracle@h1 pfile]$ ls
init.ora.103201271612

[oracle@h1 pfile]$ sz *
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring init.ora.103201271612...
100% 1 KB 1 KB/s 00:00:01 0 Errors

-----------------------------------------------

SERVER128上传参数

[root@instructor app]# mkdir -p /app/oracle/admin/orcl/pfile
[root@instructor app]# mkdir -p /app/oracle/admin/orcl/adump
[root@instructor app]# mkdir -p /app/oracle/admin/orcl/dpdump

------安装lrzsz
[root@instructor app]# yum install lrzsz

[root@instructor pfile]# cd /app/oracle/admin/orcl/pfile
[root@instructor pfile]# rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring init.ora.103201271612...
100% 1 KB 1 KB/s 00:00:01 0 Errors

[root@instructor pfile]# ls
init.ora.103201271612

[root@instructor pfile]# more init.ora.103201271612

查看得知

* db_name=ORCL

*control_files=("/app/oracle/oradata/orcl/control01.ctl", "/app/oracle/flash_recovery_area/orcl/control02.ctl"
)
db_recovery_file_dest=/app/oracle/flash_recovery_area
db_recovery_file_dest_size=260612736

*local_listener=LISTENER_ORCL

*processes=300

*audit_file_dest=/app/oracle/admin/orcl/adump

#因此：数据库db_name=orcl

-----------------------------------------------

[root@instructor app]# mkdir -p /app/oracle/flash_recovery_area

--------------------------------------

[root@instructor pfile]# mkdir -p /app/oracle/oradata/orcl

[root@instructor pfile]# cp init.ora.103201271612 /app/oracle/product/11.2.0/dbhome_1/dbs/initbitc.ora
[root@instructor pfile]# cd /app/oracle/product/11.2.0/dbhome_1/dbs/
[root@instructor dbs]# ls
hc_DBUA0.dat hc_orcl.dat initbitc.ora init.ora
[root@instructor dbs]#

3.转移dbf和ctl文件
在SERVER130上

[oracle@h1 dbs]$ cd /app/oracle/oradata/
[oracle@h1 oradata]$ ls
orcl test
[oracle@h1 oradata]$ cd /app/oracle/oradata/orcl
[oracle@h1 orcl]$ ls
control01.ctl redo01.log redo03.log    system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf
[oracle@h1 orcl]$ sz *
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring control01.ctl...
100%    9520 KB 9520 KB/s 00:00:01       0 Errors
Transferring example01.dbf...
100% 102408 KB 4876 KB/s 00:00:21       0 Errors
Transferring redo01.log...
100%   51200 KB 3200 KB/s 00:00:16       0 Errors
Transferring redo02.log...
100%   51200 KB 2844 KB/s 00:00:18       0 Errors
Transferring redo03.log...
100%   51200 KB 4266 KB/s 00:00:12       0 Errors
Transferring sysaux01.dbf...
100% 563208 KB 3181 KB/s 00:02:57       0 Errors
Transferring system01.dbf...
100% 696328 KB 9162 KB/s 00:01:16       0 Errors
Transferring temp01.dbf...
100%   29704 KB 9901 KB/s 00:00:03       0 Errors
Transferring undotbs01.dbf...
100% 102408 KB 10240 KB/s 00:00:10       0 Errors
Transferring users01.dbf...
100%    5128 KB 5128 KB/s 00:00:01       0 Errors

-----------------------------------------------------------------
在SERVER128上
[root@instructor dbs]# mkdir -p /app/oracle/oradata/orcl

[root@instructor dbs]# cd /app/oracle/oradata/orcl

[root@instructor orcl]# rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.

?[root@instructor orcl]# rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring control01.ctl...
100%    9520 KB 3173 KB/s 00:00:03       0 Errors
Transferring example01.dbf...
100% 102408 KB 2438 KB/s 00:00:42       0 Errors
Transferring redo01.log...
100%   51200 KB 2226 KB/s 00:00:23       0 Errors
Transferring redo02.log...
100%   51200 KB 1651 KB/s 00:00:31       0 Errors
Transferring redo03.log...
100%   51200 KB 4266 KB/s 00:00:12       0 Errors
Transferring sysaux01.dbf...
100% 563208 KB 4365 KB/s 00:02:09       0 Errors
Transferring system01.dbf...
100% 696328 KB 11231 KB/s 00:01:02       0 Errors
Transferring temp01.dbf...
100%   29704 KB 9901 KB/s 00:00:03       0 Errorss
Transferring undotbs01.dbf...
100% 102408 KB 10240 KB/s 00:00:10       0 Errors
Transferring users01.dbf...
100%    5128 KB 5128 KB/s 00:00:01       0 Errors

4.SERVER128配置用户.bash_profile
[root@instructor app]# su oracle
[oracle@instructor bin]$ cd /home/oracle/
[oracle@instructor ~]$ vi .bash_profile

export PATH
export ORACLE_BASE=/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORA_CRS_HOME=$ORACLE_BASE/crs
export ORACLE_PATH=$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdms/admin
export ORACLE_SID=orcl
export PATH=${PATH}:$ORACLE_HOME/bin:ORA_CRS_HOME/bin:$HOME/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/local/lib:$ORACLE_HOME/oracm/bin:$ORACLE_HOME/lib
export LIBPATH=$LIBPATH:$ORA_CRS_HOME/LIB:$ORACLE_HOME/lib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/jlib:$ORACLE_HOME/network/jlib:$ORACLE_HOME/JRE
export THREADS_FLAG=native

export LD_BIND_NOW=1
[oracle@instructor ~]$ source .bash_profile
[oracle@instructor ~]$ echo $ORACLE_HOME
/app/oracle/product/11.2.0/dbhome_1
[oracle@instructor ~]$ cd $ORACLE_HOME
[oracle@instructor dbhome_1]$

5.SERVER128用户ORACLE配置监听和namespace

[oracle@instructor dbhome_1]$ export DISPLAY=192.168.5.1:0.0
[oracle@instructor dbhome_1]$ netmgr

(配监听)

[oracle@instructor dbhome_1]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-NOV-2012 23:44:59

Starting /app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /app/oracle/diag/tnslsnr/instructor/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.128)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-NOV-2012 23:45:02
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/instructor/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.128)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#-------------------监听开启成功

（配置namespace）

6.修改有oracle目录相关的权限和所用者

[oracle@instructor dbhome_1]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 23 23:56:35 2012

ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9925

Enter user-name:

[oracle@instructor dbhome_1]$ su root
Password:
[root@instructor dbhome_1]# cd /app
[root@instructor app]# chown -Rhf oracle:oinstall /app/oracle
[root@instructor app]# su oracle
[oracle@instructor app]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 00:03:17 2012

Connected to an idle instance.

SQL> startup nomount pfile=/app/oracle/admin/orcl/pfile/init.ora.103201271612
ORACLE instance started.

Total System Global Area 263049216 bytes
Fixed Size 2212448 bytes
Variable Size 222301600 bytes
Database Buffers 33554432 bytes
Redo Buffers 4980736 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

SQL> show parameter background_dump_dest;#查看alertlog文件位置

#-------------------------以下是oracle缺失了一个控制文件

NAME TYPE VALUE
----------------------------------- ----------- ------------------------------
background_dump_dest string /app/oracle/diag/rdbms/bitc/or
cl/trace
SQL> exit

-----------------------------------

[root@instructor orcl]# cd /app/oracle/diag/rdbms/bitc/orcl/trace

在SERVER130上

[oracle@h1 ~]$ cd /app/oracle/flash_recovery_area/orcl/
[oracle@h1 orcl]$ ls
control02.ctl

[oracle@h1 orcl]$ sz *
rz
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring control02.ctl...
100% 9520 KB 9520 KB/s 00:00:01 0 Errors

在SERVER128上

[root@instructor ~]# cd /app/oracle/flash_recovery_area/orcl/
[root@instructor orcl]# ls
[root@instructor orcl]# rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring control02.ctl...
100% 9520 KB 9520 KB/s 00:00:01 0 Errors

---------------------------------------

[oracle@instructor ~]$ sqlplus "/as SYSDBA"

SQL> startup
ORACLE instance started.

Total System Global Area 263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             226495904 bytes
Database Buffers           29360128 bytes
Redo Buffers                4980736 bytes
Database mounted.
Database opened.

以下是DB_NAME在参数文件和Control File不一致情况：

[root@instructor orcl]#
--------------------------------------------------------------------------

[oracle@instructor ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 00:53:22 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'ORCL' in control file is not 'BITC'

#------------------查看 alert log或查看oracle错误代码信息

SQL> host oerr ora 1103
01103, 00000, "database name '%s' in control file is not '%s'"
// *Cause: The database name in the control file does not match your
// database name.
// *Action: Either find the correct control file or change your database name.

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string BITC

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@instructor ~]$ cd //app/oracle/admin/orcl/
[oracle@instructor orcl]$ cd /app/oracle/admin/orcl/
[oracle@instructor orcl]$ cd /app/oracle/admin/orcl/pfile/
[oracle@instructor pfile]$ ls
init.ora.103201271612
[oracle@instructor pfile]$ vi init.ora.103201271612

db_name=orcl

"init.ora.103201271612" 62L, 1906C written
[oracle@instructor pfile]$ sqlplus "/as SYSDBA"

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup mount pfile=/app/oracle/admin/orcl/pfile/init.ora.103201271612;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 263049216 bytes
Fixed Size                  2212448 bytes
Variable Size             234884512 bytes
Database Buffers           20971520 bytes
Redo Buffers                4980736 bytes
SQL> alter system set db_name=bitc scope=spfile;

System altered.

#-----------------在nomount下修改数据库db_name,数据库重启spfile生效

3.备份概念

差异备份:备份自上一次完全备份之后有变化的数据

增量备份:备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数据

RMAN备份:

用恢复管理器备份数据文件、控制文件、归档日志和SPFILE的方法。

RMAN是由目标数据库的服务器进程执行备份操作。

因为RMAN备份由目标数据库的服务器进程来完成，所以当使用RMAN执行备份操作时，目标数据库必须处于MOUNT状态或OPEN状态。

-------------------------------(1)查看归档模式

[oracle@h1 ~]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 13 23:04:08 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Current log sequence           20
SQL>

--------------#归档模式未启动

-------------------------------(2)启动归档模式

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

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

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20
SQL>

-------------------------------(3)建立目录

[oracle@h1 ~]$ cd /home/oracle
[oracle@h1 ~]$ mkdir dbbackup

------------------注：需要时备份文件夹需要授权 chmod 777 dbbackup

-------------------------------(4)Rman 登陆

Last login: Tue Nov 13 22:44:07 2012 from 192.168.5.1
[oracle@h1 ~]$ rman target /

-----------------------

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 13 23:27:30 2012

connected to target database: ORCL (DBID=1326142752)

RMAN> exit

Recovery Manager complete.
[oracle@h1 ~]$ rman target u01/abc

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Nov 13 23:27:52 2012

connected to target database: ORCL (DBID=1326142752)

-------------------------------(5)rman 全备份

RMAN> backup database format='/home/oracle/dbbackup/db_full_%d_%s_%T.dbf';

------------_%d_%s_%T是

-----------%d 是数据库name

-----------%s 是数据库第几次备份

-----------%T 是时间戳

Starting backup at 13-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_3_20121113.dbf tag=TAG20121113T233510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_4_20121113.dbf tag=TAG20121113T233510 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-NOV-12

RMAN> exit

Recovery Manager complete.
[oracle@h1 ~]$ cd /home/oracle/dbbackup/
[oracle@h1 dbbackup]$ ls

--------备份结果
db_full_ORCL_3_20121113.dbf db_full_ORCL_4_20121113.dbf

-------------------------------(4)Rman 恢复时间点数据

-----------*用户u01建表

SQL> conn u01/abc
Connected.
SQL> create table salgrade as select * from scott.salgrade;

Table created.

-----------*Rman全备

RMAN> backup database format='/home/oracle/dbbackup/db_full_%d_%s_%T.dbf';

Starting backup at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf tag=TAG20121113T234553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-NOV-12
channel ORA_DISK_1: finished piece 1 at 13-NOV-12
piece handle=/home/oracle/dbbackup/db_full_ORCL_6_20121113.dbf tag=TAG20121113T234553 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-NOV-12

RMAN> exit

Recovery Manager complete.
[oracle@h1 dbbackup]$ cd /home/oracle/dbbackup/
[oracle@h1 dbbackup]$ ls
db_full_ORCL_3_20121113.dbf db_full_ORCL_5_20121113.dbf
db_full_ORCL_4_20121113.dbf db_full_ORCL_6_20121113.dbf

----------------蓝色体为最新全备

-----------*u01 drop表

SQL> drop table salgrade;

Table dropped.

SQL>

-----------*数据库进入mount状态

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

-----------*rman restore

RMAN> restore database;

Starting restore at 13-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_5_20121113.dbf tag=TAG20121113T234553
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 13-NOV-12

-----------*rman recover

RMAN> recover database;

Starting recover at 14-NOV-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-NOV-12

-----------*数据库open

SQL> select status from v$instance;

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

SQL> alter database open;

Database altered.

SQL>

SQL> conn u01/abc
Connected.
SQL> select * from salgrade;
select * from salgrade
*
ERROR at line 1:
ORA-00942: table or view does not exist

---------------------------------------因为没有设置时间点数据库recover后日志redo到drop table阶段
-----------------解决办法

[oracle@h1 dbbackup]$ pwd
/home/oracle/dbbackup
[oracle@h1 dbbackup]$ ls
db_full_ORCL_11_20121114.dbf db_full_ORCL_12_20121114.dbf

RMAN> run{
2> set until time "to_date('2012-11-14 01:06:00','yyyy-mm-dd hh24:mi:ss')";
3> restore database;
4> recover database;
5> sql 'alter database open resetlogs';
6> }

executing command: SET until clause

Starting restore at 14-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf tag=TAG20121114T010321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 14-NOV-12

Starting recover at 14-NOV-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-NOV-12

sql statement: alter database open resetlogs

----------------(5)Rman 恢复datafile丢失

-----------*模拟datafile丢失

[oracle@h1 orcl]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 01:45:17 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@h1 orcl]$ pwd
/app/oracle/oradata/orcl
[oracle@h1 orcl]$ mv users01.dbf users01.dbf.bak
[oracle@h1 orcl]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf.bak
[oracle@h1 orcl]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 01:46:56 2012

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 501059584 bytes
Fixed Size                  2214736 bytes
Variable Size             373294256 bytes
Database Buffers          121634816 bytes
Redo Buffers                3915776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/app/oracle/oradata/orcl/users01.dbf'

----------------------数据文件users01.dbf丢失，数据库无法启动

SQL> SQL> SQL> SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 501059584 bytes
Fixed Size                  2214736 bytes
Variable Size             373294256 bytes
Database Buffers          121634816 bytes
Redo Buffers                3915776 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-----------*Rman恢复数据文件
[oracle@h1 orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 14 01:54:07 2012

connected to target database: ORCL (DBID=1326142752, not open)

RMAN> restore database;

Starting restore at 14-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf
channel ORA_DISK_1: piece handle=/home/oracle/dbbackup/db_full_ORCL_11_20121114.dbf tag=TAG20121114T010321
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:06
Finished restore at 14-NOV-12

RMAN> recover database;

Starting recover at 14-NOV-12
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_14/o1_mf_1_1_8b50djx1_.arc
archived log file name=/app/oracle/flash_recovery_area/ORCL/archivelog/2012_11_14/o1_mf_1_1_8b50djx1_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:15
Finished recover at 14-NOV-12

RMAN> exit

Recovery Manager complete.

-----------*数据库启动
[oracle@h1 orcl]$ sqlplus "/as SYSDBA"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Nov 14 02:01:41 2012

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL>

转载于:https://blog.51cto.com/cswggod/1089518

weixin_34113237

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库冷备份及RMAN简单使用

1.jsp调用oracle 1.安装tomcat [root@h1 ROOT]# cd /var/App/ [root@h1 App]# unzip apache-tomcat-6.0.14.zip 2.复制ojdbc.jar [r...
复制链接

扫一扫