Why SQL Server could survive in world of Oracle’s?

I have far less experience and knowledge to carve that out. But I think Seth’s words have an answer, rather unknowingly, for Microsoft and Oracle’s world.

Speaking Seth’s language, I think MS succeeded more with “How much I can get away with?”

Oracle Recovery – When to Use Which Recovery Technique

- Damaged, missing, or inaccessible data files: media recovery
- User dropped a table or commits a major data entry error: point-in-time media recovery, or, best to use flashback drop feature
- User error affected a large set of tables or the entire database: flashback database feature
- Logical errors: perform a TSPITR, or use flashback technique for point-in-time recovery
- Data corruption in a few blocks in a data file or a set of data files: block media recovery. (remember it can be done on partial media as well)
- Unwanted or rogue updates to a table: flashback table feature

Oracle SQL Trace

I came across a good deep dive white paper from an Oracle Development Expert, Cary Millsap. It was about SQL Trace. Well, as yet as a DBA, I have been avoiding SQL Trace without a second thought ever, but it indeed is one of most insightful utilities – not just for Developers but for DBAs (and more so because there are many Dev-DBAs that I know personally). There has to be some reasons why SQLTrace persisted to be a constant offering from Oracle since Oracle 6. From performance standpoint, enabling SQL Trace never hits >5%, which is quite acceptable for amount of time it can save.

Before speaking of anything else, always use following when doing a trace:
- binds => true : so it can tell what values the system is running with
- waits => true : so it can capture extended waits as (i.e. waits caused by OS operations)

Here’s how you can be using Traces:

1. Session Tracing
- dbms_monitor.session_trace_enable (or session_trace_disable). You can mention session_id, serial_num alongside which doest the magic.
- Inside a Trigger, specifically for the fast moving sessions which lasts for a couple of seconds. You can code them with a small trigger definition and with (session_id=>NULL, serial_num=>NULL)
- With, sys_context( ‘<USERENV>’,’<IP>’). It tells the environment details and the IP details
- Or, you can capture the suspicious SQL and can run them inside your own current session.

2. End to End Tracing
- dbms_monitor.client_id_trace_enable (client_id= ‘<IP>’, waits=> true, binds=> true, plan_stat=> ‘ALL_EXECUTIONS’);
- dbms_monitor.serv_mod_act_trace_enable (service_name=> ‘<SERVICE>’, module_name=> ‘<MODULE’, action_name=> ‘<ACTION>’, waits=> true, binds=> true, plan_stat=> ‘ALL_EXECUTIONS’);

3. Database or Instance Level Tracing: For end to end tracing at instance or database level, its important that your code passes back the client, service, module and action details. You can take this as a Development Best Practice, which by the way, is seldom mentioned in any Best Practices document. It can be done by making use of:
1. PL/SQL packages, i.e. 
    – dbms_session,
    – dbms_application_info
- ILO or Instrumentation Library for Oracle, i.e. 
    – OCI_ATTR_CLIENT_IDENTIFIER,
    – OCI_ATTR_SERVICE,
    – OCI_ATTR_MODULE,
    – OCI_ATTR_ACTION

Oracle STARTUP, SHUTDOWN

Set of Commands for Oracle Startup and Shutdown
STARTUP
SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT]
ALTER DATABASE [MOUNT | OPEN | CLOSE | DISMOUNT]
ALTER [DATABASE | TABLESPACE] [BEGIN | END] BACKUP
RECOVER

NOTE:
- SYSOPER role has rights to run all commands above. SYSDBA has CREATE DATABASE as well in addition to privs above.
- Its important to remember that Oracle database and instance are separate and can be independent.

STARTUP
- first, builds the instance in memory (STARTUP NOMOUNT),
- then, mounts it so it can open related config files etc, but not yet accessible to end users(STARTUP MOUNT / ALTER DATABASE MOUNT;), and
- then, Opens it for general usage (STARTUP OPEN / ALTER DATABASE OPEN;)

