RMAN – Backup & Restore database using Tag

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)

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

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

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

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

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

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

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

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)

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.