Configure Recovery Catalog

Create a Recovery Catalog database:
As you have already aware of the RMAN Concepts, it is time to create a recovery catalog database to keep all backup/recovery metadata. Lets first create the environment to create a recovery catalog database.

Pre-requisites:
1: It is assumed that oracle software is installed and a normal Oracle database (Ex: RMAND) is already created using dbca.
2: It is assumed that the Listener is up and running.
3: It is assumed that the tnsnames.ora contains RMAND database connection information.

Test the environment in Server:
$ ps -ef grep tns
LISTENER_RMAND process is running

$ tnsping rmand
OK (80 msec)

Create the RMAN user in the database.
$ sqlplus / as sysdba
SQL> Create user rman identifed by rman default tablespace Users and temporary tablespace temp quota unlimited on users;
SQL> Grant create session, connect, resource, recovery_catalog_owner to rman;

Test Connectivity:
Lets do a test whether connectivity works fine or not.

$sqlplus rman/rman@rmand

If it is connected to database then you are all set to create a recovery catalog database.

Create a Catalog database:
$ rman catalog rman/rman@rmand
RMAN> Create Catalog;

The above steps will complete the catalog database creation. RMAN schema will have entire RMAN objects which will hold backup/recovery metadata.

Registering the TARGET database:
As the recovery catalog database is created. It is time to register the target database which needs to be backed up using RMAN.

Pre-requisites:
1: It is assumed that oracle software is installed and a normal Oracle database (Ex: RIPO) is already created using dbca.
2: It is assumed that the Listener is up and running.
3: It is assumed that the tnsnames.ora contains RMAND (Recovery Catalog) and RIPO (Target database) database connection information.

Test the environment in Server:
$ ps -ef grep tns
LISTENER_RMAND process is running
LISTENER_RIPO process is running

$ tnsping ripo
OK (80 msec)

$ tnsping rmand
OK (80 msec)

Set the TARGET database environment:
$ . oraenvORACLE_SID = [RIPO] ? RIPO

Register Target Database:
$ rman target system/manager@ripo catalog rman/rman@rmand

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Where to See whether the target database is registered or not?
Connect to recovery catalog database and execute the below to find whether target database is registered with recovery catalog database or not.

$sqlplus rman/rman@rmand
SQL> select * from rc_database;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
--------- ------------- ------ ----- ----------------- -------------
1 2 2498101982 RIPO 1 15-JAN-04

For RMAN Concepts refer:
http://oracledocaccess.blogspot.com/2009/06/rman-concepts.html

Monitor Oracle Alert Log

Unix Shell Script To Monitor Oracle Alert Log:

#!/bin/ksh.
/usr/local/bin/RACT.env

export machine=`uname -n`
export script_dir=/export/home/oracle/local/scripts
export bdump=/export/home/oracle/admin/RACT/bdump
export flagfile=/export/home/oracle/admin/RACT/bdump/flag.lis

flagoldval=`cat $flagfile`
cat $bdump/alert_${ORACLE_SID}.log wc -l > $flagfile
flagnewval=`cat $flagfile`
NUM=$(($flagnewval-$flagoldval))

tail -$NUM $bdump/alert_${ORACLE_SID}.log awk
'BEGIN {prev="" ; ret=1 }
/^(...-Error)/ { if ( prev !~ /^(...-Error)/ ) { print "" ; print prev;} print $0;ret=0}
{prev=$0}
END { exit ret } '> $bdump/alert_temp10.log

if [ `cat $bdump/alert_temp10.log wc -l` -gt 0 ]
then
(echo "To: mailto:xxy@test.com/nSubject: CRITICAL: RACT: Error in alert log \n\n"; cat $bdump/alert_temp10.log)/usr/lib/sendmail xxy@test.com
else
echo "No Error"
fi

Oracle Database Upgrade Path

Oracle Database Upgrade Path:
The below document in metalink will recommend what are all required patches for upgrade.
Doc ID: 756671.1 : Oracle Recommended Patches -- Oracle Database

The below document is a nice document for database upgrade to 10GR2.
Doc ID: 316889.1 : Complete Checklist for Manual Upgrades to 10gR2

