How to make BDB to auto remove log file


Jephe Wu - http://linuxtechres.blogspot.com

Objective: to understand how BDB (DB4) log file remove works and automatically remove old logfiles to prevent disk space full
background:  Ubuntu 8.04.2 , Berkeley DB 4.2.52, OpenLDAP


Steps:

1.  BDB version used by openldap

root@ldap1:~/ # ldd /usr/sbin/slapd
linux-gate.so.1 =>  (0xb7faa000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb7f62000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb7f55000)
libdb-4.2.so => /usr/lib/libdb-4.2.so (0xb7e7c000)


root@ldap1:~/ # dpkg -l| grep db
ii  console-tools                              1:0.2.3dbs-65ubuntu7        Linux console and font utilities
ii  dbus                                       1.1.20-1ubuntu3.2           simple interprocess messaging system
ii  libconsole                                 1:0.2.3dbs-65ubuntu7        Shared libraries for Linux console and font
ii  libdb4.2                                   4.2.52+dfsg-4               Berkeley v4.2 Database Libraries [runtime]


2. log file removal
http://sepp.oetiker.ch/db-4.2.52-mo/ref/transapp/logfile.html

Log files may be removed at any time, as long as:


  • the log file is not involved in an active transaction.
  • a checkpoint has been written subsequent to the log file's creation.
  • the log file is not the only log file in the environment.


method 3:

