Ayyappa Yelburgi
![Subscribe to Ayyappa Yelburgi feed](http://www.orafaq.com/misc/feed.png)
The Moto behind for Creating this Blog is to share the concepts Of Oracle Database.In This Blog,The Information is gathered from Metalink,Expert's Blog and Oracle Documentaion.It Includes Real Time Scenarios,Oracle9i concepts,Oracle10g Concepts,RAC,Streams,Replication...
Please do visit my blog and post your comments & advice please.ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.comBlogger163125
Updated: 11 hours 57 min ago
Procedure to apply the PROD incremental backup based on the SCN of the DR
Normal
0
false
false
false
EN-IN
X-NONE
X-NONE
MicrosoftInternetExplorer4
ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com283
SRVCTL For RAC
srvctl enable asm -n node_name [-i ] asm_instance_nameUse the following syntax to disable an ASM instance:srvctl disable asm -n node_name [-i asm_instance_name]The above statement is generally required when u want to disable the asm so that asm does not start automatically on reboot.You can also use SRVCTL to start, stop, and obtain the status of an ASM instance as in the following examples.Useayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com16
Log Directory Structure in Cluster Ready Service:
$ORA_CRS_HOME/crs/log--->contains trace files for the CRS resources$ORA_CRS_HOME/crs/init--->contains the trace files of the CRS daemon during startup.Good Place to start with any CRS login problems.$ORA_CRS_HOME/css/log---->The Cluster Synchronization (CSS) logs indicate all actions sych as reconfigurations,missed check -inbs,connects and disconnects from the client CSS listener.In some cases,ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com10
Client want to go for DR test.The changes During DR Test should Not REflect On Prod(Means the changed Data during DR Test should Not reflect on primar
Possibility1:Planned Fail over
Note:Primary Database will be down until DR Test completes
a.Take cold/hot/RMAN backup on primary before DR test b.Take cold/hot/RMAN backup on standby Database before DR test. c.Shutdown Primary Database
d.On standby Database fire the below command sql> alter database activate standby database;
e.Once standby Database is activated,Execute the below command. ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com5
DR-Setup....
Architecture
Database Synchronization Options
Setup No-Data-Divergence
Setup Primary Database
Setup Standby Database
Start Managed Standby Recovery
Protect Primary Database
Cancel Managed Standby Recovery
Activating A Standby Database
Backup Standby Database
Database Switchover
Database Failover
Automatic Archive Gap Detection
Background Managed Recovery
Delayed Redo ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com284
LOG_ARCHIVE_DEST_n
Syntax LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] = "null_string" | ((SERVICE=service | LOCATION=location) [AFFIRM | NOAFFIRM] [ALTERNATE=destination | NOALTERNATE] [ARCH | LGWR] [DELAY[=minutes] | NODELAY] [DEPENDENCY=destination | NODEPENDENCY] [MANDATORY | OPTIONAL] [MAX_FAILURE=count | NOMAX_FAILURE] [QUOTA_SIZE=blocks | NOQUOTA_SIZE] [QUOTA_USED=blocks | NOQUOTA_USED] [ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com7
Data Guard 9i Log Transportation on RAC
PURPOSE-------This article gives an overview about how to create a Data Guard Configuration on Real Application Clusters (RAC). The Configurationyou can find here is for a Physical Standby Database. SCOPE & APPLICATION-------------------You can see which initialisation parameters you have to use / changeand how the Log Transport is organized in a RAC-DataGuard environment. NOTE: The Data Guard ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6
Creating a Data Guard Configuration
1) Ensure the Primary database is in ARCHIVELOG mode: SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /export/home/oracle/temp/oracle/arch Oldest online log sequence 7 Current log sequence 9 SQL> alter database close; Database altered. SQL> alter database archivelog; Database ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com43
Regular Taska Of a DBA
1.Regular Monitoring of The free space in Database.2.Taking logical bakups of important table.3.Checking the locks on the Database.4.Checking the long running queries on ur database5.Analysing the performance of ur Database6.Investigate Wait Statistics 7.Tablespace Usage 8.Ensure Connectivity to Oracle 9.Lock Contention 10.Extent Failure 11.Alert Logs 12.Redo Logs 13.Check if all the instances ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com5
Regular Tasks Of a DBA....
1.Regular Monitoring of The free space in Database.
2.Taking logical bakups of important table.
3.Checking the locks on the Database.
4.Checking the long running queries on ur database
5.Analysing the performance of ur Database
6.Investigate Wait Statistics
7.Tablespace Usage
8.Ensure Connectivity to Oracle
9.Lock Contention
10.Extent Failure
11.Alert Logs
ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com8
procedure for Calculating Database Growth and scheduling in DBMS JOBS....
1.
Create a Table By the Name db_growth...with following details...
Name Null? Type
----------------------------------------- -------- ----------------------------
DAY DATE
DATABASE_SIZE_MB NUMBER
DAILY_GROWTH_MBayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com13
Introduction to Simple Oracle Auditing
IntroductionThis article will introduce the reader to the basics of auditing an Oracle database. Oracle's RDBMS is a functionally rich product and there are a number of auditing alternatives available to the reader. Because auditing Oracle is such a huge subject, doing all of it justice would take an entire book, so this paper will cover the basics of why, when and how to conduct an audit. It ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com8
10 Scripts Every DBA Should Have
I. Display the Current Archivelog Status :ARCHIVE LOG LIST;II. Creating a Control File Trace FileALTER DATABASE BACKUP CONTROLFILE TO TRACE;III. Tablespace Free Extents and Free Spacecolumn Tablespace_Name format A20column Pct_Free format 999.99select Tablespace_Name,Max_Blocks,Count_Blocks,Sum_Free_Blocks,100*Sum_Free_Blocks/Sum_Alloc_Blocks AS Pct_Free from(select Tablespace_Name, SUM(Blocks) ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6
Creating a physical standby Database in Oracle10g
STEPS for creating 10g dataguardprerequisite : 9i dataguard setup knowledgestep1 :Prepare initSID.ora file for primary and standby databases as follow.** STANDBY setup parameters are given in BOLDpart A)**** Production database primary file ****prod.__db_cache_size=125829120prod.__java_pool_size=4194304prod.__large_pool_size=4194304prod.__shared_pool_size=79691776prod.__streams_pool_size=0*.ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com125
Migrating Dictionar managed tablespace to locally managed tablespace
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;*ERROR at line 1:ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrateORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0ORA-06512: at line 1SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')PL/SQL procedure successfully ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com116
Installation of oracle9i/10g on Linux & Solaris
9/10g install on Linux/Solaris Install Oracle 9i Database on Linux RHEL AS 3the following lines can be added to the /etc/sysctl.conf file:kernel.shmmax = 2147483648kernel.shmmni = 128kernel.shmall = 2097152kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000In addition the following lines can be added to the /etc/security/limits.conf file:oracle soft nofile ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com76
Sql Scripts for knowing the transaction status
select * from v$transaction;--All tranasactions + sid and usernameselect s.sid,s.username, t.start_time, t.used_ublk, t.used_urecfrom v$transaction t, v$session swhere t.ses_addr=s.saddr;--All tranasactions + sid and username + first 64 bytes of SQLselect s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_textfrom v$transaction t, v$session s, v$sql sqlwhere t.ses_addr=s.saddrand ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com17
helping-optimizer-to-take-decision-10g.
Give more knowledge to cost based optimizer about object statisticsand make it intelligent.. U remember Knowledge is Power - Aristotle-- Gather stats for a table. Auto sampling and auto number of histogram -- buckets, might not work in previous Oracle version.begindbms_stats.gather_table_stats('&OWNER','&TABLE_NAME',null,DBMS_STATS.AUTO_SAMPLE_SIZE -- put null or percentage for older oracle,falseayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6
Query in DR....
What is Right What is Wrong Found a DBA giving following commands at standby site to switchover while standby database was mounted.SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY*ERROR at line 1:ORA-16139: media recovery requiredSQL> recover managed standby database disconnect;Media recovery complete.SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6
How to create Databas in 2 node RAC ..?
Database creation in RAC environment( 2 instances). can not rely on dbca always CREATE PARAMETER FILE AND PLACE IT IN SHARED LOCATION (O:\TEST\INITTEST.ORA shared location here ) AS FOLLOWING SO THAT BOTH RAC INSTANCES CAN SHARE IT. point to this pfile/spfile from individual instance home/dbs. # START OF INITTEST.ORA *.aq_tm_processes=1*.test1.background_dump_dest='D:\Oracle\admin\test1\bdump'*.ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com8