RMAN 提示符下执行SQL语句
在手动恢复数据库时，有时候需要在SQL*Plus提示符以及操作系统提示符，RMAN提示符下来回切换显得有些繁琐。实际上RMAN为我们提供了命令行下执行一些简单的SQL语句以及PL/SQL的方法，以避免上述情形。本文描述了RMAN提示符下的一些常用命令及其用法。
1、RMAN提示符下执行SQL语句
语法： SQL '
语法比较简单，就是提示符下输入SQL，后接SQL命令，命令用单引号括起来
command 通常为可执行的SQL命令语句，也可以为PL/SQL块
使用限制
如果SQL语句中包含文件名，则文件名及路径需要使用两个单引号括起来，整个SQL语句首尾需要使用双引号
可以执行SQL查询语句，但是无任何结果输出
RMAN提示符下常用的命令
startup [nomount | mount], shutdown immediate
alter system archive log current
alter database open [resetlogs]
2、演示RMAN提示符下执行SQL语句
- a、执行常用的starup, alter SQL 语句
- robin@SZDB:~> export ORACLE_SID=GOBO1
- robin@SZDB:~> rman target /
- Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jun 4 11:04:26 2013
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- connected to target database (not started)
- RMAN> startup nomount;
- Oracle instance started
- Total System Global Area 536870912 bytes
- Fixed Size 2074080 bytes
- Variable Size 314575392 bytes
- Database Buffers 213909504 bytes
- Redo Buffers 6311936 bytes
- RMAN> alter database mount;
- using target database control file instead of recovery catalog
- database mounted
- RMAN> alter database open;
- database opened
- RMAN> list copy of archivelog all; --->查看当前数据库的归档日志，列出了1个已归档日志
- List of Archived Log Copies
- Key Thrd Seq S Low Time Name
- ------- ---- ------- - ----------------- ----
- 2084 1 8 A 20130604 09:53:17 /u02/database/GOBO1/archive/arch_816906485_1_8.arc
- RMAN> sql ' alter system archive log current '; --->对当前日志进行归档
- sql statement: alter system archive log current
- RMAN> list copy of archivelog all; --->归档后可以看到多出了1个已归档日志
- List of Archived Log Copies
- Key Thrd Seq S Low Time Name
- ------- ---- ------- - ----------------- ----
- 2084 1 8 A 20130604 09:53:17 /u02/database/GOBO1/archive/arch_816906485_1_8.arc
- 2085 1 9 A 20130604 10:46:36 /u02/database/GOBO1/archive/arch_816906485_1_9.arc
- b、执行SQL查询语句，如下，没有任何结果输出
- RMAN> sql 'select sysdate from dual';
- sql statement: select sysdate from dual
- RMAN> sql ' select * from v$database ';
- sql statement: select * from v$database
- -- Author : Robinson
- -- Blog : http://blog.csdn.net/robinson_0612
- c、执行带有路径及文件名的SQL语句
- --下面的执行那个中没有使用双引号开头以及双单引号括住路径，收到错误提示
- RMAN> sql ' create tablespace ts1 datafile '/u02/database/GOBO1/oradata/ts1.dbf' size 10m ';
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-00558: error encountered while parsing input commands
- RMAN-01006: error signalled during parse
- RMAN-02001: unrecognized punctuation symbol "/"
- --下面的SQL语句被成功执行
- RMAN> sql "create tablespace ts1 datafile ''/u02/database/GOBO1/oradata/ts1.dbf'' size 10m";
- sql statement: create tablespace ts1 datafile ''/u02/database/GOBO1/oradata/ts1.dbf'' size 10m
- d、执行PL/SQL块
- RMAN> sql ' begin dbms_lock.sleep(3); end;';
- sql statement: begin dbms_lock.sleep(3); end;
- e、rman块命令方式执行PL/SQL块
- RMAN> run{
- 2> sql ' begin dbms_lock.sleep(3); end; ';
- 3> }
- sql statement: begin dbms_lock.sleep(3); end;
- RMAN> run {
- 2> sql ' drop tablespace ts1 including contents and datafiles ';
- 3> }
- sql statement: drop tablespace ts1 including contents and datafiles
- RMAN> shutdown immediate;
- database closed
- database dismounted
- Oracle instance shut down
- f、切换到系统提示符下
- RMAN> host;
- robin@SZDB:~> exit
- exit
- host command complete
- RMAN>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23490154/viewspace-1062397/ ，如需转载，请注明出处，否则将追究法律责任。