During startup, Oracle instance tries to read following files, in order of precedence:
%ORACLE_HOME%\database\*.spfileSID.ora
OR, $ORACLE_HOME/dbs/spfileSID.ora        
– binary file, not manually updatable, used for single instance.
%ORACLE_HOME%\database\spfile.ora
OR, $ORACLE_HOME/dbs/spfile.ora            
– binary file, one common file which can be used for all RAC nodes / instance.
%ORACLE_HOME%\database\INITSID.ora
OR, $ORACLE_HOME/dbs/INITSID.ora        
– text file, manually updatable, use it only when needed.
If none of files above exist, instance wont startup.

Interpreting different STARTUP modes:

• MOUNT: Reads parameter and alert log only (SHOW PARAMETR BACKGROUND).
• NOMOUNT: Reads Controlfiles at this point (CONTROL_FILES param), all controlfiles should be available and identical for MOUNT to be successful. Paths to online redo and data files are read, but not used anyhow until OPEN.
• OPEN: attempts are made to read all the online redo and data files. If any of the files is missing or damaged, MOUNT mode is retained. If the last database shutdown was abrupt, then database is attempted to be mount and open by SMON process.

SHUTDOWN is just converse of STARTUP. i.e.
- CLOSED (ALTER DATABASE CLOSE;)
- DISMOUNT (ALTER DATABASE DISMOUNT;)
- instance stopped

Different Shutdown options are as below (in order of precedence for damage it causes):

• NORMAL (called upon as clean): Default. No new user connection is allowed, but all current connection will remain till they are voluntarily logged off.
• TRANSACTIONAL (called upon as orderly): No new user connection is allowed, current connections not running any transactions are terminated, and all connection with ongoing connections will remain connected till transaction completes; once any such transaction complete then session is closed and database is shutdown.
• IMMEDIATE (called upon as consistent): No new sessions are permitted, and all currently connected sessions are terminated. Any active transactions are rolled back, and the database will then shut down
• ABORT: Almost is Power cut for Oracle. The instance terminates immediately. Nothing is written to disk, no file handles are closed, and there is no attempt to terminate transactions that be in progress in any orderly fashion. Against common belief, note that a shutdown abort arent damaging, but some operations shouldn’t be performed after it (e.g. database backup)

Oracle Files Poster

I recently collated details on different Oracle Files, and allied information on each of them, in a pictorial format! It lists down different generally required, and option oracle files, and more details or commentary for each of the files.

Here it is for general use and reference.

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Finding Transactions which are using Rollback Segment

SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk,t.used_urec, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Finding number of Sessions / Users accessing an Object

SELECT S.USERNAME,S.SID,S.SERIAL#,S.SQL_HASH_VALUE,A.OWNER,A.OBJECT,A.TYPE
FROM V$SESSION S, V$ACCESS A
WHERE S.SID = A.SID
AND A.OWNER = ‘&OWNER_NAME’
AND A.OBJECT = ‘&OBJECT_NAME’
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)

Resolving Oracle Issue

Scenario
$ORACLE_SID is not starting up at all. In response to “startup open” command – process gone fine till mount stage, but fails after it, i.e. at open stage (Error within alert logFile: ORA-27072: File I/O error AND ORA-1109 signaled during: ALTER DATABASE CLOSE NORMAL…).

Investigation
A similar issue popped on one of our Oracle server. A further analysis pointed out that it’s because mount point hosting control files for this Oracle Instance had gone out of space. At point of opening up database, Oracle processes were trying to append Control Files (with checkpoint & other allied info) and were failing to do so. This in turn was holding up Oracle Server from coming up online in Open state.

