Download SQL fundamental PPT file
https://drive.google.com/file/d/0B56wEvD7Qe6LeEs2RGxGS0d6T2c/edit?usp=sharing
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%
How To recover lost of all controlfile.
How To recover lost of controlfile,
As a DBA on proactive basis always include controlfile text based backup in every backups.
How to take controlfile text based backup
sql>alter database backup controlfile to trace as 'somepath/bkp_controlfile.txt';
Steps to recover controlfile
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 104857944 bytes
Database Buffers 239075328 bytes
Redo Buffers 2945024 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF',
14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF',
15 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF',
16 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF'
17 CHARACTER SET WE8MSWIN1252
18 ;
Control file created.
SQL> alter database open
2 ;
Database altered.
SQL>
As a DBA on proactive basis always include controlfile text based backup in every backups.
How to take controlfile text based backup
sql>alter database backup controlfile to trace as 'somepath/bkp_controlfile.txt';
Steps to recover controlfile
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 104857944 bytes
Database Buffers 239075328 bytes
Redo Buffers 2945024 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL1" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO01.LOG' SIZE 50M,
9 GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO02.LOG' SIZE 50M,
10 GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\REDO03.LOG' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSTEM01.DBF',
14 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\UNDOTBS01.DBF',
15 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF',
16 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\USERS01.DBF'
17 CHARACTER SET WE8MSWIN1252
18 ;
Control file created.
SQL> alter database open
2 ;
Database altered.
SQL>
Recovery Steps for Lost of system datafile
Recovery Steps for Lost of system datafile
SQL>shut abort;
SQL> startup
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 104857944 bytes
Database Buffers 239075328 bytes
Redo Buffers 2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF'
While database open in read write mode, it thrown an error.
We have last Level 0 backup.
Note: DB is already in mount stage
Steps to Restoration
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 6 13:08:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL1 (DBID=1225511391, not open)
RMAN> restore datafile 3;
Starting restore at 06-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1 tag=LVL0_ORCL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 06-MAR-14
RMAN> recover datafile 3;
Starting recover at 06-MAR-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929
_.ARC
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18
_.ARC
archive log thread 1 sequence 7 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1
_.ARC
archive log thread 1 sequence 8 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_8_9KJ8SLJY
_.ARC
archive log thread 1 sequence 9 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_9_9KJ8SM47
_.ARC
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929_.ARC thread=1 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18_.ARC thread=1 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-14
RMAN> alter database open;
database opened
Same can be used for system datafile as well.
SQL>shut abort;
SQL> startup
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1248936 bytes
Variable Size 104857944 bytes
Database Buffers 239075328 bytes
Redo Buffers 2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF'
While database open in read write mode, it thrown an error.
We have last Level 0 backup.
Note: DB is already in mount stage
Steps to Restoration
C:\Users\oradb>rman target sys/orcl1@orcl1
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Mar 6 13:08:12 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL1 (DBID=1225511391, not open)
RMAN> restore datafile 3;
Starting restore at 06-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL1\SYSAUX01.DBF
channel ORA_DISK_1: reading from backup piece D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\LOCAL\HOTLEVEL0_ORCL1_DB_02P2GDIV_2_1 tag=LVL0_ORCL
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 06-MAR-14
RMAN> recover datafile 3;
Starting recover at 06-MAR-14
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929
_.ARC
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18
_.ARC
archive log thread 1 sequence 7 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1
_.ARC
archive log thread 1 sequence 8 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_8_9KJ8SLJY
_.ARC
archive log thread 1 sequence 9 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_9_9KJ8SM47
_.ARC
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_5_9KJ8S929_.ARC thread=1 sequence=5
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_6_9KJ8SB18_.ARC thread=1 sequence=6
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2014_03_06\O1_MF_1_7_9KJ8SGP1_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-14
RMAN> alter database open;
database opened
Same can be used for system datafile as well.
Subscribe to:
Posts (Atom)