RMAN IMPLEMENTATION --create catalog repository

Target Database Name: Test
Catalog Database Name: Catdb

Problems:

Creating RMAN repository in catalog database (catdb) & register the target database (test) in repository.
Login into catdb.

Steps 1: Create Repository Tablespace and Schema

SQL> CREATE TABLESPACE RMAN
DATAFILE 'C:\ORACLE\ORADATA\CATALOGDB\RMAN01.DBF' SIZE 200m REUSE
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.

Steps 2: Create Repository Schema
SQL> CREATE USER RMAN IDENTIFIED BY RMAN
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE RMAN
QUOTA UNLIMITED ON RMAN;
User created.


Steps 3: Privileges Assigned to RMAN user

SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;
Grant succeeded.

SQL> GRANT CONNECT, RESOURCE TO RMAN;
Grant succeeded.


Steps 4: Creating the Recovery Catalog

C:\Documents and Settings\raja.baskar>RMAN CATALOG RMAN@CATDB
Recovery Manager: Release 9.2.0.7.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Recovery catalog database Password:
Connected to recovery catalog database
Recovery catalog is not installed
RMAN> CREATE CATALOG;
Recovery catalog created
RMAN> Exit
Recovery Manager complete


Steps 5: Let the Catalog Database keep information in its control file for 40 days. (Optional)

SQL> ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=40;
System altered.

SQL> SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME
NAME TYPE VALUE
Control_file_record_keep_time integer 40

Steps 6: Setup Target Database (Register Database)

·EACH DATABASE TO BE BACKED UP BY RMAN MUST BE REGISTERED.

C:\Documents and Settings\manoj.kumar>RMAN CATALOG=RMAN/xxxx@CATDB TARGET=SYS/xxxx@test
Recovery Manager: Release 9.2.0.7.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
Connected to target database: TEST (DBID=1933922270)
Connected to recovery catalog database

RMAN> REGISTER DATABASE;
Database registered in recovery catalog
Starting full resync of recovery catalog
Full resync complete

RMAN> LIST INCARNATION; -à shows what are the database registers in catalog database..

Drop Database in oracle 9i,10g/11g or How to remove/drop/decommission a oracle 9i,10g,11g

How to remove/drop/decommission a oracle 9i & 10g database?

 Up to oracle 9i, we need to manually cleanup the database physical files on OS level. It is very difficult to clean up and may be a chance to accidently delete the some other db files which is using any other oracle database. So be careful before deleting the database physical files.

Steps:

 1)     Please get the approval from Business/Customer.

2)     Send the Notification to Business/Customer -> We are going to decommission the database.

3)     Raise a Request to Storage/Tape team for TAPE request with details “ To keep the Backup for next 2 years retention period” …Retention Period may be vary based on your business needs and SLA.

4)     Take the list of datafiles/control files/Redo log files and parameter files.

5)     Shutdown the database

6)     Shutdown the listener

7)     Take the complete database backup and make sure this backup is valid backup.

8)     To make sure Storage team take the  tape backup and keep the retention period rightly.. and also get Signoff mail from Storage/Tape team.

9)     Remove the monitoring jobs entry from crontab and also remove the monitoring jobs it is running from third party tool.

10)  Remove the archive log files/datafiles/control files/Redo log files/trace & dump files/backup files and respective DB directories – Be careful before deleting the physical files.

11)  Remove the Backup schedule job details.

12)  Remove the database entry from oratab entry.

13)  Send the final notification to Application/Customer “DB was decommissioned” and also share the TAPE retention details to business/Customer.

14)  Remove the database details from inventory sheet (DB registry/Server registry)

 From Oracle10g onwards, Oracle makes it as physical files removal is very simple. Please follow the below steps for Oracle 10G and 11g databases…

 Steps:

 1)     Please get the approval from Business/Customer.

2)     Send the Notification to Business/Customer -> We are going to decommission the database.

3)     Raise a Request to Storage/Tape team for TAPE request with details “ To keep the Backup for next 2 years retention period” …Retention Period may be vary based on your business needs and SLA.

4)     Take the list of data files/control files/Redo log files and parameter files.

5)     Shutdown the database

6)     Shutdown the listener

7)     Take the complete database backup and make sure this backup is valid backup.

8)     To make sure Storage team take the  tape backup and keep the retention period rightly.. and also get Signoff mail from Storage/Tape team.

9)     Remove the monitoring jobs entry from crontab and also remove the monitoring jobs it is running from third party tool.

10)   Startup the database in restrict mode and give drop database command.

                        SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup restrict mount;

ORACLE instance started.

 Total System Global Area 3221225472 bytes

Fixed Size                  2044072 bytes

Variable Size            1291849560 bytes

Database Buffers         1912602624 bytes

Redo Buffers               14729216 bytes

Database mounted.

SQL>  drop database;

 Database dropped.

 Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

[/fisc/oracle]oracle@:TEST> ps -ef|grep pmon

  Oracle 25411 26322   0 03:07:07 pts/8       0:00 grep pmon

