martes, 29 de septiembre de 2009

RMAN Troubleshooting


The sbt function pointers are loaded from library

$ORACLE_HOME/bin/sbttest /tmp/x.lst

Moving and/or Resizing the FRA

The following SQL statement uses the alter system command to move the flash recovery area to /orabackup02/FRA:

SQL> alter system set db_recovery_file_dest='/oraback02/FRA';

If disk space is available, you can increase the size of the flash recovery area to an appropriate value. This example changes the flash recovery area size to 100GB:

SQL> alter system set db_recovery_file_dest_size=100g;

Changing Retention Policy and Deleting Old Backups

RMAN> report obsolete;

This command will show the retention policy and which backups and archived redo log
files are obsolete. Here is what the output might look like:

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 6
no obsolete backups found

In this example, no obsolete backups were reported. We’ll use the configure command to
change the retention policy from a redundancy policy of six down to two:

RMAN> configure retention policy to redundancy 2;
Now the report obsolete command shows that there are several obsolete files:

RMAN> report obsolete;

Dealing with the RMAN-06059 Error

RMAN> backup database plus archivelog;

Your backup process doesn’t get very far when RMAN throws this error:

RMAN-03002: failure of backup command ...
RMAN-06059: expected archived log not found, loss of archived log compromises

You must update RMAN’s repository to reflect that archived redo log files either have been physically deleted or have been moved to another location on disk. Use the crosscheck command

RMAN> crosscheck archivelog all;

If the archived redo log files have been physically moved to a different location on disk, then use the catalog command to update the RMAN repository with the new location of the files:

RMAN> catalog start with '/oradump01/oldarchive';

RMAN to back up any
archived redo log files that have an AVAILABLE status in the V$ARCHIVED_LOG view. You can query the STATUS column of V$ARCHIVED_LOG as follows:

SQL> select sequence#,
2 decode(status,'A','available','D','deleted','U','unavailable','X','expired')
3 from v$archived_log;

Using SQL to Terminate an RMAN Channel

Use the alter system kill session SQL statement to terminate a hung RMAN job. To do this, you need to first identify the serial ID and serial number:

2 s.sid
3 ,s.serial#
4 ,p.spid
5 ,s.client_info
6 FROM v$process p,
7 v$session s
8 WHERE p.addr = s.paddr
9 AND client_info LIKE '%rman%';
----- ---------- ------------ -------------------------
157 18030 7344 rman channel=ORA_DISK_1

SQL> alter system kill session '157,18030';

Diagnosing NLS Character Set Issues


You’re trying to connect to RMAN, and you get an NLS error similar to the following:
ORA-12705: Cannot access NLS data files or invalid environment specified


SQL> select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

$ setenv NLS_LANG american_america.we8iso8859p1

In a Unix Korn shell environment, use the OS export command as follows:

$ export NLS_LANG=american_america.we8iso8859p1

V$NLS_VALID_VALUES Lists all valid values for NLS settings.
NLS_SESSION_PARAMETERS Contains NLS values for the current session.
V$NLS_PARAMETERS Contains current values of NLS parameters.
NLS_INSTANCE_PARAMETERS Contains NLS values set at the instance level. NLS_DATABASE_PARAMETERS Contains NLS values defined when your database was created.
These can be overridden by the instance, client OS, or client

Logging RMAN Output

rman TARGET SYS/pwd debug trace=rman.trc log=rman.log

$ rman target / log=rman_output.log

From the RMAN Command Line
You can also spool the output to a log file from the RMAN command line, as shown here:

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> backup database;

To turn off logging, use the log off parameter, as shown here:

RMAN> spool log off;

$ rman target / log=rman_output.log append

RMAN> spool log to rman_output.log append

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> run{ allocate channel d1 type disk;
2> backup database;
3> release channel d1;
4> }

Viewing RMAN Command History

Use V$RMAN_OUTPUT to view the text messages that RMAN produces when performing
tasks. Run this query to view the historical RMAN command messages:
SQL> select
2 sid,
3 recid,
4 output
5 from v$rman_output
6 order by recid
7 /

