Start here

Advertisements

Managing Undo Segment

Undo segment is used to remember data before modifing, for rollback or do other thing.
Read consistency
Rollback Transaction
Recover database :
For case DB is interupted
Step1: we get the lastest backup and restore  (<—– move backward)
Step2: we apply REDO segment (—–> move forward to the point when DB is is interupted)
But redo doesn’t know what data is commit or uncommit
Step3: we apply undo segment to get commit DB (<– abit backward)
Using Flashback query: select data @ a paticular point and time

Undo vs. Rollback Segments

Rollback Segments (10g earlier) and Undo Segments (9i and later)
Rollback Segments : Undo info will be saved in same tablespace
Undo Segments : Oracle manage all undo Undo Tables space, except the size

Configure Undo
By default, system uses Rollback segments.
Configuration file is in spfile, or init file.
UNDO_MANAGEMENT = AUTO  — Rollback segments then set = MANUAL
UNDO_TABLESPACE = UNDO_TBS  — We can have more than 1 undo tbs, but only 1 Tbs can be in used
UNDO_RETENTION  = 1800  — number of seconds to holdon undo info

We have active undo and inactive undo
active undo data: this data can perfome rollback
but inactive undo can be in used
select * from emp;  — get data from undo
update emp …   ;  — generate undo data
delete from emp;    — generate undo data

For the case, undo tbs is full, Oracle will get rid of inactive undo first
-> For the case it takes time for select statement, which is reading inactive undo
then undo is out of space, then we get ERROR “Snap shot too Old”.
=> Need to resize Undo tbs

Sizing the Undo tablespace
v$undostat
BEGIN_TIME
END_TIME
UNDOBLKS
UNDO = TRANSACTION RATE * BLOCK SIZE * UNDO RETENTION
1)Transaction rate =  max(undoblks)/600  -> this is number of undo block generate per second
2)show parameter undo_retention
3)show parameter db_block_size
Else, in Enteperise, we have Undo Management > Undo Advisor

Create UNDO TS: 2 ways
-Create database Orcl

Undo Tablespace undotbs
Datafile ‘/full/path/to/file.dbf’ size 5M AutoExtend ON;

-Create undo tablespace undotbs2
Datafile ‘/full/path/to/file.dbf’ size 10M reuse retention guarantee;
–reuse : if datafile is already exist, then reuse it
— retention guarantee (10.2) : garantee error snapshot too old
but the back drawn is DML is going to fail incase not enough tbs
Else “retention NO guarantee”

Alter UNDO Tbs
-Alter tablespace undotbs
ADD Datafile ‘/full/path/to/file.dbf’ size 10M ;

-Alter tablespace undotbs retention guarantee;
-Alter database rename file ‘/path/to/file.dbf’
to ‘/path/to/newfile.dbf’

Switch Undo tbs
Alter systen set UNDO_TABLESPACE = undotbs;

Drop Undo Ts
Drop tablespace undo including contents and datafiles;

–Check table space table to see undo tbs
select tablespace_name, contents, status
from dba_tablespaces;

– show parameter undo;

–Check datafile attached to tbs
select file_name from dba_data_files
where tablespace_name = ‘UNDOTBS01.DBF’;

— Switch type of Tbs from Rollback Seg to use Automatic Undo
Begin
:n = DBMS_UNOD_ADV.RBU_MIGRATION; — Advise undo tbs size
End;
/
Then modify the undo_management to AUTO instead of Manual

–Check active undo info in undo tbs
select distinct number, tablespace_name, bytes, satus
from dba_undo_extents
where status = ‘ACTIVE’; –EXPRIRED–can perfome an update statement to check

Advertisements

Oracle Start up/ Shutdown/ Initialization Files

Start up/ Shutdown/ Initialization Files

Prerequisite: SYSDBA, SYSOPER, OS special privilege which is put in DBA group

Startup Process : Unmount -> Mount -> Open
1. Unmounted : Instance started.
(background process + memory structure = instance)
-> system goes to Oracle_Home/dbs/spfileSID.ora
or spfile.ora or initSID.ora.
Incase, these files do not existed, then it fail

Optional, we can set the exactly what is initial file to look for to startup

2.Mounted : Instance is associated with DB

There’s parameter called control_files
-> point to 1 of 3 copy ctrl files.

If there’s no control files, this step will be fail.

In control file, there’s pointer point to data_files, and redo log files. If these files don’t exist, then step 3 fail.

show parameter control_files;
show parameter background;– located to alert log

select status from v$instance;

alter database mount;

3.Open: Datafiles and redo logs are opened.
DB available to everyone

alter database open;

Shutdown process
1. Close the DB
2. Unmount the DB
3. Shutdown the instance

There are 4 modes to shutdown
normal : wait for every sessions closed
transactional : wait for all trasaction finish
immediate : terminate very sessions and perform shutdown (*)
abort : => when startup Oracle instance recovery, apply redo logs …

