Run the SQL statement at the RMAN prompt.

When you manually restore the database, sometimes you need to go to the SQL * Plus prompt and the operating system prompt. It is cumbersome to switch back from the RMAN prompt. In fact, RMAN provides us

When you manually restore the database, sometimes you need to go to the SQL * Plus prompt and the operating system prompt. It is cumbersome to switch back from the RMAN prompt. In fact, RMAN provides us

When you manually restore the database, sometimes you need to go to the SQL * Plus prompt and the operating system prompt. It is cumbersome to switch back from the RMAN prompt. In fact, RMAN provides us with some simple SQL statements and PL/SQL methods under the command line to avoid the above situation. This article describes some common commands at the RMAN prompt and their usage.

1. Run the SQL statement at the RMAN prompt

Syntax: SQL'';

The syntax is relatively simple, that is, enter SQL at the prompt, and then run the SQL command. The command is enclosed in single quotes.

Command is usually an executable SQL command statement, or a PL/SQL block.

Limits

If an SQL statement contains a file name, the file name and path must be enclosed by two single quotes. Double quotation marks must be used at the beginning and end of the SQL statement.

SQL query statements can be executed, but no results are output.

Common commands at the RMAN prompt

Startup [nomount | mount], shutdown immediate

Alter system archive log current

Alter database open [resetlogs]

2. Run the SQL statement at the RMAN prompt

A. Execute common starup and alter SQL statements.
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,200 5, 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; ---> View the archived logs of the current database and list one archived log.

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'; ---> archive the current log

SQL statement: alter system archive log current

RMAN> list copy of archivelog all; ---> after archiving, one more archived log is displayed.

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_00009.arc

B. Execute the SQL query statement, as shown below, without any output.
RMAN> SQL 'select sysdate from dual ';

SQL statement: select sysdate from dual

RMAN> SQL 'select * from v $ database ';

SQL statement: select * from v $ database

C. Execute SQL statements with paths and file names
-- The execution below does not start with double quotation marks or enclose the path with double single quotation marks. An error message is returned.
RMAN> SQL 'create tablespace ts1 datafile'/u02/database/GOBO1/oradata/ts1.dbf 'size 10 m ';

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 "/"

-- The following SQL statement is successfully executed.
RMAN> SQL "create tablespace ts1 datafile''/u02/database/GOBO1/oradata/ts1.dbf'' size 10 m ";

SQL statement: create tablespace ts1 datafile ''/u02/database/GOBO1/oradata/ts1.dbf'' size 10 m

D. Execute PL/SQL blocks.
RMAN> SQL 'in in dbms_lock.sleep (3); end ;';

SQL statement: begin dbms_lock.sleep (3); end;

E. Run PL/SQL blocks using rman block commands.
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. Switch to the system prompt.
RMAN> host;

Robin @ SZDB: ~> Exit
Exit
Host command complete

RMAN>

,