Thursday, December 30, 2010

scn and checkpoint

Hi,
How the scn and checkpoint are related to each other is described in detail in the below link.This link explain you how oracle perform recovery by using this.Its superve explaination from sandeep.I really like the explaination in the below link.

http://www.dbapool.com/articles/1029200701.html



Best regards,

Rafi.

Wednesday, December 29, 2010

Datapump through Network & ORA-39083

Hi,
Many times we make use of datapump utilities for loading the data from one schema to other schema.We make use of REMAP_SCHEMA parameter provided by the impdp utility but for this we need the dumpfile which is only available when you do the export by using expdp utility,In the below example I have done the import without using dumpfile by using parameter NETWORK_LINK
provided by impdp utility,this is very useful parameter.

When I was doing this import I came across one error
ORA-39083 this usually occurs when OID already exists used by user defined objects type,We can overcome this error by using parameter TRANSFORM=oid:n in oracle 10g rel2 Database(In 10g rel1 we have to recreate type objects).

The steps involved in doing this import is as given below:


Source user:Source_SCHEMA
Target user:Target_SCHEMA

Step 1:
--------

Tablespace check:
-----------------

SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME ;

Note:
Source side total space used =64 mb
Target side free space available=2048 mb
tablespace space available to proceed with import


Step 2:
--------
Create directory for dump file.

create directory IMP_DP_TEST as 'E:\TEST_DIRECTORY';
grant read,write on directory IMP_DP_TEST to system;


Step 3: Create database link from the target schema and connect to the source schema
--------

create public database link LINK_NAME
connect to Source_SCHEMA
identified by PASSWD
using 'CONNECT_STRING'
/
TO verify this database link is working fine:
Target side:Target_SCHEMA

SQL> select *from dual@LINK_NAME;
o/p:
---
x

Step 3:
----------
Use REMAP_SCHEMA to load data from one schema to other schema.
Use TRANSFORM=oid:n to replace old oid with new.
Use NETWORK_LINK to do import through network with the help of Database link you have
created.

impdp Source_SCHEMA/PASSWD REMAP_SCHEMA=Source_SCHEMA:Target_SCHEMA TRANSFORM=oid:n DIRECTORY=IMP_DP_TEST NETWORK_LINK=LINK_NAME LOGFILE=testdata5_29122010.log


Step 4:
--------
Compare the two schemas and verify by using the below queries:

Note The source and target objects(tables,indexes,procedures,functions,packages) should be equal.

SQL>Select count(*) from user_objects;
SQL>Select count(*) from user_tables;

Step5: Once you completed your import it is good practise to compile all your objects
------
For compiling the objects

In Windows: Connect as sysdba
-----------
SQL>@%ORACLE_HOME%/rdbms/admin/utlrp.sql

In Unix:
--------
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Now verify whether there are any invalid objects in the target schema by using the below query:

SQL>select object_name||' '||object_type from user_objects where status='INVALID';

If any invalid objects exists compile them individually

eg:
SQL>alter procedure procedure_name compile;


Hope this help.



Best regards,

Rafi.

Wednesday, December 15, 2010

logon and logoff trigger for tracing user only once after they log on

Hi,
Tracking the user for log on and log off can be done as follows,In this trigger the special part is it will record the user entry only once.The steps which I followed is as follows:

Step 1: Crating user audit table
-------

Note: The below process will only work with sys user only as we are using sys_context so connect as sysdba



Step 1: Crating user audit table
-------
sqlplus
connect sys/manager AS SYSDBA;

create table
stats_user_logon
(
user_id varchar2(30),
osuser varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar2(32),
last_module varchar2(32),
logon_day date,
logon_time varchar2(10),
logoff_day date,
logoff_time varchar2(10),
elapsed_minutes number(8)
)
;
Step 2: Designing a logon trigger

create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
declare
cnt number;
us varchar2(30);
BEGIN
select user into us from dual;
select count(*) into cnt from stats_user_logon where user_id=us;
if cnt > 0 then
UPDATE stats_user_logon SET LOGON_TIME=to_char(sysdate,'hh24:mi:ss'),LOGON_DAY=SYSDATE where user_id=us;
ELSE
insert into stats_user_logon values(
user,
sys_context('USERENV','OS_USER'),
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null,
null,
null,
sysdate,
to_char(sysdate,'hh24:mi:ss'),
null,
null,
null
);
END IF;
END;
/

