ORA-DB-ONE

RMAN – Backup & Restore database using Tag

Posted in Backup and Recovery by jackyoon on January 27, 2010

Sometimes, it is necessary to backup a snapshot of a database and may need to restore exactly at that point in a real life. For example, even in my case, I had many chances that I had to backup a database before the software upgrade and had to restore exactly at the point of the backup if it fails.

The easiest way to do it is to backup the whole database directory to other name after the database shutdown and simply rename it if you need to rollback when you run oracle database on file system. But this option may not be available when you use ASM since ASM doesn’t support copy or move command from asmcmd utility. So, Rman would be the only case to do any work in this case.

We can use RMAN’s Tag function to accomplish this type of task and here are the backup and restore command that you can make use of Tag command.

RMAN> BACKUP DATABASE TAG ‘BACKUP_2010_01_27′;

To get the information of a specific entities, you can use rman’s list command like below.

RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF TABLESPACE USERS;
RMAN> LIST BACKUP OF CONTROLFILE;
RMAN> LIST BACKUP OF DATAFILE 10;
RMAN> LIST BACKUP OF SPFILE;

RMAN> RESTORE DATABASE FROM TAG ‘BACKUP_2010_01_27′;

Audit Failed Login(s)

Posted in Audit by jackyoon on January 14, 2010

This artlcle explains the detail procedure of auditing. This doesn’t cover full function of the audit_trail but could help us to make our database to meet a company’s compliance.

1. Set parameters

AUDIT_TRAIL = DB
AUDIT_FILE_DEST = /home/app/oracle/admin/$ORACLE_SID/adump

2. Set Audit for Failed Connection

SQL> AUDIT SESSION WHENVER NOT SUCCESSFULLY;

3. Query Audit Logs

set line 130
col userhost format a15
col userid format a15
col comment$text format a30

