Ayyappa Yelburgi

Normal 0 false false false EN-IN X-NONE X-NONE MicrosoftInternetExplorer4 ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com283

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

$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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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