Step 3:Enable triggers for log on
Alter trigger logon_audit_trigger enable;

Step 4:Tracking the users from the below query:
-------

SQL> select user_id||' '||osuser||' '||logon_day||' '||logon_time
from stats_user_logon;
2
USER_ID||''||OSUSER||''||LOGON_DAY||''||LOGON_TIME
--------------------------------------------------------------------------------
T1 oracle 17-DEC-10 18:05:20
T2 oracle 16-DEC-10 18:49:32
TEST1 osuser1 16-DEC-10 18:58:41
TEST2 osuser2 17-DEC-10 18:51:43

That is why PL/SQL is my favourite programming language.



Best regards,

Rafi.

Friday, December 10, 2010

Scheduling RMAN jobs in windows server

Hi ,
In windows server environment we use scheduler(cronjob in unix environment) to schedule a job.
Note:Please make sure you have enough space in the drives(disks in unix environment) before scheduling the jobs.

The important steps here are as follows:

FULL DATABASE BACKUP(INCREMENTAL LEVEL O):
Taking Database backup weekly(incremental level 0):
1)Create a cmd file(command file):
Let us create a file name backup_weekly_db1.cmd using a text file editor(any editor) write the rman script to take the backup.Suppose I want to take full backup(incremental 0) than the script for taking the weekly incremental 0 backup would be like this,assuming controlfile autobackup is off(default).

backup_weekly_db1.cmd
RMAN>run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
Backup incremental level=0 database tag='complete_backup';
Release channel ch1;
Allocate channel t1 type disk format '\path of taking backup\%d_ctrl_%U';
Backup current controlfile;
Release channel t1;
}

INCREMENTAL BACKUP(LEVEL1,LEVEL2,LEVE3 BACKUP):
2)Create the bat file(Batch file is for batch job execution by scheduler)
Let us create a batch file backup_weekly_db1.bat(This batch file we are creating for executing this file through schedule(as cronjob in unix environment).
The script is:

backup_weekly_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_weekly_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log

3)Use scheduler for scheduling job:

Go to control panel->scheduler tasks->Add scheduler task->command prompt->Perform this task->weekly->choose day and timings when you want to run this job weekly->specify administrator user and password->finish


Taking Database backup Daily(incremental level 1)

All the above steps remain the same,but the scripts and scheduler task option changes slightly:

1)
backup_daily_db1.cmd
run{
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U';
backup incremental level=1 database tag='Incremental_Backup';
Release channel ch1;
}

2)
backup_daily_db1.bat
rman target sys/db1@db1
cmdfile=\Path of cmd file\backup_daily_db1.cmd
log=D:\Path of log keeping\backup_complete_db1_%date:~4,2%_%date:~7,2%_%date:~10%.log

3)
Scheduler option choose daily and timings when you want to schedule the job

ARCHIVE LOGS BACKUP: AND CONTROLFILE BACKUP
Finally,It is good practice to take archives backup,It can be done as follows:

TESTDB_archives_backup.cmd:

Run{
Crosscheck Archivelog All;
Sql 'alter system archive log current';
Allocate channel t1 device type disk;
Delete Noprompt Copy of Archivelog All Completed before 'SYSDATE-1';
Backup Format 'D:\oracle10g\oradata\TESTDB\Archives\%d_ARCH_%U_%T' Archivelog All;
Release Channel t1;

Allocate channel ch1 device type disk FORMAT 'D:\oracle10g\oradata\TESTDB\Archives\%d_CTRL_%U_%T';
Backup Current Controlfile;
Release channel ch1;
}

TESTDB_archives_backup.bat:

set ORACLE_SID=TESTDB
rman target sys/TESTDBDBA@TESTDB cmdfile=D:\backup\TESTDB_archives_backup.cmd log=D:\backup\Log\backup_archives_TESTDB_%date:~4,2%_%date:~7,2%_%date:~10%.log


Hope this helps.


Best regards,

Rafi.

Thursday, December 2, 2010

Why to choose ORACLE as Career

