Systems Engineering and RDBMS

Archive for May 22nd, 2008

SQL Server Audit in SQL Server 2008

Posted by decipherinfosys on May 22, 2008

SQL Server 2005 already has a good set of auditing capabilities namely C2 Audit Mode Option, DDL Triggers and SQL Server Profiler (besides writing one’s own DML triggers). In SQL Server 2008, one can make use of CDC (Change Data Capture) for capturing the changes as well as a new feature called SQL Server Audit.

SQL Server Audit is built at the top of XEVENT which stands for Extended Events which is a new event infrastructure that has been introduced in SQL Server 2008.

SQL Server Audit involves these steps:

a) We first need to create a SQL Server Audit object which is used to collect the instance or database level actions. The results of the audit are sent to a target which can be a file, windows application event log or security event log and can be viewed via the event viewer.

b) We need to create a database audit specification or a server (for an instance) audit specification which needs to be mapped to the audit object created in Step #(a). This specification collects the pre-defined groups of atomic events and sends them to the Audit Object and that records them in the target (file, application or security event logs).

c) Then we need to enable the SQL Server Audit and the Audit Specification.

d) We can then read the logged data using either event viewer (start/run/eventvwr) or using the Log File Viewer or a new function: fn_read_audit_file.

You can read more on SQL Server Audit at this technet link here.

Posted in SQL Server | 1 Comment »

Recovering a table using Flashback Table command

Posted by decipherinfosys on May 22, 2008

Prior to Oracle 10g, it was not possible to recover the table back once it is dropped. Oracle introduced new feature ‘Oracle Flashback Table’, one of the many useful features. Using this new feature we can recover a dropped table to its earlier state with single statement. Let us see how we can achieve it.

First let us create an empty table and populate it with some data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE TEST
(
TEST_ID      NUMBER(9) NOT NULL,
TEST_DATE    DATE,
TEST_INDIC   NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
– TABLESPACE Clause
/

CREATE SEQUENCE TEST_SEQ
START WITH 1
CACHE 100
/

CREATE        INDEX TEST_IND_1 ON TEST(TEST_DATE)
– TABLESPACE CLAUSE
/

CREATE OR REPLACE TRIGGER TIB_TEST
BEFORE INSERT ON TEST FOR EACH ROW
BEGIN
IF :NEW.TEST_ID IS NULL THEN
SELECT TEST_SEQ.NEXTVAL INTO :NEW.TEST_ID
FROM DUAL;
END IF;
END;
/

INSERT INTO Test(test_date) VALUES(sysdate);
INSERT INTO Test(test_date) VALUES(sysdate);

In order to recover table back, we need to make sure that recyclebin parameter is on. Run following SQL to see the current value of parameter.

SQL>show parameter recyclebin

NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      OFF

Recyclebin parameter value is set to off, so we need to turn it on first. We can do it either at session level or at system level. It is a dynamic parameter so there is no need to stop and start the database after changing its value. We will set it at a system level. Alternatively we can put it in the init<sid>.ora file as well.

SQL> ALTER SYSTEM SET recyclebin = ON;

Flashing back to before drop

Before drop syntax allows to restore the table in a state just before the table was dropped. We already created table previously. Let us check it first and then drop it.

SQL>select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
TEST                           TABLE

Now let us drop the table and then again run the same query.

SQL>drop table test;
Table dropped.
SQL>select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
BIN$77qGCfjISXWU+Zmy+f5GFQ==$0 TABLE

Even though we have dropped the table, table is not actually dropped. Instead it is renamed with system generated name and is kept in recyclebin. You can view details either from user_recyclebin view or recyclebin synonym. It also renames any indexes and triggers of the table. Below query shows the original name and new name of all the objects which were dropped.

SQL> select original_name, type, object_name from recyclebin;

ORIGINAL_N TYPE                      OBJECT_NAME
———- ————————- ——————————
TEST       TABLE                     BIN$77qGCfjISXWU+Zmy+f5GFQ==$0
PK_TEST    INDEX                     BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0
TEST_IND_1 INDEX                     BIN$ALBfM1WhRPKBrsAkaHmQNA==$0
TIB_TEST   TRIGGER                   BIN$8UeSfnDBQtuinSD2vUFF7g==$0

Now let us try to recover the table. Issue following command to revive the table.

SQL> flashback table test to before drop;
Flashback complete.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
—————————— ——- ———-
TEST                           TABLE

Table is now back entirely including trigger and indexes along with data. Flashback command renames the table to its original name but does not rename the indexes, triggers or constraints. We need to manually rename them. Get the object name and its original name from the query on the previous page.

SQL> alter trigger “BIN$8UeSfnDBQtuinSD2vUFF7g==$0″ rename to TIB_TEST;
Trigger altered.

SQL> alter index “BIN$eYaVVVT7Q2eGx2qr1f+Nhg==$0″ rename to PK_TEST;
Index altered.

SQL> alter index “BIN$ALBfM1WhRPKBrsAkaHmQNA==$0″ rename to TEST_IND_1;
Index altered.

Constraint names are not retrievable from user_recyclebin view so we have to refer to original scripts or any other documentation to get its original name but following is the syntax to rename the constraints.

SQL> alter table test rename constraint “BIN$2WvwhoEMTRiDYrEumwLPCg==$0″ to PK_TEST;

Flashing back to SCN (System change number)

Other use of flashback table is to replace the data in the table to a different point of time using timestamp and/or SCN clause. Here we will show you how we can achieve it using SCN.

Let us first get the current SCN value. Make sure that you have execute privilege on dbms_flashback package. Run following statement to get current SCN.

SQL>  select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
3965449

At this point, data in the table is as shown below.

SQL> select test_id, test_indic from test;

TEST_ID TEST_INDIC
———- ———-
1          0
2          0

Now we will update value of test_indic column to 1.

SQL> update test
2     set test_indic = 1
3  /

2 rows updated.

SQL> commit;

Commit complete.

First we will compare the old value and new value of test_indic column by joining against table version at particular SCN. Run following SQL. Make sure to change the SCN value with correct value or you can parameterize the query to get the input of SCN number.

SQL> select a.test_id, a.test_indic new_indic, b.test_indic old_indic
2      from test a inner join test as of scn 3965449 b on b.test_id = a.test_id;

TEST_ID  NEW_INDIC  OLD_INDIC
———- ———- ———-
1          1          0
2          1          0

In above query, we are self joining the test table with one of its past version to compare the updated column values. Now let us see how we can restore the table to that SCN value. Issue following command to reinstate the copy of the table at SCN value.

SQL> flashback table test to scn 3965449;
flashback table test to scn 3965449
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

Oops! We ran into an error. Whenever we are using flashback clause with SCN or TIMESTAMP, we need to enable the row movement for a table.

SQL> alter table test enable row movement;
Table altered.

Now issue the flashback statement again and review the data in the table.

SQL> flashback table test to scn 3965449;
Flashback complete.

SQL> select test_id, test_indic from test;
TEST_ID TEST_INDIC
———- ———-
1          0
2          0

We have successfully reverted back the changes and restored the table to a specific point in time. In similar fashion flashback clause can be used with timestamp and restore point options as well.

Posted in Oracle | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 84 other followers