Login to one of the nodes (i.e. linux1) and disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:
$ sqlplus “/ as sysdba”
SQL> alter system set cluster_database=false scope=spfile sid=’orcl1′;
Sequence of events to bring cluster database back..
1. Start all services using “start -nodeapps”
2. Start ASM instnace using “srvctl start asm -n (node)”
3. Start RAC instances using “srvctl start instance -d (database) -I (instance)”
4. Finish up by bringing our load balanced/TAF service online “srvctl start service -d orcl -s RAC”
Different DBAs have different views on % for analyze. The oracle documentation recommends to carry full analyze on entire database which is not possible for most live systems runnning into terabytes.
In past I had performance issue on my database just over 300 GB. There were one table ORDER_DATA with 400 million rows. That one table pulled entire system down number of times just because it wasn’t properly analyzed and oracle didn’t knew data distribution in the table.
I was struggling to understand where things are going wrong as we were analysing entire table every night but not with hash buckets-histograms and surprisingly in SQL execution it was using a right index.
Exporting and Importing Statistics
Caveat: Always use import/export and use imp/exp utility on schema user who owns tables. I have wasted a week where I was exporting as DBA for XYZ user and then importing into different system under different username.
1. Login as PERFSTAT user on database. It won’t work unless U login as PERFSTAT user
Change a.statistic# to respective value
Stats for Working Hours
select to_char(trunc(b.snap_time),'DD-MM-YYYY') ,statistic#,name, sum(value) from STATS$SYSSTAT A, stats$snapshot B where a.snap_id=b.snap_id and trunc(b.snap_time) > trunc(sysdate -30) and to_char(b.SNAP_TIME,'HH24') > 8 and to_char(b.SNAP_TIME,'HH24') <18 and a.statistic#=54 group by trunc(b.snap_time) ,statistic#,name order by trunc(b.snap_time)
Create RMAN Catalogue steps
• Select database for catalogue.
• Create catalogue owner RMANC, default tablespace TOOLS.
• Grant RECOVERY_CATALOG_OWNER role to RMANC.
• Run the RMAN executable, being the same version as the catalogue database codeset and connect to the catalogue as the RMANC user.
• Issue the CREATE CATALOG command.
Datafiles Backed up during past 24 Hours
SELECT dbfiles||' from '||numfiles "Datafiles backed up", cfiles "Control Files backed up", spfiles "SPFiles backed up" FROM (select count(*) numfiles from sys.v_$datafile), (select count(*) dbfiles from sys.v_$backup_datafile a, sys.v_$datafile b where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from sys.v_$backup_datafile where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from sys.v_$backup_spfile where completion_time > sysdate - 1)
Some cheap/easy ways to Install RAC on inexpensive hardware
Importance of VIP
Real Application Clusters in 10g, however, don’t particularly want you to connect to physical IP address associated with network interface.
Doing so means IP packets are routed to a physical MAC address, so that if that address ever ceases to exist (such as when a server dies), we have to wait for TCP/IP networking protocol itself to work out that packets are undeliverable.
That can take up to 10 minutes, and would mean failover in a RAC can potentially be very slow.
Instead, Oracle wants users to connect to a Virtual IP Address (VIP). That’s an IP address that’s bound to a software-controlled MAC address and since it’s software controlled, the software can arrange for failures to be handled a lot quicker than plain old TCP/IP stack (in seconds, usually).
The VIP for a RAC node is quite often the normal, real IP address plus one so, in my case, that would imply a VIP of 192.168.1.111. I won’t be needing this until it comes time to installing the Oracle software, but it’s good to plan ahead.
Even if check fails you can continue the installation,by configuring vipca seaparately , even if that IP does not exist (no need that you own that ip).
Metadata API (DBMS_METADATA)
Prior to Oracle9i metadata could only be extracted using SQL statements, export utilities and the OCIDescribeAny interface, all of which are limited:
* The SQL approach is limited in that as versions change, so must your scripts.
* Using export with ROWS=N and an import with SHOW=Y will produce the text to allow you to recreate a schema, but it can require considerable editing.
* The OCIDescribeAny interface is limited in the objects it supports.
To solve these issues Oracle9i has introduced the DBMS_METADATA package which can be used to retrieve object definitions as XML or SQL DDL: Read more…
Our preferred v$sqlarea query is to actually report on physical disk I/O per statement execution. Hit ratios are informative but sometimes misleading. Logical I/O is less relevant. If the statement executes 1,000,000 logical I/Os but still only takes less than one-tenth of a second, who cares? It is the total physical I/O that consumes nearly all the time and identifies the potentially incorrect SQL. For example:
System Parameters Purpose: Reports all system parameters, including the hidden ones
I have seen this issue on number of SAP systems. I will compile a procedure I followed here.
undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level
1> What resource is currently in high demand?
select active_session_history.event , sum ( active_session_history.wait_time + active_session_history.time_waited ) ttl_wait_time from v$active_session_history active_session_history where active_session_history.sample_time between sysdate – 60 / 2880 and sysdate group by active_session_history.event order by 2
The easiest approach is to use an Oracle utldtree.sql script. This will be located in the %ORACLE_HOME%\rdbms\admin\ directory.
Please chnage “like” string for filtering results for a specific SQL statement.
set linesize 150;
set pagesize 1000;
BREAK ON hash_value SKIP 1;
spool 1_sql.lst;
select hash_value,to_char(snap_time,’DD-MON-YY HH24:MI:SS’),snap_id,piece, sql_text from STATS$SQLTEXT a, stats$snapshot b where hash_value in (
select hash_value
from STATS$SQLTEXT where sql_text like ‘SELECT%VBELN%KUNNR%ZZCURRENT_OWNER%VBKA%’)
and b.snap_id=a.last_snap_id
order by snap_time,hash_value,piece;
spool off;
Long running operations such as imports and batch processes sometimes fail because the server is unable to allocate more extents for an object. This may be because the object has reached max_extents or there isn’t sufficient room in the tablespace for the object to expand. In previous releases the operation would have to be rerun, possible with some manual cleanup necessary. In Oracle9i operations that would fail due to space allocation problems can be suspended and restarted once the problem is fixed.
* Resumable Mode
* Timeout Period
* AFTER SUSPEND Trigger
* Views
* DBMS_RESUMABLE Package
* Restrictions
exec dbms_java.grant_permission ('DBA_ADMIN', 'java.io.FilePermission','/usr/bin/ps', 'execute'); exec dbms_java.grant_permission ('DBA_ADMIN','java.lang.RuntimePermission','*','writeFileDescriptor' );
Oracle trace events are useful for debugging the Oracle database server.
The following two examples are simply to demonstrate syntax. Refer to later notes on this page for an explanation of what these particular events do.
2 Oracle databases – A & B. Server A is available and need to be replicated on server B. We will use rman to backup datafiles including control file and archive logs.
Tweet