Solution
Add more space to impacted mount point (i.e. $ORACLE_HOME mountpoint) or clean up the existent media to give some breathing space to Oracle instance.
For our specific instance, we decided to move some datafiles from impacted mount point so as to create some head-room. Steps of doneness for same are as enlisted below (assuming you are logged on as oracle user and have verified for correctness of $ORACLE_SID and $ORACLE_HOME):
Step 1. Shutdown the database
SERVERNAME:oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown immediate
Step 2. Move the data files physically
SERVERNAME:oracle$ cp /u02/oradata/<dbname>/DTS_*.dbf    /u05/oradata/
Step 3. Startup the database in MOUNT mode
SERVERNAME:oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup mount
Step 4. Alter the database and specify the new location of EACH datafile
SQL> alter database rename file ‘/u02/ oradata/<db_name>/users01.dbf’ to ‘/u05/oradata/users01.dbf’;
Step 5. Delete the datafiles from troubled mount point to get space created
SERVERNAME:oracle$ rm /u02/oradata/<dbname>/DTS_*.dbf
Step 6. Check if you have some breathing space $ORACLE_HOME mountpoint:
SERVERNAME:oracle$ df –kh
Step 7. Startup the database in OPEN mode and verify for datafile metadata
SERVERNAME:oracle$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup open
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u02/oradata/Data/system01.dbf
/u02/oradata/Data/undotbs01.dbf
/u02/oradata/Data/sysaux01.dbf
/u02/oradata/Data/users01.dbf
/u02/oradata/Data/example01.dbf
/u05/oradata/Data/DST_USER_TBS01.dbf
/u05/oradata/Data/DST_USER_IDX_TBS01.dbf

Vikas Rajput (http://vikasrajput.wordpress.com)

Configuring Oracle Database Console Manually

This article tells about configuring Oracle Enterprise Manager, OEM.
In order to manually configure your Enterprise Manager, connect to database instance, you must complete these tasks:
Verify that the user you are authenticating to the Solaris or Linux system has the Oracle Environment variables set correctly.
env | grep ORACLE*

[oracle@SERVERNAME ~]$ env | grep ORACLE*
ORACLE_SID=XXXXXX
ORACLE_BASE=/app/apps/oracle
ORACLE_HOME=/app/apps/oracle/product/10.2.0/db_1

If the above environment variables are set correctly, we are ready to continue. If the variables are not set correctly, we should modify the users profile to include them before continuing. If we prefer to continue without modifying your profile, you will need to export the variable for this Terminal Session as well as any future sessions.

After verifying that the environment variables are set correctly, we have one more step to verify before configuring the database control repository: make sure that your netservices files are configured correctly.

To verify, you need to check your tnsnames.ora, listener.ora and sqlnet.ora files to verify that XXXXXX is listed.

Creating and Configuring the Database Control Repository
To create and configure your database control repository, run the following commands in your terminal session:

[oracle@SERVERNAME ~]$ emca -repos create

This command will prompt you for your Database SID, Listener port number, Password for your SYS user, and the Password for your SYSMAN user. All of this information should be known prior to running the command. The password for the SYS and SYSMAN users were configured during the database install.

[oracle@SERVERNAME ~]$ emca -repos create
STARTED EMCA at
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Enter the following information:
Database SID: XXXXXX
Listener port number: 1521
Password for SYS user: xxxxxxxx
Password for SYSMAN user: xxxxxxxx
Do you wish to continue? [yes(Y)/no(N)]: y
oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/product/10.2/db_1/cfgtoollogs/emca/XXXXXX/emca_-PM.log.
oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) …
Once your repository is created, you must configure your database control as follows:

[oracle@SERVERNAME ~]$ emca –config dbcontrol db

This command will again prompt you for your Database SID, Listener port number, Password for SYS, DBSNMP, and SYSMAN users. It will also ask you for data to configure email for notifications from the EM.

Starting the Console
Now that you have created your repository and database control, you are ready to start the console. To start the console, initiate the following command at your terminal console:

[oracle@SERVERNAME ~]$ emctl start dbconsole

By launching a browser and going (http://SERVERNAME.DOMAIN.com:5500/em) to this page, I will now be prompted to login to OEM with the Oracle database instance of XXXXXX.
Automatically start and stop the DB-Console:
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
You now have a working repository and database control to access OEM for XXXXXX instance.

Vikas Rajput (http://vikasrajput.wordpress.com)

Finding List of Active Sessions

select sid,status,to_char(logon_time,’dd/mm/yyyy:hh:mi’),osuser,terminal,username,
serial# from v$session
where  status=’ACTIVE’
/

 

Vikas Rajput (http://vikasrajput.wordpress.com)