RMAN-Checking the Syntax of RMAN Commands

Problem
You want to check the syntax of your RMAN commands without actually executing the commands.

Solution
To check the syntax of RMAN commands, you must start the RMAN client with the operating system command-line argument checksyntax. You can easily check the syntax of commands prior to their execution either by entering them at the command prompt or by reading in the commands through a command file. Here’s how you check the syntax of a single RMAN command (run {backup database;}) by first starting the RMAN client with the checksyntax argument:

$. /rman checksyntax
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> run {backup database;}
The command has no syntax errors
RMAN>

In this example, there were no errors in the syntax of the simple run block, and RMAN confirms that. You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file. Simply specify the checksyntax argument before invoking the command file that consists of the RMAN commands. In the following example,
the file goodcmdfile contains a couple of restore and recovery commands:

$ rman checksyntax @/tmp/goodcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with legal syntax
2> restore database;
3> recover database;
4>
The cmdfile has no syntax errors
Recovery Manager complete.
$

You can also open an RMAN session solely for the purpose of checking the syntax of commands
that you type interactively:

$ rman checksyntax

An important point about the checksyntax argument is that you can’t use it after starting RMAN. That is, you can’t include the checksyntax argument from the RMAN command line. You must pass checksyntax as an argument to the rman command when you start the RMAN client and without connecting to any target or recovery catalog.

How It Works
When you either execute an RMAN command file by preceding it with the checksyntax argument or enter any RMAN commands after starting RMAN with the checksyntax argument, RMAN won’t actually execute any RMAN commands. RMAN will check and report only on the syntax of those commands. If the RMAN commands that you type at the command line or that you include as part of a command file have no errors, you get the “the command (cmdfile) has no errors” message from RMAN. Otherwise, RMAN will issue an error, as shown in the following example:

$ rman checksyntax @/tmp/badcmdfile
Recovery Manager: Release 11.1.0.1.0 - Beta on Mon Apr 2 08:31:11 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> # file with illegal syntax
RMAN> run (backup database);
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "(": expecting one of: "{"
RMAN-01007: at line 1 column 5 file: standard input
RMAN>

The output of the checksyntax command reveals there is a syntax error in your run block. The checksyntax command is handy for checking scripts for syntax errors. With RMAN, there’s no need for a script to fail unexpectedly because you mangled the syntax of a command. If you’re surprised by an error, it’s because you didn’t test with checksyntax first.

RMAN-Creating Dynamic Command Files

Problem
You want to create dynamic command files that can be used for multiple jobs by passing substitution variables.

Solution
You can create dynamic shell scripts by using substitution variables in the RMAN command files inside the shell scripts. You can specify values for use in substitution variables through the new using clause when calling an RMAN command file. You use the &integer syntax (&1, &2, and so on) to indicate to which variable your substitution values should be assigned, just as in SQL*Plus.
Let’s review an example that shows how to create a dynamic backup shell script.

1. Create the RMAN command file that uses two substitution variables:

#backup.cmd
connect target sys/<sys_password>@prod1
run {
backup database
tag &1
format &2
}
exit;

The command file shown here will back up the database using two substitution variables (&1 and &2), one for the backup tag and the other for the string value in the format specification.
2. Create the shell script to run the backup command file you created in step 1:

#!/bin/tcsh
# script name: nightly_backup.sh
set tag=$argv(1)
set format=$argv[2]
rman @backup.cmd using $tag $format

3. Now that you have created a dynamic shell script, you can specify the arguments for the tag and format variables on the command line, thus being able to modify them for different jobs. Here’s an example:

$ nightly_backup.sh longterm_backup back0420

The example shows how to execute the shell script nightly_backup.sh with two dynamic parameters, longterm_backup (tag) and back0420 (format string).

How It Works
The ability to use substitution variables in RMAN scripts is new in Oracle Database 11g. The use of substitution variables in RMAN scripts is similar to the way you specify substitution variables in operating system and SQL*Plus scripts. Specifying substitution variables lets you use the same command file by modifying it appropriately for different backup tasks, thus
making the command file dynamic.

RMAN-Executing Operating System Commands from Within RMAN

Problem
You’ve invoked the RMAN client, and now you need to issue some operating system commands.

Solution
Use the RMAN command host to invoke an operating system subshell. You can execute this command in two ways: you can issue it from the RMAN prompt, or you can execute it from inside a run block, which is a group of RMAN commands executed as a single unit. If you issue the host command stand-alone, without any parameters, RMAN will take you to the operating
system command line. Thus, the host command works the same in RMAN as it does from within SQL*Plus. If you issue the command host followed by a valid operating system command as a parameter, then RMAN will execute that operating system command and continue to process the rest of the commands in the run block, if there are any.
In the following example, we use the host command to list all files ending with dbf, after backing up a datafile from the RMAN prompt:

RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> backup datafile '/u01/app/oracle/oradata/targ/system01.dbf' format '/tmp/system01.dbf';
RMAN> host 'ls -l /tmp/*dbf';
RMAN> alter database open;

The following example uses the host command with no parameters to temporarily escape to the operating system level during an interactive RMAN session:

RMAN> backup datafile 3 format '/u01/app/oracle/oradata/targ_db/dbs01.cpy';
RMAN> host;
$ ls $ORACLE_HOME/oradata/dbs01.cpy /net/oracle/oradata/dbs01.cpy
$ exit
RMAN>

How It Works
As you can see in the two examples, you can use the host command with or without an operating system command as a parameter. If you run the host command as part of a series of RMAN commands, RMAN executes the host command and continues with the rest of the commands. When you execute the host command by itself, RMAN displays the operating
system command prompt and resumes after you exit the command-line subshell.

Creating an Oracle Password File

You can easily create an Oracle password file with the help of the orapwd utility. Just type orapwd at the operating system command line to view the syntax of the command:

$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

where
file - name of password file (required),
password - password for SYS (required),
entries - maximum number of distinct DBA (required),
force - whether to overwrite existing file (optional),
ignorecase - passwords are case-insensitive (optional),
nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
There must be no spaces around the equal-to (=) character.

Of the six options for the orapwd utility, the file, password, and entries options are mandatory. You can create a simple Oracle password file using the following syntax:

$ orapwd file=mydb_pwd password=sammyy1 entries=20

This command will create an Oracle password file. The default location for the password file is the $ORACLE_HOME/dbs directory. Once you create the password file, edit your init.ora file or your spfile in the following manner:

remote_login_passwordfile = 'EXCLUSIVE'

Once your restart your database after this, you’ll be able to log in as the sys user.

RMAN - Logging Command-Line RMAN Output

Problem
You want to log the output of RMAN commands you issue in command-line mode.

Solution
If you want RMAN to log all its output when you use RMAN from the operating system command line, just add the keyword log to the command line, and supply the name of the log file to use. For example:

$ rman target / cmdfile commandfile1.rcv log /u01/app/oracle/outfile.txt

In this case, RMAN will write the output of the RMAN commands in the command file named commandfile.rcv to the log file outfile.txt. If you later want to run another set of RMAN commands and want to append the log messages to the same log file, you can do this by using the append option along with the log option. Here’s an example:

$ rman target / cmdfile commandfile2.rcv log /u01/app/oracle/outfile.txt append

The previous command will append the output from executing the command file commandfile2.rcv to the text file outfile.txt.

How It Works
The command-line argument log causes RMAN to send all its output to the log file you specify.Failure to add the keyword append when referring to an already existing log file will result in the overwriting of that older log file. If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, you can take advantage of the Unix/Linux tee command. The tee command sends output both to a text file and to the terminal. Here’s how you use the tee command:

$ rman | tee rman.log
RMAN>

All is not lost if you don’t specify a log file to capture the RMAN output. The view V$RMAN_OUTPUT returns detailed information about RMAN jobs in progress. For example, if your media manager runs into a problem with a tape drive, RMAN records the associated error messages in V$RMAN_OUTPUT and also outputs the message to the terminal or to a log file. As with all dynamic performance views, the contents of the V$RMAN_OUTPUT view are refreshed when you restart the database. The V$RMAN_STATUS view contains information about completed RMAN jobs as well as all RMAN jobs in progress.

RMAN–Using RMAN

You can start using RMAN to back up and recover your databases with very little fanfare. When you install the Oracle server software, you’ll automatically install RMAN as well. You only absolutely need two things to start using RMAN: the database you want to back up (referred to as the target database) and the RMAN client, which is the interface you use to interact with the RMAN server processes that perform the actual backup and recovery tasks.

When you use RMAN to back up and recover your database files and objects, you use the RMAN client to interact with the database. The RMAN client interprets the RMAN commands you issue and starts up the necessary server sessions to process those commands. The term RMAN repository refers to the record of RMAN metadata about all backup and recovery actions on the target database. RMAN relies on this metadata when it performs backup and recovery operations.