Hi,
There are so many people either fresher/experienced,they all have one question that is common,specially when we become graduate and we have to choose our career in any one domain/technology,So answer to these queries is very simple and described below:

Note:The below details is only helpful when you have the passion to build your career in Oracle field/technology

Oracle has a wide product range; business-database, middleware, business intelligence, business applications, collaboration etc. ->

http://otn.oracle.com/products but companies’ primary expertice is with their database product -> http://otn.oracle.com/database

I - As a Professional,I choose Oracle because

A- This community has no support or information problem; tahiti.oracle.com metalink.oracle.com asktom.oracle.com otn.oracle.com blogs.oracle.com and forums.oracle.com are great places information is shared, with high quality. Community leaders like Thomas Kyte, Jonathan Lewis ,Steven Feuerstein and
Aman sir which always guide for you with their experiences available through their books, blogs and articles.

B- There are lots of supplied packages within database; nearly eveything is thought and solved simply for the developers with these packages; 'DBMS%' 'UTL_%' 'OWA_%' 'SDO_%' 'HTMLDB_%' 'CTX_%' etc. With them you will be more time to market and they have no maintainance or support problem, really reliable applications -> http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/toc.htm

C- As Mr. Kyte says; "all databases are fundamentally different and, when designing your application, you must approach each as if you never used a database before. Things you would do in one database are either not necessary, or simply won’t work in another database."

Oracle with its undo mechanism never blocks readers and locking is row level, this makes Oracle a high concurrent database system. Where as a simple update query does a lot behind and in this system your primary approach must be reducing logical I/O. In this high-concurrent system readers do not block writers and writers do not block readers.

Oracle allows you a high degree of read consistency, if you start a long running query you do not see the changes of the other users until your query ends. Also the system protects you from phantom-uncommited data changes. This is also handled with undo mechanism.

In Oracle you will learn that concurrency and consistency can be achieved very fast and correct, every time -> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i13945

D- As Mr. Lewis says: "Oracle is not just a bucket into which you can throw your data, even for a basic set of documentation you will find ~10,000 pages of documentation you will be reading. The error messages alone ~1,000 page." This is a complex system with lots of beautifull features, just like an operating system.

E- Oracle has a high degree of recoverability, Oracle will recover itself automatically to the moment of the contingeny and your data will be in a consistent state; any incomplete transaction will be rollbacked and commited data will be available for you after you startup your instance.

F- PL/SQL is the native language of Oracle, for highly scalable applications needs PL/SQL and Oracle's SQL extentions like analytic functions are great when you are working on huge amount of data -> http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Oracle may not be cheap for some companies but after reading above references if you will be doing a mission critical database operation, I believe you will eventually choose Oracle without hesitation. Because these are the reasons why Oracle Database is #1 with 48.6% share in the market at 2005 -> http://www.gartner.com/press_releases/asset_152619_11.html

II - 12 important links for Oracle newbies

1. History of Oracle
http://tonguc.wordpress.com/2006/12/27/history-of-oracle/

2. Oracle Product Family and Pricing Highlights
http://tonguc.wordpress.com/2006/12/28/oracle-product-family-and-pricing-highlights/

3. Oracle Database and Corporation From Wikipedia, the free encyclopedia
http://en.wikipedia.org/wiki/Oracle_database
http://en.wikipedia.org/wiki/Oracle_Corporation

4. Oracle® Database Concepts 10g Release 2 (10.2)
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

5. Oracle database official documentation
http://tahiti.oracle.com

6. Free Database 10g Release 2 Express Edition for Windows and Linux
http://otn.oracle.com/xe

7. Free Development Environments for Oracle Database
http://otn.oracle.com/apex
http://www.oracle.com/technology/getting-started/sqldev.html
http://otn.oracle.com/jdev

8. Oracle Technology Network(OTN) portal for downloads, articles and sample code
http://otn.oracle.com

9. Oracle Technology Network(OTN) forums
http://forums.oracle.com

10. AskTom forum by Thomas Kyte
http://asktom.oracle.com

11. Free Oracle Magazine
http://www.oracle.com/oramag

12. Oracle Community can help you in connecting various groups and forums.
http://www.oraclecommunity.net/

Hope it helps.All the best have a bright future in Oracle.



Best regards,

Rafi.