select USERHOST,USERID,comment$text,
to_char(timestamp#,’DD-MON-YY:HH24:MI:SS’) login,
to_char(logoff$time,’DD-MON-YY:HH24:MI:SS’) logoff,
to_char(ntimestamp#,’DD-MON-YY:HH24:MI:SS’) time_tried
from sys.aud$
/

or

set line 130
col os_username format a15
col userhost format a20
col username format a20
col comment_text format a30
col action_name format a20

select OS_USERNAME,USERHOST,USERNAME,
to_char(TIMESTAMP,’DD-MON-YY:HH24:MI:SS’) TIMESTAMP,ACTION_NAME,COMMENT_TEXT
from DBA_AUDIT_TRAIL
/

3. Maintain Audit Logs

DELETE FROM SYS.AUD$
WHERE NTIMESTAMP# > TO_TIMESTAMP (‘29-FEB-08 09.07.59.907000 PM’) AND
NTIMESTAMP# < TO_TIMESTAMP ('29-MAR-08 09.07.59.907000 PM');

To delete all audit records from the audit trail, enter one of the following statements:

DELETE FROM SYS.AUD$;
TRUNCATE TABLE SYS.AUD$;

Use the DELETE and TRUNCATE TABLE statements to purge the fine-grained audit trail as well. For example:

DELETE FROM SYS.FGA_LOG$;
TRUNCATE TABLE SYS.FGA_LOG$;

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.

MERGE

Posted in SQL / PLSQL by jackyoon on December 14, 2009
MERGE INTO CLIENT_MASTER CM
   USING CLIENT C
    ON ( CM.clno = C.clno AND
         CM.flno = C.flno )
 WHEN MATCHED THEN
    UPDATE SET CM.qty = CM.qty + C.qty
 WHEN NOT MATCHED THEN
    INSERT ( CM.clno, CM.client_name, CM.flno, CM.qty )
      VALUES ( C.clno, C.client_name, C.flno, C.qty )

Scalar Subqueries

Posted in SQL / PLSQL by jackyoon on December 14, 2009

The scalar subquery enables you to treat the output of a subquery as a column or expression within a SELECT statement. A scalar subquery is a query that only selects one column or expression and return just one row.

SELECT F.flno, flavor_name, ( SELECT count(*)
                               FROM client c
                              WHERE c.flno = f.flno ) AS "How Many Times"
  FROM flavor f;

CASE Statement

Posted in 3766 by jackyoon on December 14, 2009

1. The simple CASE expression

SELECT client_name, flavor_name, qty
           ( CASE qty
               WHEN 1 THEN 'Got to have it'
               WHEN 2 THEN 'Just a little more'
               WHEN 3 THEN 'Bring it on'
             ELSE
                 'Real hungry'
             END ) AS "Whatsup!"
  FROM client NATURAL JOIN flavor;

2. The searched CASE expression
Oracle9i introduces the searched CASE expression.

SELECT client_name, flavor_name, qty
           ( CASE
               WHEN qty >= 4 THEN 'You must be hungry'
               WHEN UPPER(flavor_name) LIKE '%ILL%' THEN 'This is bad for you'
             ELSE
                 'Have a blast!'
             END ) AS "Remarks!"
  FROM client NATURAL JOIN flavor;

OUTER JOIN

Posted in SQL / PLSQL by jackyoon on December 14, 2009

Oracle9i or above, you can use the SQL:99 specification for the outer join.

1. LEFT OUTER JOIN

SELECT client_name, flavor_name
FROM client LEFT OUTER JOIN flavor
ON ( client.flno = flavor.flno );
CLIENT_NAME               FLAVOR_NAME
------------------- --------------------
Andy                    Vanilla
Beth                    Vanilla
Carol                   Strawberry
David

2. RIGHT OUTER JOIN

SELECT client_name, flavor_name
FROM client RIGHT OUTER JOIN flavor
ON ( client.flno = flavor.flno );
CLIENT_NAME               FLAVOR_NAME
------------------- --------------------
Andy                    Vanilla
Beth                    Vanilla
Carol                   Strawberry
                        Chocolate

3. FULL OUTER JOIN

SELECT client_name, flavor_name
FROM client FULL OUTER JOIN flavor
ON ( client.flno = flavor.flno );
CLIENT_NAME               FLAVOR_NAME
------------------- --------------------
Andy                    Vanilla
Beth                    Vanilla
Carol                   Strawberry
David
                        Chocolate

The ON clause

Posted in SQL / PLSQL by jackyoon on December 14, 2009

Instead of having to specify a join condition along with other criteria in the WHERE clause, the ON clause enables you to specify the join condition separate from the other criteria.

SELECT client_name, flavor_name, qty
FROM client JOIN flavor
ON ( client.flno = flavor.flno )
WHERE client_name IN ( ‘Andy’ , ‘Carol’ );

PRINT_TABLE PROCEDURE

Posted in SQL / PLSQL by jackyoon on December 11, 2009

This store procedure is very useful when I print a result from sqlplus.

CREATE OR REPLACE PROCEDURE PRINT_TABLE ( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ‘: ‘ ||
l_columnValue );
end loop;
dbms_output.put_line( ‘—————–’ );
end loop;
exception
when others then
raise;
end;

Equijoin with the USING keyword

Posted in SQL / PLSQL by jackyoon on December 11, 2009

One problem with the NATURAL JOIN specification is that it uses all the matching columns that have same name. You can perform the equijoin on a subset of the matching columns with USING clause.

SELECT client_name, flavor_name, qty
FROM client JOIN flavor USING ( flno );

You do not need to specify prefix for the columns in the select list.

NATURAL JOIN(Equi join)

Posted in SQL / PLSQL by jackyoon on December 11, 2009

The NATURAL JOIN matches rows that have equal values in all columns that have same name. Oracle assumes that the matching columns with the same name also have the same datatype. If they have different datatype, you will encounter error.

SELECT client_name, flavor_name, qty
FROM client, flavor
WHERE client.flno = flavor.flno;

You would get the same results when you specify the same query using SQL:99 in Oracle9i or above.

SELECT client_name, flavor_name, qty
FROM client NATURAL JOIN flavor;

When you use NATURAL JOIN, you need to keep below things in mind.
1. You do not need to specify join condition
2. You do not need to specify prefix for the columns in the select list.