Call the DB_ENV->set_flags method from the application, with the DB_LOG_AUTOREMOVE (http://sepp.oetiker.ch/db-4.2.52-mo/api_c/env_set_flags.html#DB_LOG_AUTOREMOVE) flag, to remove any log files that are no longer needed on an ongoing basis. With this configuration, Berkeley DB will automatically remove log files, and the application will not have an opportunity to copy the log files to backup media.

Two ways to implement checkpoint (besides DB_LOG_AUTOREMOVE flag in DB_CONFIG)


a. checkpoint settings in slapd.conf

checkpoint kbyte min
The checkpoint directive defines the time between checkpoint operations in BDB (the database can only be recovered from the last checkpoint).

The frequency of checkpointing determines the time during which data may be unrecoverable by BDB in the event of a system failure. If NOT using the dbnosync this time could be set to a reasonably long period, say, 10 mins or more, if the dbnosync directive is being used 5 - 15 mins or less if practical. kbytes is the number of kilobytes written to the directory and min is the time in minutes. Whichever occurs first determines the period between checkpoints.

OpenLDAP default is NO CHECKPOINTING - you should always supply a checkpoint directive. See the BDB Chapter 12 Section 15 documentation for more information. This directive may be replaced by using DB_CONFIG file with the txn_checkpoint directive. Examples:

checkpoint 128 15
# check point whenever 128k data bytes written or
# 15 minutes has elapsed whichever occurs first


b.  txn_checkpoint in DB_CONFIG file


# more DB_CONFIG
# see more detail config parameter at http://sepp.oetiker.ch/db-4.2.52-mo/ref/env/intro.html

set_flags DB_LOG_AUTOREMOVE
txn_checkpoint 1024 5 0



DB_CONFIG example:
http://www.zytrax.com/books/ldap/ch6/bdb.html#db-config




root@ldap1:/etc/ldap/ # grep checkpoint slapd.conf
# first DIT definition
database bdb
...
# DIT will act as a provider
overlay syncprov
checkpoint  1024 5
syncprov-checkpoint 100 10


3. working configuration example:
The following configuration makes logfile auto remove possible in my testing environment.


  • DB_CONFIG (sitting at the same directory as BDB database files)

set_cachesize 0 2097152 0
# default cache size is 256KB
# sets a database cache of 0G(first column) + 2M(2*1024*1024=2097152) and 
# do not allows fragmentation 
# does NOT replace slapd.conf cachesize 
# this is a database parameter, for slapd.conf cachesize paramter, refer to
# http://www.zytrax.com/books/ldap/ch6/bdb.html#cachesize
# see http://sepp.oetiker.ch/db-4.2.52-mo/api_c/env_set_cachesize.html
# The database environment's cache size may also be set using the environment's DB_CONFIG file. The syntax of the entry in that file is a single line with the string "set_cachesize", one or more whitespace characters, and the cache size specified in three parts: the gigabytes of cache, the additional bytes of cache, and the number of caches, also separated by whitespace characters. For example, "set_cachesize 2 524288000 3" would create a 2.5GB logical cache, split between three physical caches. Because the DB_CONFIG file is read when the database environment is opened, it will silently overrule configuration done before that time.
set_lk_max_objects 1500 
set_lk_max_locks 1500
set_lk_max_lockers 1500
set_flags DB_LOG_AUTOREMOVE
txn_checkpoint 1024 5 0
# replaces checkpoint in slap.conf
# writes checkpoint if 128K written or every 15 mins
# 0 = no writes - no update 

  • slapd.conf  (put the following checkpoint in the every BDB definition)
checkpoint  1024 5

4. References:


http://www.openldap.org/doc/admin24/maintenance.html

Administrators can change the size limit of a single log file (by default 10MB), and have old log files removed automatically, by setting up DB environment (see below). The reason Berkeley DB never deletes any log files by default is that the administrator may wish to backup the log files before removal to make database recovery possible even after a catastrophic failure, such as file system corruption.

Log file names are log.XXXXXXXXXX (X is a digit). By default the log files are located in the BDB backend directory. The db_archive tool knows what log files are used in current transactions, and what are not. Administrators can move unused log files to a backup media, and delete them. To have them removed automatically, place set_flags DB_LOG_AUTOREMOVE directive in DB_CONFIG.

Note: If the log files are removed automatically, recovery after a catastrophic failure is likely to be impossible.


http://www.zytrax.com/books/ldap/ch6/bdb.html#samples

cachesize  in slapd.conf
Format:
cachesize integer
The cachesize directive defines the number of entries that the LDAP backend will maintain in memory. Do not confuse this directive with the BDB set_cachesize directive - they control different behaviours.

For maximum performance this figure should be as high as practical or as close as practical to the number of records maintained in the directory. The default is 1000. Examples:

cachesize 10000
# LDAP maintains 10,000 entries in memory 
See also Performance chapter.


http://www.openldap.org/faq/data/cache/1072.html


How to rebuild Oracle database controlfile

Jephe Wu - http://linuxtechres.blogspot.com

Objective:  rebuild database controlfile without any backup
Environment: Oracle database enterprise edition 11.2.0.3 64bit, lost controlfile without any RMAN backup, datafile and online redo log are okay. need to rebuild control file manually.


Steps:

Preparation:

  • Find out all files path such as redo log, datafile
  • Find out database SID from /etc/oratab
  • calculate redo log file size


1. prepare rebuild script.

-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.


$ more controlfile_rebuild.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "sm" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1200
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 (
    '/u01/oradata/sm/redo01.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/u01/oradata/sm/redo02.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/u01/oradata/sm/redo03.log'
  ) SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u02/oradata/sm/system01.dbf',
  '/u02/oradata/sm/sysaux01.dbf',
  '/u02/oradata/sm/undotbs01.dbf',
  '/u02/oradata/sm/users01.dbf'
CHARACTER SET AL32UTF8
;

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

ALTER DATABASE OPEN;


[oracle@oratest sm]$ sqlplus / as sysdba @controlrebuild.sql 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 15:50:39 2012

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

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size    2235208 bytes
Variable Size 2315257016 bytes
Database Buffers 1946157056 bytes
Redo Buffers   12132352 bytes

Control file created.

ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required



System altered.


Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/sm/system01.dbf
/u02/oradata/sm/sysaux01.dbf
/u02/oradata/sm/undotbs01.dbf
/u02/oradata/sm/users01.dbf


SQL>  show parameter control

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time     integer 7
control_files     string /u01/oradata/sm/control01.ctl,
 /u01/oradata/sm/control02.ctl
control_management_pack_access     string DIAGNOSTIC+TUNING


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/sm/temp02.dbf' size 10m autoextend on next 1m;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/sm/temp02.dbf

sql> !rm -f /u02/oradata/sm/temp01.dbf

Oracle archivelog retention policy


Jephe Wu - http://linuxtechres.blogspot.com

Objective: understanding Oracle archivelog retention policy
Environment: Oracle Linux 6.3 64bit, Oracle database 11gR2 Enterprise Edition.


case 1: use flush recovery area for archivelog and archivelog deletion

[oracle@oratest archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 08:43:55 2012

Copyright (c) 1982, 2009, 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> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     206
Next log sequence to archive   208
Current log sequence       208

SQL> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oratest archivelog]$ find /home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/ -type f
/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc
[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 08:45:10 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name CMDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_cmdb.f'; # default

Note: before rman backup, try to delete archivelog all, failed, as it will check deletion policy, need to be backed up once.

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc thread=1 sequence=208

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc thread=1 sequence=208

RMAN> delete noprompt force archivelog all;  (force will override deletion policy checking)

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2       1    208     A 22-NOV-12
        Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc

deleted archived log
archived log file name=/home/oracle/app/oracle/flash_recovery_area/CMDB/archivelog/2012_11_22/o1_mf_1_208_8btltmgl_.arc RECID=2 STAMP=800009091
Deleted 1 objects


RMAN>


case 2: use user-specified area for archivelog instead of flush recovery area

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog';

System altered.

SQL> archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       /home/oracle/archivelog
Oldest online log sequence     207
Next log sequence to archive   209
Current log sequence       209

SQL> alter system archive log current;

System altered.

SQL> !ls -l /home/oracle/archivelog
total 156
-rw-r----- 1 oracle oinstall 153600 Nov 22 08:51 1_209_796487725.dbf

RMAN> show archivelog deletion policy;

RMAN configuration parameters for database with db_unique_name CMDB are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete archivelog sequence 209;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete archivelog all;        

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf thread=1 sequence=209

RMAN> delete force archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
List of Archived Log Copies for database with db_unique_name CMDB

=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
3       1    209     A 22-NOV-12
        Name: /home/oracle/archivelog/1_209_796487725.dbf


Do you really want to delete the above objects (enter YES or NO)? y
deleted archived log
archived log file name=/home/oracle/archivelog/1_209_796487725.dbf RECID=3 STAMP=800009468
Deleted 1 objects

Summary: no matter archivelog sitting in flash recovery area or other location, it will always check archive log deletion policy, but force option override it.


case 3: restore archivelog from rman backup
========================
3.1 use rman to backup database plus archivelog, it will first archive log current, then at the end of backup, it will archive log current again, 
so it will generate logfile sequence 211 and 212, before rman, there was only 210.


[oracle@oratest archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 08:54:39 2012

Copyright (c) 1982, 2009, 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> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oratest archivelog]$ find .
.
./1_210_796487725.dbf

[oracle@oratest archivelog]$ ls
1_210_796487725.dbf

[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 08:55:08 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> backup database plus archivelog;


Starting backup at 22-NOV-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=210 RECID=4 STAMP=800009690
input archived log thread=1 sequence=211 RECID=5 STAMP=800009718
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp tag=TAG20121122T085519 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

Starting backup at 22-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/cmdb/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/cmdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T085520_8btmg92p_.bkp tag=TAG20121122T085520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_ncsnf_TAG20121122T085520_8btmjyt0_.bkp tag=TAG20121122T085520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

Starting backup at 22-NOV-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=212 RECID=6 STAMP=800009808
channel ORA_DISK_1: starting piece 1 at 22-NOV-12
channel ORA_DISK_1: finished piece 1 at 22-NOV-12
piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp tag=TAG20121122T085648 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-NOV-12

3.1.1 how does rman backup database works?

a. when issuing 'backup database plus archivelog', it will firstly run 'alter system archive log curent' to generate one additional archivelog first.
in this case, it generated log seq no 213.

Thu Nov 22 08:56:47 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 08:56:48 2012
Thread 1 cannot allocate new log, sequence 213
Private strand flush not complete
  Current log# 2 seq# 212 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo02.log
Thread 1 advanced to log sequence 213 (LGWR switch)
  Current log# 3 seq# 213 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo03.log
Archived Log entry 6 added for thread 1 sequence 212 ID 0x42c754aa dest 1:
Thu Nov 22 09:31:00 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 09:31:00 2012
Thread 1 cannot allocate new log, sequence 214
Private strand flush not complete
  Current log# 3 seq# 213 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo03.log
Thread 1 advanced to log sequence 214 (LGWR switch)
  Current log# 1 seq# 214 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo01.log
Archived Log entry 10 added for thread 1 sequence 213 ID 0x42c754aa dest 1:

---------
after done rman backup, it run 'alter systme archive log current' again to generate seq 214.
------------

Thu Nov 22 09:32:29 2012
ALTER SYSTEM ARCHIVE LOG
Thu Nov 22 09:32:29 2012
Thread 1 cannot allocate new log, sequence 215
Private strand flush not complete
  Current log# 1 seq# 214 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo01.log
Thread 1 advanced to log sequence 215 (LGWR switch)
  Current log# 2 seq# 215 mem# 0: /home/oracle/app/oracle/oradata/cmdb/redo02.log
Archived Log entry 11 added for thread 1 sequence 214 ID 0x42c754aa dest 1:


[oracle@oratest archivelog]$ ls -lart
total 5132
drwx------ 30 oracle oinstall    4096 Nov 22 08:35 ..
-rw-r-----  1 oracle oinstall 5131264 Nov 22 09:31 1_213_796487725.dbf
-rw-r-----  1 oracle oinstall   90624 Nov 22 09:32 1_214_796487725.dbf
drwxr-xr-x  2 oracle oinstall    4096 Nov 22 09:32 .



When comes to restore, seq 213 is not required.
===========================

RMAN> restore database preview;

Starting restore at 22-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK



List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.27G      DISK        00:01:22     22-NOV-12    
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20121122T093102
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T093102_8btok67l_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/system01.dbf
  2       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
  3       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
  4       Full 6476692    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/users01.dbf
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
11      1    214     A 22-NOV-12
        Name: /home/oracle/archivelog/1_214_796487725.dbf

12      1    215     A 22-NOV-12
        Name: /home/oracle/archivelog/1_215_796487725.dbf

Media recovery start SCN is 6476683
Recovery must be done beyond SCN 6476692 to clear datafile fuzziness
Finished restore at 22-NOV-12


3.2 restore archivelog from rman backup
==============================================

RMAN> run {
2> set archivelog destination to '/tmp';
3> restore archivelog from logseq=210 until logseq=212; 
4> }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK

archived log for thread 1 with sequence 210 is already on disk as file /home/oracle/archivelog/1_210_796487725.dbf
archived log for thread 1 with sequence 211 is already on disk as file /home/oracle/archivelog/1_211_796487725.dbf
archived log for thread 1 with sequence 212 is already on disk as file /home/oracle/archivelog/1_212_796487725.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 22-NOV-12


Recovery Manager complete.
[oracle@oratest archivelog]$ ls
1_210_796487725.dbf  1_211_796487725.dbf  1_212_796487725.dbf
[oracle@oratest archivelog]$ rm -f *
[oracle@oratest archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 22 09:00:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CMDB (DBID=1120341162)

RMAN> run { set archivelog destination to '/tmp'; restore archivelog from logseq=210 until logseq=212; }

executing command: SET ARCHIVELOG DESTINATION
using target database control file instead of recovery catalog

Starting restore at 22-NOV-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK

channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=210
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=211
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085519_8btmg7dq_.bkp tag=TAG20121122T085519
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/tmp
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=212
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_annnn_TAG20121122T085648_8btmk08t_.bkp tag=TAG20121122T085648
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 22-NOV-12


[oracle@oratest tmp]$ ls -l /tmp/*.dbf
-rw-r----- 1 oracle oinstall 1017856 Nov 22 09:00 /tmp/1_210_796487725.dbf
-rw-r----- 1 oracle oinstall   10752 Nov 22 09:00 /tmp/1_211_796487725.dbf
-rw-r----- 1 oracle oinstall   45056 Nov 22 09:00 /tmp/1_212_796487725.dbf


3.3 preview restore database, it requires archivelog 212.
RMAN> restore database preview;

Starting restore at 22-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.27G      DISK        00:01:22     22-NOV-12    
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20121122T085520
        Piece Name: /home/oracle/app/oracle/flash_recovery_area/CMDB/backupset/2012_11_22/o1_mf_nnndf_TAG20121122T085520_8btmg92p_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/system01.dbf
  2       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/sysaux01.dbf
  3       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/undotbs01.dbf
  4       Full 6472970    22-NOV-12 /home/oracle/app/oracle/oradata/cmdb/users01.dbf
using channel ORA_DISK_1

List of Archived Log Copies for database with db_unique_name CMDB
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
9       1    212     A 22-NOV-12
        Name: /tmp/1_212_796487725.dbf

Media recovery start SCN is 6472970
Recovery must be done beyond SCN 6472970 to clear datafile fuzziness
Finished restore at 22-NOV-12


References:

1. http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm

Commands
1.  list archivelog that backed up 2 times.
list archivelog all backed up 2 times to device type disk;

NFS client mount options for Oracle and Netapp

Jephe Wu - http://linuxtechres.blogspot.com

Environment: Linux x86_64, Oracle 11g 64bit SI(Single Instance) or RAC,  use Netapp as storage for Oracle binary, datafile, rman backup and expdp
Kernel 2.6, OL 5.8 and OL 6.3

Objective: the recommended NFS mounting options


Steps:

Please refer to Mount Options for Oracle files when used with NFS on NAS devices [ID 359515.1]

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options [ID 781349.1]

and Netapp doc ID:
What are the mount options for databases on NetApp NFS?


Mount options for binary (exact order is important!)
RAC: rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600, actimeo=0
Netapp RAC: rw,bg,hard,rsize=32768,wsize=32768,vers=3,actimeo=0, nointr, suid, timeo=600, tcp

SI:    rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp
Netapp SI: rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600, tcp


For datafiles:
RAC: rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600
Netapp RAC: rw,bg,hard,rsize=32768,wsize=32768,vers=3, actimeo=0, nointr, suid, timeo=600, tcp

SI:    rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp
Netapp SI: rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr, timeo=600, tcp

Mount options for CRS voting disk and OCR [RAC]
RAC: rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,noac,vers=3,timeo=600,actimeo=0
Netapp RAC: rw,bg,hard,rsize=32768,wsize=32768,vers=3,actimeo=0, nointr, suid, timeo=600, tcp

Note: 
For RMAN backup sets, image copies, and Data Pump dump files, the "NOAC" mount option should not be specified - that is because RMAN and Data Pump do not check this option and specifying this can adversely affect performance.

Due to Unpublished bug 5856342, it is necessary to use the following init.ora parameter when using NAS with all versions of RAC on Linux (x86 & X86-64 platforms) until 10.2.0.4. This bug is fixed and included in 10.2.0.4 patchset.
filesystemio_options = DIRECTIO

NOTE:  As per Bug 11812928, the 'intr' & 'nointr' are deprecated in UEK kernels, as well as Oracle Linux 6. It is harmless to still include it, you will get a notice..

NFS: ignoring mount option: nointr.



Variable Details: 

OptionDescription
hard Generate a hard mount of the NFS file system. If the connection to the server is lost temporarily, Oracle continues to retry the connection until the NAS device responds. 
bgTry to connect in the background if connection fails. 
proto=tcp(or tcp on Linux)Use the TCP protocol rather than UDP. TCP is more reliable than UDP. 
vers=3(or nfsvers=3 on Linux)Use NFS version 3. Oracle recommends that you use NFS version 3 where available, unless the performance of version 2 is higher. 
suid Allow clients to run executables with SUID enabled. This option is required for Oracle software mount points. 
rsize, wsize The number of bytes used when reading or writing to the NAS device. A value of 8192 is often recommended for NFS version 2 and 32768 is often recommended for NFS version 3. 
nointr (or intr)Do not allow (or allow) keyboard interrupts to kill a process that is hung while waiting for a response on a hard-mounted file system. 
noac
actimeo=0
Disable attribute caching. (a combination of sync and actimeo=0)
disable attribute caching on the client





How to Limit data warehouse DB query to run more than one hour


Jephe Wu - http://linuxtechres.blogspot.com

Objective: to limit a particular schema/user to run not more than one hour
Environment: Oracle 11.2.3.0 enterprise edition 64bit on AIX 7.1


Method 1: Require Oracle Enterprise Edition  - use OEM DB console

1. create a Oracle database user/schema
sqlplus / as sysdba
sql> create user user1 identified by oracle;
sql> grant connect,resource to user1

2.  use Enterprise Manager DB Console, server tab, Consumer Groups

create a special group called 'test'. Then click user button to add 'user1' to this group.

3. go to 'Consumer Group Mappings', this is critical, select 'Oracle user', click on button 'Add Rule for Selected Type', then choose user 'user1'.

4. click on Plan, create a plan called 'myplan', besides 'Other_groups' group, add user 'test' into this plan. Make 'Execution Time Limit(Sec) as 3600, and choose 'Cancel SQL' as action.

5. activate plan 'myplan', only one of the plans can be activated anytime.

Note: if you need to modify 'myplan', you need to deactivate plan first.

6. double check settings from command line

sqlplus test1
SQL> select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

USERNAME       RESOURCE_CONSUMER_GROUP  COUNT(*)
------------------------------ -------------------------------- ----------
1
DBSNMP       OTHER_GROUPS 3
SYSMAN       OTHER_GROUPS 7
SYS       OTHER_GROUPS 1
      _ORACLE_BACKGROUND_GROUP_ 21
USER1       TEST 1

6 rows selected.

Note: you should see user1 is tied to 'test' resource group, not 'other_groups'.

7. testing (you can use 1 sec for testing purpose , instead of 3600s)

sqlplus test1
SQL> select count(*) from dba_segments, dba_extents, dba_tables;
select count(*) from dba_segments, dba_extents
                     *
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

Method 2: Require Oracle Enterprise Edition - use resource manager SQL script CLI, refer to http://www.pythian.com/news/2740/oracle-limiting-query-runtime-without-killing-the-session/


[oracle@oratest bin]$ more resman.sh
#!/bin/sh

sqlplus / as sysdba @resman.sql

sleep 2

sqlplus jephe/jephe @resman2.sql

[oracle@oratest bin]$ more resman.sql
set serverout on size 5555
--
-- first remove an existing active plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
--
-- delete any existing plan or group
-- we have to create a pending area first
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
begin
  dbms_resource_manager.create_pending_area();
  --
  -- we need a consumer group that maps to the desired oracle user:
  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value => 'JEPHE',
    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'
  );

  -- and we need a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'LIMIT_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );

  -- now let's create a plan directive for that special user group
  -- the plan will cancel the current SQL if it runs for more than 120 sec
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>3600,  # adjust this to your case
    SWITCH_ESTIMATE=>false
  );

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
COMMENT=>''
  );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

end;
/

exec dbms_resource_manager_privs.grant_switch_consumer_group('JEPHE','GROUP_WITH_LIMITED_EXEC_TIME',false);

exec dbms_resource_manager.set_initial_consumer_group('JEPHE','GROUP_WITH_LIMITED_EXEC_TIME');

-- to enable it:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;
-------------------------
exit;

[oracle@oratest bin]$ more resman2.sql
select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;
select count(*) from dba_segments, dba_extents,dba_tables;
exit;

[oracle@oratest bin]$ more remove_all.sql
set serverout on size 5555
--
-- first remove an existing active plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
--
-- delete any existing plan or group
-- we have to create a pending area first
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');

Method 3:  use 'alter system kill session' to kill the entire session, not query itself, doesn't require Oracle Enterprise Edition


# cat  /home/oracle/scripts/limit_exec_time.sh
#!/usr/bin/bash

# Variables
export PATH=/usr/bin:/usr/local/bin:/usr/ccs/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin/
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=oradb

ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

export PREPATH=/home/oracle/scripts

sqlplus / as sysdba @/home/oracle/scripts/query_exec_time.sql
sleep 1
grep alter $PREPATH/limit_exec_time.sql 
if [ $? -eq 0 ];then
sqlplus / as sysdba @$PREPATH/limit_exec_time.sql > $PREPATH/result.txt 2>&1
mail -s "session killed" jwu@domain.com < $PREPATH/result.txt 
fi



oracle@oradev1 (oradb)> cat query_exec_time.sql 
set head off feedback off echo off term off pagesize 0  linesize 32767 trimsp on tab off;
spool /home/oracle/scripts/limit_exec_time.sql
select 'select sid,sql_text from v$sqltext , v$session where v$sqltext.address = v$session.sql_address and sid in ' || sid || ';' from v$session where status='ACTIVE' AND username ='JEPHE' and last_call_et/120 > 1;
select 'alter system kill session ''' || sid ||','|| serial# || ''';' from v$session where status='ACTIVE' AND username ='JEPHE' and last_call_et/1800 > 1;
select 'exit;' from dual;
spool off;
exit;


* * * * *  /home/oracle/scripts/limit_exec_time.sh 

Note: above scripts will detect user 'JEPHE' , kill the session which run statement more than 1800s (half hour).


References:

1.  v$session explanation

LAST_CALL_ET NUMBER If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

2. long running sessions




4. Should Sessions be Killed in OS or Using Alter System Kill Session? [ID 161794.1]
Do not kill the sessions at the OS level.
Use ALTER SYSTEM KILL SESSION 'sid, serial#';   

This acts in the following manner:

1. It terminates a session, rolls back ongoing transactions, releases all 
session locks, frees all session resources.

2. If the session is performing some activity that must be completed (eg 
waiting for a reply from a remote database or rolling back a transaction), 
Oracle waits for this activity to complete, kills the session then returns 
control.

3).If the wait lasts for 60 seconds then Oracle marks the session to be killed, 
and returns control with a message that the session is marked to be killed. It 
then gets killed when the activity is complete.

and 

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.'

6. search 'cancel long run query' in oracle support

Method 4: use event to simulate ^C operation to cancel SQL only, not entire session. It will cancel the whole session of PL/SQL, not inside individual statement. For normal scipts, it will cancel individual statement.

*/5 * * * * /home/oracle/bin/cancel_query/limit_exec_time.sh > /dev/null 2>&1


oracle@ora1 > cat /home/oracle/bin/cancel_query/limit_exec_time.sh
#!/usr/bin/bash
# purpose: detect if there's any statement running more than 1 hours under user FINANCE, cancel those SQL statementes if any.
# Variables
export PATH=/usr/bin:/usr/local/bin:/u01/app/oracle/product/11.2.0/bin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=PROD
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

export PREPATH=/home/oracle/bin/cancel_query
rm -f $PREPATH/limit_exec_time.sql
sqlplus / as sysdba @$PREPATH/query_exec_time.sql
sleep 1
fgrep -q ',' $PREPATH/limit_exec_time.sql 
if [ $? -eq 0 ];then

cd $PREPATH

while read line
do 
SID=`echo $line | awk -F',' '{print $1}'`
SERIAL=`echo $line | awk -F',' '{print $2}'`
sed -e "s#SID#$SID#g" -e "s#SERIAL#$SERIAL#g" template.sql > 1.sql
sqlplus / as sysdba @$PREPATH/1.sql >/dev/null 2>&1
mail -s "query cancelled" jephe@domain.com < $PREPATH/limit_exec_time.sql
done  < limit_exec_time.sql

fi


oracle@ora1 > cat query_exec_time.sql 
set head off feedback off echo off term off pagesize 0  linesize 32767 trimsp on tab off;
spool /home/oracle/bin/cancel_query/limit_exec_time.sql
select sid ||','|| serial# from v$session where status='ACTIVE' AND username ='FINANCE' and last_call_et/3600 > 1;
spool off;
exit;


$ cat /home/oracle/bin/cancel_query/template.sql


DECLARE
  l_status  v$session.status%TYPE;
BEGIN

  dbms_system.set_ev( SID, SERIAL, 10237, 1, '');

  LOOP
    SELECT status INTO l_status FROM v$session
      WHERE sid = SID and serial# = SERIAL;
    EXIT WHEN l_status='INACTIVE';
  END LOOP;

  dbms_system.set_ev( SID, SERIAL, 10237, 0, '');
END;
/
exit;


8. References:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm001.htm
http://www.pythian.com/news/2740/oracle-limiting-query-runtime-without-killing-the-session/
http://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
Oracle University course materials

How to quickly use vnc to install Oracle database

Jephe Wu - http://linuxtechres.blogspot.com

Objective: use simplest/fastest way to install Oracle database


Steps:

1. read http://www.oracle.com/technetwork/articles/servers-storage-admin/ginnydbinstallonlinux6-1845247.html  for how to install Oracle on OL6

For OL 5, the package is oracle-validated rpm. and please refer to
http://www.oracle.com/technetwork/articles/servers-storage-admin/ginnydbinstallonlinux-488779.html

2. install vnc-server for OL5 and tigervnc-server for OL6, and install xterm package so that we can have a xterminal window after vnc

Note: xterm rpm package doesn't depend on X11 server, it requires a few tools and fonts package.

If you didn't choose 'X Window System' during installing Oracle Linux 6 or RHEL 6, you have to install xorg-x11-twm package also for having window manager in vnc for dbca to work.

3. run 'vncserver' after login as 'oracle' user

4.  run 'vncpasswd' to set vnc password for 'oracle' user

5. run installer from xterm terminal windows after vnc into the server

6.  run 'dbca', 'emca' etc to create database and Enterprise Manager database control if necessary.

7. make sure /etc/hosts has correct ip address and hostname entry, then run the following to create EM.

emca -config dbcontrol db -repos recreate


How to setup efficient rman backup in Oracle database 11g


Jephe Wu - http://linuxtechres.blogspot.com

Environment:  Oracle database 11.2.0.3 Enterprise edition 64bit.
Objective: create rman backup script to backup database plus archivelog and keep 3 days.


Steps/scripts:

1. Environment variables
[oracle@db01 fast_recovery_area]$ more /usr/local/bin/oraset 
export PATH=$PATH:/u01/app/oracle/product/11.2.0/db_1/bin:/home/oracle/bin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' 
export ORACLE_SID=orcl

ORAENV_ASK=NO
. /usr/local/bin/oraenv
unset ORAENV_ASK

2. cronjobs 
[root@db01 bin]# more /etc/cron.d/rman 
#!/bin/sh
MAILTO=jephe.wu@domain.com
0 23 * * * oracle /home/oracle/bin/rman.sh

3. scripts
[root@db01 bin]# more /home/oracle/bin/rman.sh

#!/bin/bash
. /usr/local/bin/oraset
MAIL_LIST='jwu@domain.com'

export TIMESTAMP=`date +%Y%m%d%H%M`
export BACKUP_DIR=/u01/orarman
export LOG=$BACKUP_DIR/rman_full_${TIMESTAMP}.log
export week=`date +%w`

find $BACKUP_DIR/rman_full*.log -type f -mtime +7 -exec rm -f {} \;

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 # this is important, otherwise the next line $ORACLE_HOME/bin/rman will be invalid.

$ORACLE_HOME/bin/rman log=${LOG} <<EOF

connect target /
set echo on;
show all;
report unrecoverable;

# refer to http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmconc3.htm for explanation
# Run CROSSCHECK to change the status of these files to EXPIRED and then run DELETE EXPIRED to delete the records from the RMAN repository
# To ensure that data about backups in the recovery catalog or control file is synchronized with actual files on disk or in the media management catalog, perform a crosscheck. 
# The CROSSCHECK command operates only on files that are recorded in the RMAN repository.
# Update outdated information about backups that disappeared from disk or tape or became corrupted, maybe due to umount a partition or something.
# Update the repository if you delete archived redo logs or other files with operating system commands
# crosscheck command itself doesn't delete record, you must use delete command to remove the record for the expired file from the repository
crosscheck backup;
delete force noprompt expired backup;

crosscheck copy;
delete force noprompt expired copy;

crosscheck archivelog all;
delete noprompt expired archivelog all;

use CONTROLFILE_RECORD_KEEP_TIME=7 by default to record those backup history 

# You can specify that DELETE should remove backups that are EXPIRED or OBSOLETE. 
# if you run DELETE EXPIRED on a backup that exists, RMAN issues a warning and does not delete the backup. You can override this behavior and delete the backup by running DELETE FORCE.

# Configure environment, redundant to run each time, but ensures set correctly.
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE BACKUP OPTIMIZATION CLEAR;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT='/u01/orarman/%n_%U.bu' MAXPIECESIZE 20G;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
Note: The Retention Policy applies to Backups. It does not apply to ArchiveLogs.
The ArchiveLog Deletion Policy applies to ArchiveLogs. It does not apply to Backups.
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/orarman/%n_%F';

# Backup datafiles, archive redo logs, and control file
backup as compressed backupset incremental level=0 database plus archivelog;
or
backup database plus archivelog;

# for backup ... delete [all] input if any
# If you specify DELETE INPUT (without ALL), then RMAN deletes only the specific files that it backs up. 
# If you specify ALL INPUT, then RMAN deletes all copies of the files recorded in the RMAN repository.


# Always use DELETE command within RMAN to remove RMAN backups, rather than an operating system or media manager utility or command
# Otherwise, the RMAN repository can contain records of backups that are no longer available for use in restore operations.

delete force noprompt obsolete;   # only delete obsolete backups according to retention policy, not archivelogs.
delete noprompt archivelog all backed up 1 times to device type disk;   # only for archivelog, not for backups.

# backup controlfile to trace
sql 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE';
or
sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ''/u01/orarman/controlfile.backup.${week}.sql'' reuse";
sql "CREATE PFILE=''/u01/orarman/spfile.backup'' FROM SPFILE";


# now do a trial restore to see if all is ok
restore database validate check logical;
restore controlfile to '/tmp/' validate check logical;
restore spfile validate check logical;
restore archivelog from time 'sysdate-5' validate check logical;

Note: since we used the recovery windows as 5 days in the beginninig, so we use use 'sysdate-5' here.

# now do a checkup for running database
validate database include current controlfile plus archivelog;
----------
For 10g, use below to validate, 10g doesn't support 'validate database' command to check running database.

restore database validate check logical;
restore archivelog from time 'sysdate-2' validate;
restore controlfile validate;
restore spfile validate;
-----------------------------------


# report 
report unrecoverable;

Note: for datawarehouse, it's critical there's no nolog operation during rman backup period.
#report schema;
#list backup summary;
#list backupset;
#list backup of controlfile;
exit;
EOF

if [ $? -ne "0" ]; then
   mailx -s "rman full backup for `hostname` failed" $MAIL_LIST < $LOG
else
   mailx -s "rman full backup for `hostname` successful" $MAIL_LIST < $LOG
fi

2. archivelog retention policy and auto deletion

Based on page http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm  for archivelog retention.

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Only logs in the flash recovery area can be deleted automatically by the database. For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs when additional disk space is required. You can manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

The archived redo log deletion policy is configured to NONE by default. In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

You can use the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP integer TIMES TO DEVICE TYPE command to enable an archived log deletion policy. This configuration specifies that archived logs are eligible for deletion only when the specified number of archived log backups exist on the specified device type.

Refer to http://www.fatihacar.com/blog/delete-archivelog-using-rman-in-oracle/ as follows:

Archivelog List Commands
RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;
Archivelog Delete Commands
RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

RMAN>delete noprompt archivelog all backed up 1 times to device type disk;



3. how to use rman to restore archive log

RMAN> run {
set archivelog destination to ‘/tmp’;
restore archivelog from logseq=6 until logseq=7;
}

#######
How to check datafile block corruption?

rman> backup validate check logical datafile 3;
rman> backup validate check logical database;

The CHECK LOGICAL option checks for both PHYSICAL and LOGICAL Block corruptions

sql> select * from v$database_block_corruption;

References:
1. How to identify all the Corrupted Objects in the Database with RMAN [ID 472231.1]
2. Master Note for Handling Oracle Database Corruption Issues [ID 1088018.1]
3. How to Check Archivelogs for Corruption using RMAN [ID 377146.1]

4. 



If you simply want to check for corruption but not actually create a backuppiece then use the 'validate' option:
RMAN>backup validate archivelog all;
4. How To Check (Validate) If RMAN Backup(s) Are Good [ID 338607.1]

Example: Refer to Oracle support:

11.2 RMAN Block recovery syntax
=========================

In 11.2 you can use RMAN to:

1) validate all database files and archived redo log files for physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL
  DATABASE ARCHIVELOG ALL;

2) to check individual data blocks, as shown in the following example:

VALIDATE DATAFILE 4 BLOCK 10 TO 13;

3) validate backup sets:

VALIDATE BACKUPSET 3;

You specify backup sets by primary key, which is shown in the output of the LIST BACKUP command.


The following RMAN command recovers the corrupted blocks:


1) recover all corrupted blocks reported in v$database_block_corruption

RMAN> RECOVER CORRUPTION LIST;

2) recover individual blocks, see eg:

RMAN> RECOVER DATAFILE 1 BLOCK 233, 235 DATAFILE 2 BLOCK 100 TO 200;