This document will give you all the details regarding database upgrade to any version.
Doc ID: 730365.1 : Oracle Database Upgrade Path Reference List

For the above documents you need to have a account with https://metalink.oracle.com/. The account with Oracle for this site is possible only when you have a valid Customer Service Identification (CSI) number available with you.

RMAN Concepts

Components of the RMAN Environment:
Component:Target database
Description:The control files, datafiles, and optional archived redo logs that RMAN is in charge of backing up or restoring. RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations. The work of backup and recovery is performed by server sessions running on the target database.
Required?:Yes

Component:RMAN client
Description:The client application that manages backup and recovery operations for a target database. The RMAN client can use Oracle Net to connect to a target database, so it can be located on any host that is connected to the target host through Oracle Net.
Required?:Yes

Component:Recovery catalog database
Description:A database containing the recovery catalog schema, which contains the metadata that RMAN uses to perform its backup and recovery operations.
Required?:No

Component:Recovery catalog schema
Description:The user within the recovery catalog database that owns the metadata tables maintained by RMAN. RMAN periodically propagates metadata from the target database control file into the recovery catalog.
Required?:No

Component:Standby database
Description:A copy of the primary database that is updated using archived logs created by the primary database. RMAN can create or back up a standby database. You can fail over to the standby database if the primary database goes down.
Required?:No

Component: Duplicate database
Description:A copy of the primary database that you can use for testing purposes.
Required?:No

Component:Media management application
Description:A vendor-specific application that allows RMAN to back up to a storage system such as tape.
Required?:No

Component:Media management catalog
Description:A vendor-specific repository of information about a media management application.
Required?:No

Component:Enterprise Manager
Description:A browser-based interface to the database, including backup and recovery through RMAN.
Required?No

As you know the above mentioned required components (Target Database and RMAN client) are available as soon as you are ready with your target database. The RMAN client is an executable which comes with the database software installation. Fire the below and you will see whether RMAN is installed or not.

C:\Documents and Settings\sisrath>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 4 11:58:46 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN>

Now when you know how to get ready with the required components you should have a basic idea about how RMAN stores all the backup and recovery related information. The below section will provide a clear idea on this.

Storage of the RMAN Repository in the Control File:
Because most information in the recovery catalog is also available in the target database's control file, RMAN supports an operational mode in which it uses the target database control file instead of a recovery catalog. This mode is especially appropriate for small databases where installation and administration of a separate recovery catalog database is burdensome. The only RMAN feature that is not supported in NOCATALOG mode is stored scripts.

When you do not use a recovery catalog, the control file is the exclusive source of information about backups and copies as well as other relevant information. The control file contains two types of records:
1: Circular reuse records
2: Noncircular reuse records.

Circular Reuse Records:
Circular reuse records contain noncritical information that is eligible to be overwritten if the need arises. These records contain information that is continually generated by the database. Circular reuse records are arranged in a logical ring. When all available record slots are full, the database either expands the control file to make room for a new record or overwrites the oldest record. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter specifies the minimum age in days of a record before it can be reused. By default it is 7 days. SO it is always recommended to have CONTROL_FILE_RECORD_KEEP_TIME with a higher value when no catalog database is used to store as much as backup and recovery metadata information.

Noncircular Reuse Records:
Noncircular reuse records contain critical information that does not change often and cannot be overwritten. Some examples of information in noncircular reuse records include datafiles, online redo logs, and redo threads.

RMAN Repository:
The RMAN repository is the collection of metadata about the target databases that RMAN uses for backup, recovery, and maintenance. Recovery catalog, an external Oracle database in which this information can be stored. The control file has finite space for records of backup activities, while a recovery catalog can store a much longer history. The added complexity of operating a recovery catalog database can be offset by the convenience of having the extended backup history available if you have to do a recovery that goes further back in time than the history in the control file.

There are also a few features of RMAN that only function when you use a recovery catalog. For example, RMAN stored scripts are stored in the recovery catalog, so commands related to them require the use of a recovery catalog. Other RMAN commands are specifically related to managing the recovery catalog and so are not available (and not needed) if RMAN is not connected to a recovery catalog.