By default, RMAN always stores a copy of the RMAN repository in the target database’s control file. Optionally, you can also use a recovery catalog for long-term storage of the RMAN repository. Whenever there is a change in the database structure, archived redo logs, or backups, RMAN updates the recovery catalog with the new information from the target database
control file. This way, you have an alternate source for the all-important RMAN repository data if you lose or can’t access the control file of the target database. In addition, the recovery catalog provides a long-term storage capacity for all RMAN backup and recovery information, whereas such older data is liable to be overwritten in the control file. The recovery catalog exists as a separate database schema, located ideally in a database separate from the target database(s). You can simplify your RMAN administration by using a single recovery catalog for all your Oracle databases.

You start up the RMAN client using the RMAN executable rman, which you’ll find in the $ORACLE_HOME/bin directory. In addition to the rman executable, RMAN also comes with two other internal components: one a set of PL/SQL procedures in the target database and the other a file named recover.bsq. RMAN turns the backup and recovery commands you issue into PL/SQL procedure calls using the recover.bsq file to construct the calls. After you start the
RMAN client, you must log in using either operating system credentials or database authentication. After logging in, you can issue backup and recovery instructions either by entering RMAN commands at the command line or by executing a script file that contains RMAN commands. You can also issue several types of SQL commands from the RMAN command line.
After you finish your backup and recovery session, you exit the RMAN client.

recover.bsq file is location in here - ?/rdbms/admin/recover.bsq

In addition to the target database and the RMAN client, the RMAN environment can have other optional elements. If you follow the Oracle’s backup and recovery recommendations you may also have a flash recovery area. In addition, you must have a media
management layer (MML) to interact with tape drives, since RMAN can’t work directly with the tape drives. RMAN can use either a third-party MML or Oracle’s own backup and recovery offering, called Oracle Secure Backup. The MML accesses and controls the tape libraries and manages the loading and unloading of tapes.

Finally, if you plan on working with several databases, it may be a smart idea to use an RMAN catalog database, which is a separate Oracle database dedicated to storing the recovery catalog. Although the recovery catalog isn’t mandatory, it provides two important advantages over using the database control file to store the RMAN metadata relating to backup and recovery
activity: you can store vastly greater amounts of data in the recovery catalog as compared to a control file, and you can store RMAN scripts inside the recovery catalog. By default, all RMAN-related records in the target database’s control file are overwritten after seven days, but you can control the length of retention by setting a higher value for the initialization parameter control_file_record_keep_time.

One may argue that since the control file can record all of RMAN’s metadata, there is no need to create and manage a separate recovery catalog database to store RMAN metadata. However, consider a situation where you lose all your control file copies at once. You can, of course, rebuild the control file quickly using the output of a recent alter database backup controlfile to trace command. However, when you re-create the control file using the output of that command, the one thing you do not get back is all the RMAN metadata that used to be stored in the control file! This and the fact that Oracle may always overwrite even useful
RMAN metadata in the control file means you should seriously consider using the recovery catalog. Oracle recommends using a recovery catalog in order to provide redundancy for your RMAN metadata. Chapter 6 discusses the recovery catalog in detail.

Backup and Recovery Instance Architecture–Undo Redo

The Oracle instance consists of the system global area (SGA), which is the memory allocated to the Oracle instance, and a set of Oracle processes called the background processes. The Oracle processes start when you start the instance and keep running as long as the instance is alive. Each of the Oracle background processes is in charge of a specific activity, such as writing
changed data to the datafiles, cleaning up after disconnected user sessions, and so on.
We’ll briefly review the key Oracle background processes that perform critical backup and recovery–related tasks, which are the checkpoint process, the log writer process, and the archiver process.
The Checkpoint Process
The checkpoint process does three things:
• It signals the database write process (DBWn) at each checkpoint.
• It updates the datafile headers with the checkpoint information.
• It updates the control files with the checkpoint information.
The Log Writer Process
Oracle’s online redo log files record all changes made to the database. Oracle uses a “writeahead” protocol, meaning the logs are written to before the datafiles are. Therefore, it is critical to always protect the online logs against loss by ensuring they are multiplexed. Any changes made to the database are first recorded in the redo log buffer, which is part of the SGA.
Redo log files come into play when a database instance fails or crashes. Upon restart, the instance will read the redo log files looking for any committed changes that need to be applied to the datafiles. Remember, when you commit, Oracle ensures that what you are committing has first been written to the redo log files before these changes are recorded in the actual datafiles. The redo log is the ultimate source of truth for all changes to the data in an Oracle database, since an instance failure before the changes are written to the datafiles means that the changes are only in the redo log files but not in the datafiles.
The log writer (LGWR) process is responsible for transferring the contents of the redo log buffer to the online redo log files. The log writer writes to the online redo files under the following circumstances:
• At each commit
• Every three seconds
• When the redo log buffer is one-third full
The important thing to remember here is that the log writer process writes before the database writer does, because of the write-ahead protocol. Data changes aren’t necessarily written to datafiles when you commit a transaction, but they are always written to the redo log.
The Archiver Process
The archiver (ARCn) is an optional background process and is in charge of archiving the filled online redo log files, before they can be overwritten by new data. The archiver background process is used only if you’re running your database in archivelog mode.