11)  Remove the archive log files, trace, dump files backup files and respective DB directories – Be careful before deleting the physical files.

12)  Remove the Backup schedule job details.

13)  Remove the database entry from oratab entry.

14)  Send the final notification to Application/Customer “DB was decommissioned” and also share the TAPE retention details to business/Customer.

15)  Remove the database details from inventory sheet (DB registry/Server registry)

While running a query is slow compare as yesterday

DBA career mostly face the issue “Yesterday this database Query is running fine. But today we have observed database query response time is very slow compare as yesterday" question raise from Development Team / Client.

Today morning I went to my office through bike at 8.00 AM from my home. I have reached my office at 9.00 AM.It took one hour. When I was started from my office to home at 6.00 PM, it took 2 hours. Why the time different is varying?

DBA point of view first action 

1. Take explain plan for database query.

* If you have any old (yesterday) explain plan for database query, Compare the both.
* Query used which type of optimizer?
* If table’s go to full table scan check the index available or not.
* If index is not available, Create the index & Compare the explain plan.
* If index available Check why my index is not being used?
* Check the Joins Method
* Using HINT to force the optimizer.

2. Compare the table growth today & old one.

3. Check the statistics gathered for the table or not.

* Yesterday the table contains only 1,00,000 records.Yesterday night
   inserted large number of records. (Depend on business).
* Large number of deletion occur in the table.( So fragmentation is there)
* Now the table contains 10,00,000 records.So its takes more time to fetch the data generally.

* Check when did the table is analyzed?
* Suppose the tables were analyzed yesterday morning. But yesterday night large number records        are inserted.So we need to analyze the corresponding objects.
* If statistics is outdated, gather the new statistics using Analyze/DBMS package.

4. Check the connected sessions

* Yesterday 25 Active Users only connected to database.
   Today 100 Active users connected. (Due to this PGA, SGA & I/O used
   large amount of resource are consumed)
* Check the wait event.

5. Check the Physical reads/ Logical reads.

6. Check the hit ratio. (Library cache, Data dictionary & Db Cache)

7. Ensure if any init parameter or memory structure changed.

8. Generated trace file for the particular query. (Using TKPROF)

9. OS level any background process taking more CPU utilization belongs to the database. (RMAN     backup, EXP/IMP, DB Analyze job, Materialized view refresh job etc…)

Recovery without UNDO Tablespace DataFiles

If you encounter a situation where an UNDO datafile is missing from a cloned database you can
actually drop and recreate the UNDOTBS tablespace with

       a) Shutdown immediate
       b) Edit the init.ora to unset "undo_management=AUTO" {Oracle then ignores the setting of     

           "undo_tablespace"}
       c) Startup
       d) Drop Tablespace UNDOTBS
       e) Create UNDO Tablespace UNDOTBS datafile ...
        f) Shutdown
       g) Reset "undo_management=AUTO"
       h) Startup Database.

Corrupt Redo Log Block Header

Today we had 100% storage in the filesystem of the database and archive logs (to many netapp snapshots).

 In the alert.log file we got:

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 24184 change 45850151 time 06/03/2007 03:10:12
ORA-00312: online log 2 thread 1: '/cycprd/oraredo2/redo02_2.log'
ORA-00312: online log 2 thread 1: '/cycprd/oraredo2/redo02.log'
ARC0: All Archive destinations made inactive due to error 354 

After freeing this filesystem, the solution was: 

alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 3;

Checking if it worked: 

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

Difference between Oracle database 9i and 10g & Difference Between Oracle database 10g and 11g

Summary Of Differences Between oracle Database 9i and 10g.

The below Points are completely New in oracle database 10g (Not there in oracle 9i)

• Automatic Storage Management(ASM).

• Automatic Shared Memory Management(ASMM).

• Automatic Database Diagnostic Monitor(ADDM).

• Automatic Workload Repository(AWR).

• Flashback Technologies.

• Data Pump replaces Traditional EXP/IMP.

• Automatic Checkpoint Tunning(FAST_START_MTTR_TARGET).

• Automatic Undo Retention Tunning.

• Introduced Default Permanent Tablespace (USERS).

• Introduced SYSAUX tablespace.

• Streams Technology(STREAMS POOL).

• Introduced Big file Tablespace Option and Rename Tablespace Command.
• Automatic SQL Tunning.

• Temporary Tablespace Group and Default Temporary Tablespace.

• Recovery Manager Enhancements(RMAN).

• DBMS Scheduler Packages and DBMS File Transfer Packages.

The below Points are completely New in oracle database 11g

• Simplified and improved automatic memory management
• New fault diagnosability infrastructure to prevent, detect, diagnose, and help
  resolve critical database errors
• Invisible Indexes
• Virtual columns
• Enhanced security for password-based authentication by enabling use of mixed
  case in passwords.
• Tablespace-level encryption
• Ability to online redefine tables that have materialized view logs
• Be carefull with merge join cartesian explain plan
• Access Control List for accessing UTL_MAIL, UTL_SMTP, UTL_HTTP, UTL_TCP etc