Monthly Archives: November 2014

Performing an Exadata Health Check Using exachk


Exacheck is advised to be run as a part of periodic maintenance operations on the exadata. It is strongly recommended to be run before or after any upgrade, configuration change or anychange on the software or hardware. Download the latest version from the oracle support (ID 1070954.1). Query the version like that : oracle@bakuexa1dbadm01:~$ cd […]

AFTER DELETE TRIGGER


EXAMPLE CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) ); We could then use the CREATE TRIGGER statement to create an AFTER DELETE trigger as follows: CREATE OR REPLACE TRIGGER orders_after_delete AFTER DELETE ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN — Find username of person performing the DELETE on […]

How to Start VNC Manually in Solarius 11.1


Become superuser or assume the root role. Start the VNC server. # /usr/bin/vncserver Enter the VNC server password. Password: Verify: New ‘myhost:2 ()’ desktop is myhost:2 Creating default startup script /home/user1/.vnc/xstartup Starting applications specified in /home/user1/.vnc/xstartup Log file is /home/user1/.vnc/myhost:2.log From another machine, run the vncviewer command with the address reported by the vncserver command. […]

Commonly used ILOM (Integrated Lights out Manager) commands for Exadata X4-2 HP Rack Database Machine


Login related commands -> start /SP/console — start the SP-console -> show /SP/sessions — see the currently active sessions -> stop /SP/console — to stop any user session Start and stop system -> start /SYS (start system) -> stop [-force] /SYS (stop system) -> show /SYS (shows the power status) -> reset /SYS (reset host) […]

ORA-03124: two-task internal error


In alert log: SQR 5528) ORACLE OCIStmtExecute error 3124 in cursor 10: ORA-03124: two-task internal error Error on line 1233: (SQR 3723) Problem executing cursor. SQR for PeopleSoft: Program Aborting. Non RAC SQL> ALTER SYSTEM SET “_enable_row_shipping”=FALSE scope=both; System altered. SQL> show parameter _enable_row_shipping NAME TYPE VALUE ———————————— ———– —————————— _enable_row_shipping boolean FALSE For RAC SQL> […]

Archivelog Mode On RAC


ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/ANARDB/archive/’ SCOPE=spfile; ALTER SYSTEM SET log_archive_format=’arch_%t_%s_%r.arc’ SCOPE=spfile; ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile; $ srvctl stop database -d ANARDB STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile; SHUTDOWN IMMEDIATE; srvctl start database -d ANARDB

FIXED_DATE : Set sysdate without changing system clock


An extremely useful command for fixing the sysdate at a particular time for testing purposes. alter system set fixed_date = ’11-NOV-2014′; select sysdate from dual; This fixes the sysdate and time to a date which does not increment ( ie even the time stays the same ) for the instance. To reverse this action and […]

DBMS_STATS Gather Statistics of Schema, Tables, Indexes


Syntax: exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed); Code examples: exec dbms_stats.gather_schema_stats(‘SCOTT’,DBMS_STATS.AUTO_SAMPLE_SIZE); exec dbms_stats.gather_schema_stats(ownname=>’SCOTT’, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE); exec dbms_stats.gather_schema_stats(ownname => ‘SCOTT’, estimate_percent => 25); exec dbms_stats.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’); exec dbms_stats.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’); exec dbms_stats.delete_schema_stats(‘SCOTT’);

Change Scan Port


srvctl status scan_listener SCAN Listener LISTENER_SCAN1 is enabled SCAN listener LISTENER_SCAN1 is running on node srvd2 SCAN Listener LISTENER_SCAN2 is enabled SCAN listener LISTENER_SCAN2 is running on node srvd1 SCAN Listener LISTENER_SCAN3 is enabled SCAN listener LISTENER_SCAN3 is running on node srvd1 lsnrctl status LISTENER_SCAN1 lsnrctl status LISTENER_SCAN2 lsnrctl status LISTENER_SCAN3 crsctl stat res ora.LISTENER_SCAN1.lsnr […]

Move segments from one Tablespace to another:


Move Tables of user PROD_USER like this: Tables + indexes of tables EMP,PRODUCTS,CUSTOMERS into tablespace TBS1. All the other tables + indexes of this user into tablespace TBS2. The Script: set serveroutput on –*********************************************** — (Run the script as DBA user) — Parameters: ————— — user_name : owner to which to move segments — TBS1 […]

deadlock_alert


################################################################### ## deadlock_alert.sh ## ################################################################### #!/bin/ksh . /etc/oracle.profile sqlplus -s <<! oracle/$1@$2 set feed off set heading off spool deadlock.alert SELECT   SID, DECODE(BLOCK, 0, ‘NO’, ‘YES’ ) BLOCKER,               DECODE(REQUEST, 0, ‘NO’,’YES’ ) WAITER FROM     V$LOCK WHERE    REQUEST > 0 OR BLOCK > 0 ORDER BY block DESC; spool off exit ! if [ `cat […]