Another way
SQL>startup pfile=’c:\oracle\product\….\initdw.ora’;–define initial file
startup restrict;– only some certain privilege users can connect to Oracle.

–To check

select logins from v$instance;
–RESTRICTED
alter system disable restricted session;
–The purpose of Restrict is when Admin user maintenance the system

select logins from v$instance;
–ALLOWED

alter system suspend;– prevent anymore input/output to datafiles
when perform backup without I/O interference

select database_status from v$instance;

alter system resume; — go back to normal

show parameter spfile; –binary server files parameter

Different between when using spfile and predefine init file:

– init file is a text file, and only read when startup

– spfile is binary file,  constantly read  by server and can change the content using alter system.

How to dynamic change parameter

desc v$parameter
ISSYS_MODIFIABLE -> to check of a parameter can reset/change online or not
IMMEDIATE :ok

-> if we want temporary change -> …. scope=memory;
DEFERED : modify affect after restart
–note for scope

-> alter system set audit_file_dest = ‘..\..’ scope = spfile  comment = ‘modify by Me’;

The change affect in spfile, and affect to system after restart DB.

FAIL : can’t change

Dump spfile to text file

create a pfile  from spfile; –For window server, file will be Oracle_home\datatbase\…

–For Linux: it will be dbs directory

else can specify the path

create a pfile = ‘c:\…\myinit.ora’ from spfile;

We can do opposite way, create a spfile from pfile, but DB will down in the mean time.

create spfile =’c:\…\Myspfile.ora’ from pfile = ”;

Data Dictionary Views

–Setting for instance

v$system_parameter

v$system_parameter2 (* –better output)

— This view show current session setup

v$parameter

v$parameter2

v$spparameter

–This view shows valid value for parameters, default value

v$parameter_valid_values

–Check this view to see if any obsolete parameter has been recently set, value  ISSPECIFIED= TRUE

v$obsolete_parameter

 

.Oracle EM port list:  $ORACLE_HOME/install/portlist.ini

Miscellaneous

1. Case sensitive password in oracle 11g
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

In DBA user view, it includes the password version password was created, and the latest DB version that password is updated.

2. Cross-session PL/SQL Function result cache.
@Oracle version 11.1g, to enable the function result cache, just simple add RESULT_CACHE clause, and RELIES_ON for validating the integrity.
After Oracle 11.2, RELIES_ON is unnecessary, bcs its automatically add-in.

3.Database replay in Oracle 11g
DBMS_WORKLOAD_CAPTURE
The Database Replay functionality of Oracle 11g allows you to capture workloads on a production system and replay them exactly as they happened on a test system

4.DDL with WAIT option lock -- DDL_LOCK_TIMEOUT
DDL requires exclusive lock on internal structure. U will get "ORA-00054 resource busy" if the lock is not available.
We can set the time wait for retaining the lock by setting time for parameter DDL_LOCK_TIMEOUT.

Alter session set DDL_LOCK_TIMEOUT = 30; --30 seconds.


FIRST_ROWS vs. ALL_ROWS and ROWNUM predicate

http://dioncho.wordpress.com/2009/01/30/89/

Managing Sessions From a Connection Pool in Oracle Databases

For client-server applications, If applications used a single database user and managed security internally, this made identifying the real users of the system difficult. The issue was complicated further by multi-tier architectures that used connection pooling.

To counter this, SET_IDENTIFIER and CLEAR_IDENTIFIER procedures to allow the real user to be associated with a session, regardless of what database user was being used for the connection.

In application layer : EXEC DBMS_SESSION.set_identifier(‘tim_hall’); –real user

In DB layer:

SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall
SELECT username, client_identifier FROM v$session WHERE username = 'TEST';

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall

Before the session is released back into the connection pool, the application should call the CLEAR_IDENTIFIER procedure.

EXEC DBMS_SESSION.clear_identifier;

Later releases of Oracle included the CLIENT_IDENTIFIER information in the audit trail, SQL trace files and performance tuning tools, making it even more useful.

Contexts (CLEAR_CONTEXT and CLEAR_ALL_CONTEXT)

http://www.oracle-base.com/articles/misc/dbms_session.php

DBMS_APPLICATION_INFO : For Code Instrumentation

Use to trace info from application to DB.

SET_MODULE/ SET_ACTION/ SET_CLIENT_INFO

Those info will be put to v$session. At Enterprise -> Performance Analysis, we can see Module/ Action info

http://www.oracle-base.com/articles/8i/dbms_application_info.php

 

 

 

 

 

Import FlatFile to Oracle

File Input:

File Output:

~~~~~~~~~~~~~~CODE FOR UTL_FILE~~~~~~~~~~~~~~