Physical Database Structures Used in Recovering Data
You need to deal with four major physical database structures during a database recovery:
• Datafiles
• Redo logs (archived and online)
• Control files
• Undo records
In a basic database recovery situation, you’d need to first restore datafiles by using backups (from a past period, of course).Once the restoration of the datafiles is completed, you issue the recover command, which results in the database rolling forward all committed data and thus bringing the database up-to-date. The database also rolls back any uncommitted data that’s recorded in the undo segments that are part of the undo tablespace. The database server automatically performs the rollback of uncommitted data by using undo records in the undo tablespace to undo all uncommitted changes that were applied to the datafiles from the
redo logs during the recovery process. This rolling back of uncommitted data takes place by using the information about all the changes made since the last database start-up. Oracle records all changes made to the database in files called the online redo log files. Since Oracle uses a round-robin method of writing the online redo log members, it is critical that you save the filled online redo logs before they are written. The process of saving the filled redo log files is called archiving, and the saved redo log files are termed archived redo log files. A media recovery process uses both the archived redo log files and the online redo log files.
The control file is essential for the Oracle instance to function, because it contains critical information concerning tablespace and datafile records, checkpoints, redo log threads in the current online redo log, log sequence numbers, and so on.
RMAN lets you back up all the files you need for a database recovery, including datafiles, control files, and archived redo logs. RMAN also lets you make image copies of both datafiles and control files, in addition to the standard RMAN-formatted backup pieces. You should never back up online redo log files; instead, always duplex these files to protect against the loss of an online redo log.
HOW TO DETECT UNDO:There are some views that show information related to undo activity:

• V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
• V$TRANSACTION: present time view providing information on current transactions.
• V$SESSTAT: individual session statistics, which includes one for undo usage.
• V$UNDOSTAT will provide who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.
• V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT a.sid, a.username, b.used_urec, b.used_ublk

FROM v$session a, v$transaction b

WHERE a.saddr = b.ses_addr

ORDER BY b.used_ublk DESC

• V$SESSTAT provides another view, which uses the undo kind of view, but we must avoid getting lost in the maze of Oracle statistics and focusing on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value

FROM v$sesstat a, v$statname b

WHERE a.statistic# = b.statistic#

AND a.statistic# = 176<– Which stands for undo change vector size

ORDER BY a.value DESC

HOW TO DETECT REDO:To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes

FROM v$session s, v$sess_io i

WHERE s.sid = i.sid

ORDER BY 5 desc, 1, 2, 3, 4;

1. Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
2. Query V$TRANSACTION. These view contains information about the amount of    undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

The query you can use is:

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec

FROM v$session s, v$transaction t

WHERE s.taddr = t.addr

ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session. You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.

Archivelog and Noarchivelog Mode of Operation
You can operate your Oracle database in either archivelog mode or noarchivelog mode. In noarchivelog mode, Oracle will overwrite the filled online redo logs, instead of archiving (saving) the online redo logs. In this mode, you’re protected only from instance failures, such as those caused by a power failure, for example, but not from a media failure. Thus, if there is a media failure, such as a damaged disk drive, the changes that were overwritten are gone forever, and the database won’t be able to access those data modifications to recover the database up to the current point in time. The transactions made since the last backup are lost forever, and you can restore the database only to the point of the last backup you made.

If you are running your database in noarchivelog mode and you happen to lose a datafile, for example, you follow these steps to get back to work again:
1. If the instance isn’t already shut down, first shut it down.
2. Restore the entire database (datafiles and control files) from the backups.
3. Restart the database by using the startup (open mode) command.
4. Users lose any data that was changed or newly entered in the database since you took the backup that was just restored. You can enter the data if you have a source, or you’re going to have a data loss situation.

If you are running a production database—or if you want to make sure that all the data changes made to any database, for that matter, are always protected—you must operate your database in archivelog mode. Only a database running in archivelog mode can recover from both instance and media failures. You can’t perform a media recovery on a database running in noarchivelog mode.

If you’re running the database in noarchivelog mode, remember that you can make a whole-database backup only after first shutting the database down. You can’t make any online tablespace backups in such a database. A database in noarchivelog mode also can’t use the tablespace point-in-time recovery technique. Make sure you take frequent whole-database backups if an important database is running in noarchivelog mode for some reason.