How to see the oldest flashback available?

Using the following query one can see the flashback data available.

SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME,
(sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f;

CURRENT_TIME OLDEST_FLASHBACK HIST_MIN
—————- —————- ———-
2012-04-25 07:34 2012-04-25 05:48 106.066667

RMAN – incremental backup

RMAN command to create level 0 backup which is needed before running of incremental backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;

How to fix issue when the online redo logs are missing?

If for some reason the online redo log were lost or when cloning the copy of shutdown database you don’t have the on-line redo logs, you would need to open the database with “RESETLOGS” option which will create the online redo logs.

Example:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 356517584 bytes
Database Buffers 155189248 bytes
Redo Buffers 8052736 bytes
SQL> alter database mount;

Database altered.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

-- shows online redo logs have been recreated
SQL> select member from v$logfile;

MEMBER
—————————————–
E:\ORACLE\DB\TESTDB\ORADATA\REDO01A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO01B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG

6 rows selected.

One has perform “RECOVER DATABASE UNTIL CANCEL” before opening the database, if one doesn’t Oracle will generate the following error when trying to open with resetlogs:
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

How to check if RMAN catalog is installed for a database?

There are couple of ways to determine if RMAN database is registered with a catalog.

Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
calora4db01q:/home/oracle $ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=1023910334)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

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> select * from rc_database where name = ‘TESTDB’;

no rows selected

redo log generation during unrecoverable/nologging

# Capture amount of redo logs generated when table is created in UNRECOVERABLE
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 597736

SQL> create table test_all unrecoverable as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 648936

CREATE TABLE with NOLOGGING option
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1193320

SQL> create table test_all nologging as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1244436

Amount of redo logs generated for CREATE TABLE AS
– without UNRECOVERABLE/NOLOGGING option = 478904
– UNRECOVERABLE option = 51200
– NOLOGGING option = 51116
The amount of redo size is considerable less when nologging/unrecoverable is used.
Note: The database has to be in ARCHIVELOG mode to see the difference, if the database is in NOARCHIVELOG mode you won't see the difference.

Using RMAN to validate backup

When using VALIDATE option, Oracle will validate backup and not create backups and will not restore when this option is used.

# Validate if datafile exists and have no physical or logical corruptions that would prevent RMAN from running. If “archivelog” is specified it will also check if archive logs are valid too
RMAN> backup validate database archivelog all;

# Validate if datafile exists and have no physical or logical corruptions that would prevent RMAN from running.
RMAN> backup validate database;

# Validate if a specific tablespace can be restored, Oracle will check the backup piece to validate if the tablespace can be restored
RMAN> RESTORE VALIDATE TABLESPACE system;

Starting restore at 31-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/oradata/TEST/rman/60m3efef
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oradata/TEST/rman/60m3efef tag=TAG20110130T203006
channel ORA_DISK_1: validation complete, elapsed time: 00:00:47
Finished restore at 31-JAN-11

# Validate backup for database full restore, RMAN will not perform restore it will validate backup piece
RMAN> RESTORE VALIDATE DATABASE;

Starting restore at 31-JAN-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /u01/oradata/TEST/rman/60m3efef
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/oradata/TEST/rman/60m3efef tag=TAG20110130T203006
channel ORA_DISK_1: validation complete, elapsed time: 00:00:36
Finished restore at 31-JAN-11

How to rename block change tracking file?

To move/rename current block change tracking file, one would need to restart the database.

# Get current block change tracking file
SQL> SELECT filename FROM v$block_change_tracking;

FILENAME
——————————————————————————–
/tmp/blocktracking.f

# Shutdown database and start database in mount state
SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

# Rename block tracking file and
SQL> ALTER DATABASE RENAME FILE ‘/tmp/blocktracking.f’ TO ‘/u01/oradata/TEST/blocktracking.f’;

# Move current block tracking file through OS
SQL> !mv /tmp/blocktracking.f /u01/oradata/TEST/blocktracking.f

# Open database
SQL> ALTER DATABASE OPEN;

Note: Recreating the file by using disable and creating it will not use until level 0 backup is run.

RMAN – to enable compression of backupset

Backup in RMAN can be compressed when they are being created. Using the following command one can compress backup to disk or tape. The commands used to backup database remain the same.

# Enable compression when creating backup to disk
RMAN> configure device type disk backup type to compressed backupset;

# Enable compression when creating backup to tape
RMAN> configure device type sbt backup type to compressed backupset;

# Backup the database shows the command remains the same
RMAN> run {
backup database;
}

Shows backup size when non-compressed backup versus compressed backup, the percentage of compression may vary.
-rw-r—– 1 oracle oinstall 1109958656 Jan 22 20:09 4rm2pb5i # Shows non-compression backup size
-rw-r—– 1 oracle oinstall 139124736 Jan 22 20:12 4tm2pbb5 # Shows compressed backup size

By looking at v$sesstat one can see the compression rate by comparing physical write total bytes for RMAN sessions
RMAN session where compression was enabled
SID NAME VALUE
———- —————————————————————- ———-
23 physical write total bytes 152788992
23 physical read total bytes 1368006656

RMAN session where compression was not enabled
SID NAME VALUE
———- —————————————————————- ———-
24 physical write total bytes 1124999168
24 physical read total bytes 1367990272

To disable backup for disk
RMAN> configure device type disk backup type to backupset;

To disable backup for tape
RMAN> configure device type sbt backup type to backupset;

RMAN – checksyntax

Starting RMAN with check syntax will check syntax of RMAN command, it will report errors if the syntax is incorrect.

Example:
-- Start RMAN with check syntax option
$ $ORACLE_HOME/bin/rman checksyntax

Recovery Manager: Release 10.2.0.1.0 – Production on Sat Jan 22 19:49:42 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

-- Valid RMAN command, RMAN validates syntax and prints if syntax is correct, it doesn’t run the command
RMAN> run { backup database; }

The command has no syntax errors

-- Invalid RMAN command shows the command has syntax error
RMAN> run {backup databaase; }

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “identifier”: expecting one of: “archivelog, as,
backup, backupset, blocks, channel, check, copy, copies, controlfilecopy, cumul
ative, current, database, datafile, datafilecopy, device, diskratio, db_recovery
_file_dest, db_file_name_convert, duration, filesperset, for, format, full, force, incremental, keep, (, maxsetsize, nochecksum, noexclude, nokeep, not, proxy,pool, reuse, recovery, skip, spfile, setsize, tablespace, tag, to, validate”
RMAN-01008: the bad identifier was: databaase
RMAN-01007: at line 1 column 13 file: standard input

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: “allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate”
RMAN-01007: at line 1 column 22 file: standard input

To check the syntax of a RMAN cmdfile or script file
$ cat /tmp/rmanbackup.cmdfile
run {
backup database;
}

$ $ORACLE_HOME/bin/rman checksyntax @/tmp/rmanbackup.cmdfile

Recovery Manager: Release 10.2.0.1.0 – Production on Sat Jan 22 19:57:08 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> run {
2> backup database;
3> }
4>
The cmdfile has no syntax errors

Offlining a datafile

When one offline a datafile before you online the datafile one has to recover the datafile

1) Offline a datafile
SQL> alter database datafile 5 offline;

Database altered.

— Shows datafile is in recover state
SQL> select status from v$datafile where file# = 5;

STATUS
——-
RECOVER

— datafile can’t be made online
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: ‘/u01/oradata/TEST/testtbs_01.dbf’

— recover datafile
SQL> recover datafile ‘/u01/oradata/TEST/testtbs_01.dbf’;
Media recovery complete.
SQL> alter database datafile 5 online;

Database altered.