Saturday, 31 May 2014
Tuesday, 1 April 2014
Wha is Deadlock?
A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked.
Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.
Error in alert file: Ora 00060
In above case deadlock occurs.
because each one have lock on each other tables.
1) like when user 1 updates t1 it put lock on it, at same time user2 updates t2 it put lock on it.
2) then user 1 updates t2,at same time user 2 updates on t1.
3) When user 1 tries to commit on it doesn't allow to commit since t2 hv lock by user2.
4) when user 2 tries to commit on it doesn't allow to commit since t1 hv lock by user1.
Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock.
Error in alert file: Ora 00060
In above case deadlock occurs.
because each one have lock on each other tables.
1) like when user 1 updates t1 it put lock on it, at same time user2 updates t2 it put lock on it.
2) then user 1 updates t2,at same time user 2 updates on t1.
3) When user 1 tries to commit on it doesn't allow to commit since t2 hv lock by user2.
4) when user 2 tries to commit on it doesn't allow to commit since t1 hv lock by user1.
RMAN Backup type (Differential & Cumulative)
RMAN backup types
RMAN incremental backup for an Oracle DB
Sometimes we need to backup the database changes only from the last backup (only the last changes are backed up). This is an incremental backup. There are 2 types of incremental backup: DIFFERENTIAL (by default) & CUMULATIVE.
NOTE: The incremental backups are only for the DATA files.
DIFFERENTIAL backup = which backs up all blocks changed after the most recent incremental backup at level 1 or 0. (See Picture 1). The following RMAN command is used to take a DIFFERENTIAL database backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
CUMULATIVE backup = which backs up all blocks changed after the most recent incremental backup at level 0. (See Picture 2)
The following RMAN command is used to take a CUMULATIVE database backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
The advantages of each types of incremental backup
Here are the advantages of each types of incremental backup:
Advantages of the DIFFERENTIAL backup
Advantages of the CUMULATIVE backup
RMAN incremental backup for an Oracle DB
Sometimes we need to backup the database changes only from the last backup (only the last changes are backed up). This is an incremental backup. There are 2 types of incremental backup: DIFFERENTIAL (by default) & CUMULATIVE.
NOTE: The incremental backups are only for the DATA files.
DIFFERENTIAL backup = which backs up all blocks changed after the most recent incremental backup at level 1 or 0. (See Picture 1). The following RMAN command is used to take a DIFFERENTIAL database backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
CUMULATIVE backup = which backs up all blocks changed after the most recent incremental backup at level 0. (See Picture 2)
The following RMAN command is used to take a CUMULATIVE database backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;
The advantages of each types of incremental backup
Here are the advantages of each types of incremental backup:
Advantages of the DIFFERENTIAL backup
- Less space disk used
- Less network traffic
- Less time to take a backup
Advantages of the CUMULATIVE backup
- Less time to recover
Saturday, 29 March 2014
Download SQL fundamental PPT file
Download SQL fundamental PPT file
https://drive.google.com/file/d/0B56wEvD7Qe6LeEs2RGxGS0d6T2c/edit?usp=sharing
Thursday, 6 March 2014
Playing with dbms_jobs
create job
From TOAD and PL/SQL developer you can generate create job statement.
In sql developer you can't generate create job statement.
On sqlplus level using get_ddl you can generate create job statement
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => 'Proc_Dashboard(1,next_date,job);',
next_date => to_date('04-01-2013 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE+3/1440');
commit;
end;
/
select * from dba_jobs order by schema_user
DBA_JOBS_RUNNING lists all jobs that are currently running in the instance.
SID Identifier of process that is executing the job. See "V$LOCK".
JOB Identifier of job. This job is currently executing.
FAILURES Number of times this job started and failed since its last success.
LAST_DATE Date that this job last successfully executed.
LAST_SEC Same as LAST_DATE. This is when the last successful execution started.
THIS_DATE Date that this job started executing.
THIS_SEC Same as THIS_DATE. This is when the last successful execution started.
INSTANCE NUMBER Indicates which instance can execute or is executing the job; the default is 0.
Jobs that are currently running:
SELECT * FROM user_jobs_running;
Loging to perticular schmeas and remove the jobs
========================================
NEXT_DATE
You can alter the next date that Oracle executes a job by calling the DBMS_JOB.NEXT_DATE procedure,
as shown in the following example:
BEGIN
DBMS_JOB.NEXT_DATE(2, SYSDATE + 3/1400);
commit;
END;
/
INTERVAL
The following example illustrates changing the execution interval for a job by calling the DBMS_JOB.INTERVAL procedure:
BEGIN
DBMS_JOB.INTERVAL(41, 'SYSDATE + 4/1400');
commit;
END;
/
BEGIN
DBMS_JOB.BROKEN(41,TRUE);
commit;
END;
/
BEGIN
DBMS_JOB.REMOVE( 41);
commit;
END;
/
To run the job manually
EXEC dbms_job.run(job_no);
From TOAD and PL/SQL developer you can generate create job statement.
In sql developer you can't generate create job statement.
On sqlplus level using get_ddl you can generate create job statement
variable job number;
begin
sys.dbms_job.submit(job => :job,
what => 'Proc_Dashboard(1,next_date,job);',
next_date => to_date('04-01-2013 12:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'SYSDATE+3/1440');
commit;
end;
/
select * from dba_jobs order by schema_user
DBA_JOBS_RUNNING lists all jobs that are currently running in the instance.
SID Identifier of process that is executing the job. See "V$LOCK".
JOB Identifier of job. This job is currently executing.
FAILURES Number of times this job started and failed since its last success.
LAST_DATE Date that this job last successfully executed.
LAST_SEC Same as LAST_DATE. This is when the last successful execution started.
THIS_DATE Date that this job started executing.
THIS_SEC Same as THIS_DATE. This is when the last successful execution started.
INSTANCE NUMBER Indicates which instance can execute or is executing the job; the default is 0.
Jobs that are currently running:
SELECT * FROM user_jobs_running;
Loging to perticular schmeas and remove the jobs
========================================
NEXT_DATE
You can alter the next date that Oracle executes a job by calling the DBMS_JOB.NEXT_DATE procedure,
as shown in the following example:
BEGIN
DBMS_JOB.NEXT_DATE(2, SYSDATE + 3/1400);
commit;
END;
/
INTERVAL
The following example illustrates changing the execution interval for a job by calling the DBMS_JOB.INTERVAL procedure:
BEGIN
DBMS_JOB.INTERVAL(41, 'SYSDATE + 4/1400');
commit;
END;
/
BEGIN
DBMS_JOB.BROKEN(41,TRUE);
commit;
END;
/
BEGIN
DBMS_JOB.REMOVE( 41);
commit;
END;
/
To run the job manually
EXEC dbms_job.run(job_no);
SGA memory structure
show parameter sga_
NAME TYPE VALUE
------------------------------------ ----------- -------
sga_max_size big integer 280M
sga_target big integer 280M
SQL> select name,bytes/1024/1024 "Mbs" from v$sgainfo;
NAME Mbs
-------------------------------- ----------
Fixed SGA Size 1.19078064
Redo Buffers 2.80859375
Buffer Cache Size 196
Shared Pool Size 68
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 4
Granule Size 4
Maximum SGA Size 280
Startup overhead in Shared Pool 36
Free SGA Memory Available 0
============================================================
============================================================
Buffer Cache Size 196
Shared Pool Size 68
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 4
Granule Size 4
----------------------------------------------------
Maximum SGA Size 280
SQL> show sga
Total System Global Area 293601280 bytes 280 Mbs
Fixed Size 1248624 bytes 1.190 Mbs
Variable Size 83886736 bytes 80 Mbs
Database Buffers 205520896 bytes 196 Mbs
Redo Buffers 2945024 bytes 2.808 Mbs
Database buffers(196)=Buffer Cache Size (196)
Variable Size(80)=Shared Pool Size(68) + Large Pool Size(4) + Java Pool Size(4) + Streams Pool Size(4)
select name,bytes/1024/1024 "mbs" from v$sgainfo where name like '%Pool%' or name like '%Buffer%';
NAME TYPE VALUE
------------------------------------ ----------- -------
sga_max_size big integer 280M
sga_target big integer 280M
SQL> select name,bytes/1024/1024 "Mbs" from v$sgainfo;
NAME Mbs
-------------------------------- ----------
Fixed SGA Size 1.19078064
Redo Buffers 2.80859375
Buffer Cache Size 196
Shared Pool Size 68
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 4
Granule Size 4
Maximum SGA Size 280
Startup overhead in Shared Pool 36
Free SGA Memory Available 0
============================================================
============================================================
Buffer Cache Size 196
Shared Pool Size 68
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 4
Granule Size 4
----------------------------------------------------
Maximum SGA Size 280
SQL> show sga
Total System Global Area 293601280 bytes 280 Mbs
Fixed Size 1248624 bytes 1.190 Mbs
Variable Size 83886736 bytes 80 Mbs
Database Buffers 205520896 bytes 196 Mbs
Redo Buffers 2945024 bytes 2.808 Mbs
Database buffers(196)=Buffer Cache Size (196)
Variable Size(80)=Shared Pool Size(68) + Large Pool Size(4) + Java Pool Size(4) + Streams Pool Size(4)
select name,bytes/1024/1024 "mbs" from v$sgainfo where name like '%Pool%' or name like '%Buffer%';
How to set Environmental variables in linux & windows
How to set Environmental variables in linux & windows
In linux
export ORACLE_SID=name
echo $ORACLE_SID
In windows
set ORACLE_HOME=C:\ss
echo %ORACLE_HOME%
In linux
export ORACLE_SID=name
echo $ORACLE_SID
In windows
set ORACLE_HOME=C:\ss
echo %ORACLE_HOME%
Subscribe to:
Posts (Atom)