The recovery catalog's version of the RMAN repository is maintained solely by RMAN. The target instance never accesses it directly. RMAN propagates information about the database structure, archived redo logs, backup sets, and datafile copies into the recovery catalog from the target database's control file after any operation that updates the repository, and also before certain operations.

The RMAN repository is no different than a normal database. As it holds the most backup/recovery information of all target databases, hence it is required to keep a backup of this database. The backup of this database is always recommended to be taken by using Cold/Hot backup concepts.

Now you know what are all the required components and how RMAN stores the backup/recovery metadata information. Now it is time to setup an environment to do the RMAN backup.

Create Recovery Catalog Database and Register the Target Database:
http://oracledocaccess.blogspot.com/2009/06/configure-recovery-catalog.html

Manage 10G DB Control

Enterprise Manager Grid Control - Version: 10.1 to 10.2
Information in this document applies to any platform for DB Console:

This article provides following instructions on how to manage the db console application used in a single instance 10G database:
1: Create repository and configuration files for the DB Control application
2: Drop repository and configuration files for the DB Control application
3: Recreate the repository and configuration files for the DB Control application

Create DB Control Objects:
Option1: Create the Db Control Configuration Files
Option2: Create the DB Control Repository and Configuration Files

Option1:
For DB Control 10.1.x, run the command: /bin/emca -r
For DB Control 10.2.x, run the command: /bin/emca -config dbcontrol db

Option2:
For DB Control 10.1.x, run the command: /bin/emca
For DB Control 10.2.x, run the command: bin/emca -config dbcontrol db -repos create

Drop DB Control Objects:
Option 1: Delete DB Control Configuration Files using EMCA scripts
Option 2: Delete DB Control Configuration Files Manually
Option 3: Delete DB Control Repository Objects using RepManager
Option 4: Delete DB Control Repository Objects Manually
Option 5: Delete DB Control Configuration Files and Repository Objects using EMCA

Option 1:
For DB Control 10.1.x, run the command: /bin/emca -x
For DB Control 10.2.x, run the command: bin/emca -deconfig dbcontrol db

Option 2:
Remove the following directories from your filesystem:
/
/oc4j/j2ee/OC4J_DBConsole__

NOTE: On Windows you also need to delete the DB Console service:
- run regedit
- navigate to HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
- locate the OracleDBConsole entry and delete it

Option 3:
In both 10g R1 and R2 run: /sysman/admin/emdrep/bin/RepManager -action drop

Option 4:
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> drop usersysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Option 5:
For DB Control 10.1.x:
/bin/emca -x
/sysman/admin/emdrep/bin/RepManager -action drop

For DB Control 10.2.x:
/bin/emca -deconfig dbcontrol db -repos drop

Recreate DB Control Objects:
Option 1. Recreate the DB Control Configuration Files only (leave Repository intact)
Option 2. Recreate the DB Control Configuration Files and Repository

Option 1:
For DB Control 10.2.x, run the command:
/bin/emca -config dbcontrol db

Option 2:
For DB Control 10.2.x, run the command:
/bin/emca -config dbcontrol db -repos recreate

FTP to/from Oracle Support

FTP to/from Oracle Support:

During most of dba issues we deal with Oracle Support for which we need quick communication with Oracle. Somtimes during this support window Oracle Support Team might ask you to upload some files or download some patches which are larger in size and upload facilities in Service Request (SR) might be very slow for that to upload or download the files. Under such circumstances the below can be used to communicate with Oracle Support Team:

External Support FTP site:
Scope and ApplicationUsers of this ftp site should be aware of the following:
1: Customers and Employees should be familiar with an FTP commands or an FTP tool.
2: The following directory structures will be used by all groups in Support:

/ftp/anonymous/support/incoming/

/ftp/anonymous/support/outgoing

3: All files in these directories will be erased every 7 calendar days.
4: The outgoing directory is public domain and therefore nothing security sensitive should be placed here (no passwords etc).

To place a file for Oracle support to get:
Logon to ftp.oracle.com

Userid: anonymous

Password: your_complete_email id (ex: abc.def@xyz.com)