Enabling RMAN’s Debug Output

From the OS Prompt
This first example enables all debugging and captures the output in a log file:
$ rman target / debug=all log=rman_output.log

The following example enables debugging just for I/O activities:
$ rman target / debug=io

When Configuring a Channel
This example configures a channel to debug and trace at level 5:

RMAN> configure channel device type disk debug=5 trace=5;

RMAN> spool log to rman_output.log
RMAN> debug on
RMAN> set echo on
RMAN> backup database;
RMAN> debug off
RMAN> spool log off

Enabling Granular Time Reporting

$ setenv NLS_DATE_FORMAT 'dd-mon-yyyy hh24:mi:ss'
In a Unix Korn shell environment, use the OS export command as follows:

$ export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'
In a Windows environment, use the set command as shown here:

c:\> set NLS_DATE_FORMAT=dd-mon-yyyy hh24:mi:ss

For example, if you wanted to just debug I/O-related operations when backing up your
users tablespace, then you would enable I/O debugging as shown here:

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> debug io
RMAN> backup tablespace users;
RMAN> debug off
RMAN> spool log off

RMAN Compatibility Matrix

Target DB Auxiliary DB RMAN Executable Catalog DB Catalog Schema >= 8.1.7 or >=
9.0.1 9.0.1 9.0.1 >= 8.1.7 >= RMAN executable
9.2.0 9.2.0 >= and <= Target DB >= 8.1.7 >= RMAN executable
10.1.0 10.1.0 >= and <= Target DB >= 9.0.1 >= RMAN executable
10.2.0 10.2.0 >= and <= Target DB >= 9.0.1 >= RMAN executable
11.1.0 11.1.0 TBD TBD >= RMAN executable

Managing Files in an ASM Environment

C:\> asmcmd -p
You should now see the ASMCMD prompt:
The -p option will set your prompt to display the current working directory as part of the
prompt. For example, the ASMCMD prompt changes as we use the cd command to change
the current directory:
ASMCMD [+] > cd +data/prmy/datafile
ASMCMD [+data/prmy/datafile] >

Command Description
cd Changes the current directory to the specified directory
du Displays the total disk space occupied by ASM files in the specified ASM
directory and all its subdirectories, recursively
exit Exits ASMCMD
find Lists the paths of all occurrences of the specified name (with wildcards) under
the specified directory help Displays the syntax and description of ASMCMD commands
ls Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups
lsct Lists information about current ASM clients
lsdg Lists all disk groups and their attributes
mkalias Creates an alias for a system-generated filename
mkdir Creates ASM directories
pwd Displays the path of the current ASM directory
rm Deletes the specified ASM files or directories
rmalias Deletes the specified alias, retaining the file to which the alias points
md_backup Creates a metadata backup script of mounted disk groups (Oracle Database
11g only)
md_restore Restores a disk group backup (Oracle Database 11g only)
lsdsk Lists the ASM disks (Oracle Database 11g only)
repair Repairs range of physical blocks on the ASM disk (Oracle Database 11g only)

with SQL*Plus, the following query is useful for viewing files within your ASM environment:

SQL> SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM
2 (SELECT gname, a.parent_index pindex, aname,
3 a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
4 WHERE a.group_number = g.group_number)
5 START WITH (mod(pindex, power(2, 24))) = 0
6 CONNECT BY PRIOR rindex = pindex;

Backup Validation with RMAN
You can run the BACKUP ... VALIDATE command to check datafiles for physical and logical corruption, or to confirm that all database files exist in the correct locations. No backup is taken, but all specified files are scanned to verify that they can be backed up. All corruptions are recorded in theV$DATABASE_BLOCK_CORRUPTION view.

The following example shows how to check your entire database and archived redo log files for physical and logical corruption:

You cannot use the MAXCORRUPT or PROXY parameters with the VALIDATE option.

# mark backup as unavailable in the repository so that RMAN does not attempt to
# restore it unless explicitly specified on the RESTORE command