Pankaj Chandiramani's Blog

Does this sounds as a RAC topic ......yes it was .....but now in Oracle 10g it is possible to use the Oracle Clustersoftware to protect single instance databases .

OTN has a nice sample code on how to's for a cold failover for a single db instance .

click here to download it

It has sample code + witepaper to get you going .The best part is that it covers couple of case studies also .

Few days back i created a RMAN catalog for our Production RAC env which has a 8-node cluster , sharing my experience on the same ........

First we need to understand why we need a Catalog .....
To have a better Back up solutions for large no of db's ; Backup policy is easy to manage  ; Catalog offers enterprise-wide repository ; Reporting is easier ...etc

Basic Architecture shows how this is implemented ....refer below pic

So we will get right to the work & do teh installation & config as below :

Creating a RMAN Catlog is a 3 phase process :
    Create DB that will contain catalog
    Create User , Table space etc
    Create Catalog

Step 1 :Create DB that will contain catalog
Here is what i did :
    Instal 10gR2(without the db ) -> Upgrade to 10.2.0.2
    ->Create a DB
As we were to ues this as just a RMAN Catalog , we created a custom db .

Step 2: Create User , Table space etc
We created a table space for Rman called TRMAN
eg : CREATE SMALLFILE TABLESPACE "TRAMAN" DATAFILE '/u02/oradata/T_02.dbf' SIZE 2000M REUSE , '/u02/oradata/T_01.dbf' SIZE 2000M REUSE LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

We then created a USER called Rman with defalut table space TRMAN & unlimited quota
eg : CREATE USER RMAN PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "TRAMN" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "CATTBS" ACCOUNT UNLOCK

Then we gave the required priviledges
GRANT "CONNECT" TO RMAN
GRANT "RECOVERY_CATALOG_OWNER" TO RMAN

Step 3 : Create Catalog

Connect to RMAN & create a catalog like below :
RMAN> connect catalog rman/oracle
RMAN>create catalog TRAMN (tablespacename)

So Rman catalog is created .................
No to register any DB into this we use the below command

./rman target / catalog rman/oracle

RMAN> register database ;

will cover exp + backup details in some of my next posts ........

The Countdown Has Begun to July 11, 2007, when 11g DB will be here :)
Its much awaited release as it has some new feature improvements in high availability, performance, scalability, manageability and diagnosability.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665

Administring OCR
We will see how OCR(Oracle cluster Registry) backup & recovery is done .

Backup
Oracle clusterware automatically creastes a OCR backup every 4 hrs & retains the last 3 backups . Actually the CRSD process creates & manages the backup for each full day & a weekly backup at nd of the week .
Default backup Location : $CRS_HOME/cdata/$clustername

Other than the automated backup , you can export the content any time you want to a file .
eg : $ ocrconfig -export emergency_export.ocr

You can see the list of ocrbackup by using :
$ ocrconfig -showbackup

As the backup directory is default , you can change the dir by using below command
$  ocrconfig -backuploc <directory>

Restore
OCR can be restored (if you have a backup ) be below command

NOTE: Should you need to restore, make sure all CRS daemons on all nodes are stopped.

To perform a restore, execute the command:

$ cd CRS_Home/cdata/crscluster
$ ocrconfig -restore  week.ocr

If you had exported using the above command & want to resore , then you can use import
IMPORTANT: Importing a backup when CRS daemons are running will only corrupt OCR.  

$ ocrconfig -import emergency_export.ocr

If anything is wrong than you can use the OCRDUMP comand to dump all info to a file & check
$ ocrdump OCR_DUMP

Also you can use :

$ ocrcheck 
to check for the stats of OCR 

Backing Up and Recovering Voting Disks

What is a voting disk & why its needed ?
The voting disk records node membership information. A node must be
able to access more than half of the voting disks at any time.

For example, if you have seven voting disks configured, then a node must
be able to access at least four of the voting disks at any time. If a
node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster.

Backing Up Voting Disks

When to backup voting disk ?

1.       After installation
2.       After adding nodes to or deleting nodes from the cluster
3.       After performing voting disk add or delete operations

11g for linux is available for download @OTN from here.
Read all abt the new features for HA , Db Replay   etc from http://www.oracle.com/technology/products/database/oracle11g/index.html

I wrote this paper to describes the process & benefits of applying patches using the EM.
Case : RAC - 3 node 10.2.0.3
Patch : CPU July 2007
Method : Rolling patch , zero downtime , using Deployment procedures in EM.

You can access the paper here on OTN.
or by copying the below url :
http://www.oracle.com/technology/products/oem/pdf/saving%20time%20and%20labor%20on%20oracle%20patching%20with%20em%20provisioning%20pack%20-%20a%20case%20study%20with%20oracle%20internal%20it.pdf

Other interesting white papers on EM are also listed here .

Hi , Recently i installed 11g db on one of my systems ,as it comes with APEX i thought to move my apex app(which were in 10g) to the same .........when i moved my apps , i got everything working but mail .......it gave error :ORA-24247: network access denied by access control list (ACL)

The issue is with ACL as by default, the ability to interact with network services is disabled in Oracle Database 11g. To enable i needed to run a procedure as shown below .

DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give FLOWS_030000
-- the "connect" privilege if FLOWS_030000 does not have the privilege yet.

SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

-- Before checking the privilege, make sure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'FLOWS_030000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;

DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'FLOWS_030000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'FLOWS_030000', TRUE, 'connect');
END IF;

EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'FLOWS_030000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

Finally got it working :)