Change your working directory to where the customer will place the files.
cd support/incoming/

Make a new directory for the files by using your SR number
mkdir 12345678.9

change to that directory
cd 12345678.9

switch to bin mode and place the files in the SR directory
bin

put (filename)

exit out of the ftp system
quit

To get a file from Oracle Support:
Logon to ftp.oracle.com

Userid: anonymous

Password: your_complete_email id (ex: abc.def@xyz.com)

Change your working directory to outgoing
cd support/outgoing/

Change your working directory to the SR number
cd 12345678.9

switch to bin mode and get the files in the SR directory
bin

get (filename)

exit out of the ftp system
quit

Optimizer Hints

Oracle10g New Optimizer Hints:
With the 10g database release, there are many new optimizer hints available to control the optimization rules. Here are few for your understanding. Follow the URL and get an idea about the Oracle Hints.
http://www.fast-track.cc/10g_24.htm

Manage a Job

Manage Oracle Jobs:

Create a job using DBMS_JOB Package:

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'DBMS_MVIEW.REFRESH(''TEST.DW_CUSTOMERLIFECYCLE'', ATOMIC_REFRESH => FALSE);'
,next_date => to_date('02/06/2009 02:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE+1) + 2/24 '
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' to_char(x));
END;
/

commit;

Note: Assume in the above process a job is created whose job# is 745.

Alter the NEXT_DATE of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.NEXT_DATE (
job IN BINARY_INTEGER,
next_date IN DATE);
SQL>commit;

Example:
SQL>execute DBMS_JOB.NEXT_DATE(745,to_date('03/06/2009 02:00:00','dd/mm/yyyy hh24:mi:ss'));
SQL>Commit;

Alter the INTERVAL of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.INTERVAL (
job IN BINARY_INTEGER,
interval IN VARCHAR2);

SQL>commit;

Example:
SQL> execute DBMS_JOB.INTERVAL(745, 'TRUNC(SYSDATE+1) + 3/24');
SQL> Commit;

Alter the WHAT of a Job using DBMS_JOB package:
SQL>execute DBMS_JOB.WHAT (
job IN BINARY_INTEGER,
what IN VARCHAR2);
SQL>commit;

Example:
SQL>execute DBMS_JOB.WHAT(745, ‘DBMS_MVIEW.REFRESH(''XXBLIS.DW_CUSTOMERLIFECYCLE'');');
SQL>Commit;

Stop Jobs:
It is not always easy to stop a running job. There are different ways to stop a job:

1: You can change the job_queue_process parameter to 0. This will stop all the jobs running in the database.
2: You can find the server process of the job and kill the server process which will stop the job.
Find the SID from dba_jobs_running and get the server process for the same SID. Kill the server process bi kill -9 and you are all set.

Runing Jobs:
How do you know which job is running on the server. To find the list of jobs running on the server, execute the below:

SQl> select job.log_user, schema_user, what, failures, broken from dba_jobs where this_date is not null;

Failures or Broken Jobs:
How do you know what are all the jobs failed or in broken stage. Execute the below to find the same.

SQL> select job, schema_user, failures, broken, what from dba_jobs where failures>0 or broken ='Y';

Fix Broken Jobs:
When the job is broken how to fix the broken job. To do that run the below:

SQL> execute DBMS_JOB.BROKEN(job,FALSE);
SQL> commit;

Create Materialized View

Create Materialized View:

CREATE MATERIALIZED VIEW TEST.DW_CUSTOMERLIFECYCLE
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH COMPLETE
START WITH TO_DATE('02-Jun-2009 02:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE+1) + 2/24
WITH PRIMARY KEY
AS
SELECT ORGANIZATIONUNITID UPOID,STATUS,EVENTNAME,EFFECTIVEFROM,EFFECTIVETO,
C.CREATEDBY,C.CREATIONDATE,C.LASTMODIFIEDBY,C.lastmodifiedDate
FROM customerlifecycle@repo C, DUAL@repo C;

This creates a materialized view and schedule a job by default and uses a prebuilt table. Hence make sure a table is already created. The materialized view created will do complete refresh as defined in the script.