PROCEDURE load_example
IS
log    VARCHAR2 (100)      := ‘username/password’;
v_file     UTL_FILE.file_type;
err_file   UTL_FILE.file_type;
v_dir      VARCHAR2 (50)      := ‘/utl_file_dir/’;
l_file     VARCHAR2 (100)     := ‘data_file.txt’;
e_file     VARCHAR2 (100)     := ‘err_file.txt’;
v_how      VARCHAR2 (1)       := ‘r’;
v_text     VARCHAR2 (2000);
v_size     NUMBER             := 32767;

TYPE data_rec IS RECORD (
field1         VARCHAR2 (10),
field2        VARCHAR2 (100),
field3        VARCHAR2 (100),
val        VARCHAR2 (100),
rst        VARCHAR2 (2000),
cnt        NUMBER
);

l          data_rec;
BEGIN
DBMS_OUTPUT.ENABLE (1000000);

DBMS_APPLICATION_INFO.set_client_info (‘1’);

v_file := UTL_FILE.fopen (v_dir, l_file, v_how, v_size);
err_file := UTL_FILE.fopen (v_dir, e_file, ‘w’, v_size);

BEGIN
LOOP
l := NULL;
l.cnt := 0;

BEGIN
UTL_FILE.get_line (v_file, v_text, v_size);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXIT;
END;

v_text :=
REPLACE (REPLACE (REPLACE (v_text, CHR (13)), CHR (10)),
CHR (9)
);
l.rst := v_text;

WHILE LENGTH (l.rst) > 0
LOOP
BEGIN
l.cnt := l.cnt + 1;
l.val := SUBSTR (l.rst, 1, INSTR (l.rst, ‘,’, 1, 1) – 1);
l.rst := SUBSTR (l.rst, INSTR (l.rst, ‘,’, 1, 1) + 1);

IF l.cnt = 1
THEN
BEGIN
l.field1 := l.val;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line
(err_file,
‘Err while processing field1: ‘
|| v_text
|| ‘:’
|| SQLERRM
);
EXIT;
END;
ELSIF l.cnt = 2
THEN
BEGIN
l.field2 := l.val;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line
(err_file,
‘Err while processing field2: ‘
|| v_text
|| ‘:’
|| SQLERRM
);
EXIT;
END;
ELSIF l.cnt = 3
THEN
BEGIN
l.field3 := l.val;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line
(err_file,
‘Err while processing field3: ‘
|| v_text
|| ‘:’
|| SQLERRM
);
EXIT;
END;
END IF;

IF INSTR (l.rst, ‘,’, 1, 1) = 0
THEN
BEGIN
l.field3 := l.rst;
EXIT;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line
(err_file,
‘Err while processing field3: ‘
|| v_text
|| ‘:’
|| SQLERRM
);
EXIT;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line (err_file,
‘Err while processing line: ‘
|| v_text
|| ‘:’
|| SQLERRM
);
EXIT;
END;
END LOOP;

l.field1 := UPPER (LTRIM (RTRIM (l.field1)));
l.field2 := UPPER (LTRIM (RTRIM (l.field2)));
l.field3 := UPPER (LTRIM (RTRIM (l.field3)));

BEGIN
INSERT INTO table1 VALUES (l.field1, l.field2, l.field3 );
UPDATE TABLE2
SET fld1 = l.field1
WHERE UPPER (fld2) = l.field2
AND UPPER (fld3) = l.field3;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line (err_file,
‘Err while inserting/updating: ‘
|| l.field1
|| ‘:’
|| l.field2
|| ‘:’
|| l.field3
|| ‘:’
|| SQLERRM
);
END;
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.put_line (err_file, ‘Err : ‘ || SQLERRM);
END;

UTL_FILE.fclose (v_file);                                  — Close File
UTL_FILE.fclose (err_file);
EXCEPTION
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line (‘Invalid Path’);
UTL_FILE.fclose (v_file);                              — Close File
UTL_FILE.fclose (err_file);
WHEN UTL_FILE.invalid_mode
THEN
DBMS_OUTPUT.put_line (‘Invalid Mode’);
UTL_FILE.fclose (v_file);                              — Close File
UTL_FILE.fclose (err_file);
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.put_line (‘Write Error’);
UTL_FILE.fclose (v_file);                              — Close File
UTL_FILE.fclose (err_file);
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line (‘Invalid Operation’);
UTL_FILE.fclose (v_file);                              — Close File
UTL_FILE.fclose (err_file);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ‘: ‘ || SQLERRM);
UTL_FILE.fclose (v_file);                              — Close File
UTL_FILE.fclose (err_file);
END load_example;

create table table1( f1 varchar2(100),

 f2 varchar2(100),

f3 varchar2(100));

data_file.txt: 1, aaaaaaa, zzzzzzz
2, bbbbbbbb, xxxxxxxxx
3, ccccccccc, yyyyyyy
4, dddddddd, wwwwwwwww

Hierarchical goodbye Connect By

Hierarchical goodbye Connect By