External Tables
Sometimes you want to access data that resides outside of the database in the text format, but you want to use it as if it were a table in the db. You could use a utility such as SQL*Loader to load the table into the database, the data may be quite volatile or your user base expertise might not in include executing SQL * Loader at win or unix.
Example:
SQL> create directory tst as 'f:\';
Directory created.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
D:\Documents and Settings\Administrator>f:
F:\>type babu.sql
10,babu
20,taj
F:\>
F:\>exit
SQL> create table emp ( eno_id int,ename varchar2(50))
2 organization external
3 (type oracle_loader
4 default directory tst
5 access parameters
6 (records delimited by newline
7 fields terminated by ','
8 (eno_id char,ename char)
9 )
10 location ('babu.sql')
11 );
Table created.
SQL> select * from emp;
ENO_ID ENAME
---------- --------------------------------------------------
10 babu
20 taj
2 rows selected.
Friday, July 13, 2007
Table
Global Temporary Tables
They are temporary tables is the sense of the data that is stored in the table, not in definition of the table itself. The command Create Global temporary table created temp tables. all users who have permissions on the table itself can perform DML on a temporary table.
There are two different flavors of temp data in a temp table.
1. Temporary for the duration of the transaction.
2. Temporary for the duration of the session.
On commit delete rows - Removes all rows from the temp. tale when a commit or rollback issued.
On commit preserve rows - Keeps the rows in the table beyond the tran. boundary. However the users session terminated all of the user row's in the temp. table removed.
Example:
SQL> create global temporary table babu ( eno int) on commit delete rows;
Table created.
SQL> insert into babu values (10);
1 row created.
SQL> insert into babu values (20);
1 row created.
SQL> insert into babu values (30);
1 row created.
SQL> select * from babu;
ENO
----------
10
20
30
SQL> commit;
Commit complete.
SQL> select * from babu;
no rows selected
They are temporary tables is the sense of the data that is stored in the table, not in definition of the table itself. The command Create Global temporary table created temp tables. all users who have permissions on the table itself can perform DML on a temporary table.
There are two different flavors of temp data in a temp table.
1. Temporary for the duration of the transaction.
2. Temporary for the duration of the session.
On commit delete rows - Removes all rows from the temp. tale when a commit or rollback issued.
On commit preserve rows - Keeps the rows in the table beyond the tran. boundary. However the users session terminated all of the user row's in the temp. table removed.
Example:
SQL> create global temporary table babu ( eno int) on commit delete rows;
Table created.
SQL> insert into babu values (10);
1 row created.
SQL> insert into babu values (20);
1 row created.
SQL> insert into babu values (30);
1 row created.
SQL> select * from babu;
ENO
----------
10
20
30
SQL> commit;
Commit complete.
SQL> select * from babu;
no rows selected
dbv
The DBVERIFY utility, available since in oracle 7.3, Check the logical integrity of an offline database. The file can only be datafiles. DEVERIFY can't analyze online redo log files or archived redo log files. In pervious version of Oracle DBV could analyze all of TS Datafiles in parallel by spawning multiple DBV cmd's. However, becuase Bigfile TS has only one datafile, DBV has been
enhanced to analyze parts of a bigfile TS's Datafiles in parallel.
SQL> host dbv help=y
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (2048)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
SQL> select file#,blocks,name from v$datafile;
FILE# BLOCKS NAME
---------- ---------- ---------------------------------------------------------
1 83200 E:\SP2\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
2 51200 E:\SP2\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
3 5120 E:\SP2\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
4 5120 E:\SP2\ORACLE\ORADATA\ORCL\DRSYS01.DBF
5 39040 E:\SP2\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
6 6400 E:\SP2\ORACLE\ORADATA\ORCL\INDX01.DBF
7 2560 E:\SP2\ORACLE\ORADATA\ORCL\TOOLS01.DBF
8 6400 E:\SP2\ORACLE\ORADATA\ORCL\USERS01.DBF
8 rows selected.
SQL> host dbv file=E:\SP2\oracle\oradata\orcl\UNDOTBS01.DBF BLOCKSIZE=4096
DBVERIFY: Release 9.0.1.1.1 - Production on Fri Jul 13 22:46:46 2007
(c) Copyright 2001 Oracle Corporation. All rights reserved.
DBVERIFY - Verification starting : FILE = E:\SP2\oracle\oradata\orcl\UNDOTBS01.D
BF
DBVERIFY - Verification complete
Total Pages Examined : 51200
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 30224
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 20976
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Wednesday, July 11, 2007
Flashback Tech
In Oracle 10g, the flashback functionality has been greatly extended.
Flashback Database
Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle databa se server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.
Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.
RVWR Background Process
When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.
RVWR Background process and Flashback Database Logs,
The list below shows all the background processes for the 'grid' instance.
$ ps -ef | grep grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
Enabling Flashback Database
You can enable Flashback Database using the following steps:
1. Make sure the database is in archive mode.
2. Configure the recovery area by setting the two parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
- db_flashback_retention_target
5. Determine if Flashback Database is enabled:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
Flashback Drop
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until,
- You permanently drop them with the PURGE command.
- Recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace needs to be extended.
You can view the dropped objects in the recycle bin from two dictionary views:
- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects
Example :
QL> show user
USER is "SCOTT"
SQL>
SQL> create table tst (a int);
Table created.
SQL> select object_name,original_name,operation,type from user_recyclebin;
no rows selected
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
SQL> create table tst (a int);
Table created.
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
BIN$NPgvUqA422DgQKjAAwIuMw==$0 TST DROP TABLE
Restoring a Dropped Object
SQL> flashback table TST to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
TST TABLE
6 rows selected.
SQL> select object_name,type from user_recyclebin;
OBJECT_NAME TYPE
------------------------------ -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
Dropping a Table Permanently
SQL> DROP TABLE TST PURGE;
Table dropped.
SQL> purge table tst;
Table purged.
SQL> purge recyclebin;
Recyclebin purged.
SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-01031: insufficient privileges
This statement purges all objects from tablespace users in the recycle bin
SQL> purge tablespace users;
Tablespace purged.
SQL>
Flashback Table
Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.
Flashback Table operations are not valid for the following object types:
- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)
Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.
However, if the following DDL commands are issued, the flashback table command does not work:
- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE
- ALTER TABLE ... MOVE
Flashback versions
Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.
You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.
Example:
SQL> CREATE TABLE BABU (S INT);
Table created.
SQL> INSERT INTO BABU VALUES (123);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575002
SQL> UPDATE BABU SET S=321;
1 row updated.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575090
SQL> DELETE FROM BABU WHERE S=321;
1 row deleted.
SQL> L
1* DELETE FROM BABU WHERE S=321
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575105
SQL>
SQL> INSERT INTO BABU VALUES (258);
1 row created.
SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575112
SQL> UPDATE BABU SET S=456;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575120
SQL> select versions_startscn as startscn, versions_endscn as endscn, versions_xid as xid,
2* versions_operation as oper, s from babu versions between scn 575002 and 575120
SQL> /
STARTSCN ENDSCN XID O S
---------- ---------- ---------------- - ----------
575118 09000E003A010000 I 456
575118 09000E003A010000 D 123
575118 123
Flashback Transaction
The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.
You can retrieve the transaction history from dba_transaction_query view:
Example :
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3* where xid= '09000E003A010000'
SQL> /
START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
574990 575118 SCOTT UPDATE BABU
update "SCOTT"."BABU" set "S" = '258' where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT INSERT BABU
delete from "SCOTT"."BABU" where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT DELETE BABU
insert into "SCOTT"."BABU"("S") values ('321');
Flashback Database
Flashback Database is faster than traditional point-in-time recovery. Traditional recovery uses redo log files and backups. Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle databa se server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.
Flashback Database reduces the time required to recover the database to a previous point. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.
RVWR Background Process
When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.
RVWR Background process and Flashback Database Logs,
The list below shows all the background processes for the 'grid' instance.
$ ps -ef | grep grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
Enabling Flashback Database
You can enable Flashback Database using the following steps:
1. Make sure the database is in archive mode.
2. Configure the recovery area by setting the two parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
4. Set the Flashback Database retention target:
- db_flashback_retention_target
5. Determine if Flashback Database is enabled:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------
YES
Flashback Drop
Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time.
This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.
Recycle Bin
A recycle bin contains all the dropped database objects until,
- You permanently drop them with the PURGE command.
- Recover the dropped objects with the UNDROP command.
- There is no room in the tablespace for new rows or updates to existing rows.
- The tablespace needs to be extended.
You can view the dropped objects in the recycle bin from two dictionary views:
- user_recyclebin - lists all dropped user objects
- dba_recyclebin - lists all dropped system-wide objects
Example :
QL> show user
USER is "SCOTT"
SQL>
SQL> create table tst (a int);
Table created.
SQL> select object_name,original_name,operation,type from user_recyclebin;
no rows selected
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
SQL> create table tst (a int);
Table created.
SQL> drop table tst;
Table dropped.
SQL> select object_name,original_name,operation,type from user_recyclebin
SQL> /
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TST DROP TABLE
BIN$NPgvUqA422DgQKjAAwIuMw==$0 TST DROP TABLE
Restoring a Dropped Object
SQL> flashback table TST to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
TST TABLE
6 rows selected.
SQL> select object_name,type from user_recyclebin;
OBJECT_NAME TYPE
------------------------------ -------------------------
BIN$NPgvUqA322DgQKjAAwIuMw==$0 TABLE
Dropping a Table Permanently
SQL> DROP TABLE TST PURGE;
Table dropped.
SQL> purge table tst;
Table purged.
SQL> purge recyclebin;
Recyclebin purged.
SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-01031: insufficient privileges
This statement purges all objects from tablespace users in the recycle bin
SQL> purge tablespace users;
Tablespace purged.
SQL>
Flashback Table
Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as indexes, constraints, and triggers will be restored.
Flashback Table operations are not valid for the following object types:
- Tables that are part of a cluster
- Materialized views
- Advanced Queuing tables
- Static data dictionary tables
- System tables
- Partitions of a table
- Remote tables (via database link)
Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a previous point in time.
However, if the following DDL commands are issued, the flashback table command does not work:
- ALTER TABLE ... DROP COLUMN
- ALTER TABLE ... DROP PARTITION - CREATE CLUSTER - TRUNCATE TABLE
- ALTER TABLE ... MOVE
Flashback versions
Flashback Query was first introduced in Oracle9i to provide a way to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Row History.
You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row.
Example:
SQL> CREATE TABLE BABU (S INT);
Table created.
SQL> INSERT INTO BABU VALUES (123);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575002
SQL> UPDATE BABU SET S=321;
1 row updated.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575090
SQL> DELETE FROM BABU WHERE S=321;
1 row deleted.
SQL> L
1* DELETE FROM BABU WHERE S=321
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575105
SQL>
SQL> INSERT INTO BABU VALUES (258);
1 row created.
SQL>
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575112
SQL> UPDATE BABU SET S=456;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
575120
SQL> select versions_startscn as startscn, versions_endscn as endscn, versions_xid as xid,
2* versions_operation as oper, s from babu versions between scn 575002 and 575120
SQL> /
STARTSCN ENDSCN XID O S
---------- ---------- ---------------- - ----------
575118 09000E003A010000 I 456
575118 09000E003A010000 D 123
575118 123
Flashback Transaction
The Flashback Transaction History feature provides a way to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Row History feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction History provides a faster way to undo a transaction than LogMiner.
You can retrieve the transaction history from dba_transaction_query view:
Example :
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
2 from flashback_transaction_query
3* where xid= '09000E003A010000'
SQL> /
START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
574990 575118 SCOTT UPDATE BABU
update "SCOTT"."BABU" set "S" = '258' where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT INSERT BABU
delete from "SCOTT"."BABU" where ROWID = 'AAAM0ZAAEAAAAGEAAB';
574990 575118 SCOTT DELETE BABU
insert into "SCOTT"."BABU"("S") values ('321');
Tuesday, July 10, 2007
EMCA
How to create Oracle Enterprise manager repository in 10g R1
Oracle@linuxDomain:~> emca -repos create
STARTED EMCA at Jul 10, 2007 5:00:50 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: lamp
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 10, 2007 5:01:05 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.1.0/Db_1/cfgtoollogs/emca/lamp/emca_2007-07-10_05-00-50-PM.log.
Jul 10, 2007 5:01:05 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jul 10, 2007 5:03:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 10, 2007 5:03:50 PM
oracle@linuxDomain:~> emca -config dbcontrol db
STARTED EMCA at Jul 10, 2007 5:06:16 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: lamp
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/10.1.0/Db_1
Database hostname ................ linuxDomain.site
Listener port number ................ 1521
Database SID ................ lamp
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 10, 2007 5:06:36 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.1.0/Db_1/cfgtoollogs/emca/lamp/emca_2007-07-10_05-06-16-PM.log.
Jul 10, 2007 5:06:36 PM oracle.sysman.emcp.DatabaseChecks performReposChecks
WARNING: 'shared_pool_size' must be greater than or equal to 80 MB.
Jul 10, 2007 5:06:43 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 10, 2007 5:08:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 10, 2007 5:08:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://linuxDomain.site:5501/em <<<<<<<<<<<
How to create Oracle Enterprise manager repository in 10g R2
C:\>set oracle_sid=us
C:\>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://standbyserver.samiindia.com:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory D:\oracle\product\10.2.0\db_1/standbyserver.sami
india.com_us/sysman/log
C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://standbyserver.samiindia.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The requested service
has already been started.
More help is available by typing NET HELPMSG 2182.
C:\>emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Nov 21, 2007 5:55:10 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: us
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 21, 2007 5:55:27 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\US\emca_2007-11-21_05-55-10-PM.log.
Nov 21, 2007 5:55:28 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 21, 2007 5:55:49 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Nov 21, 2007 5:58:11 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 21, 2007 5:58:12 PM
C:\>emca -config dbcontrol db -repos create
STARTED EMCA at Nov 21, 2007 6:03:01 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: us
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1
Database hostname ................ standbyserver.samiindia.com
Listener port number ................ 1521
Database SID ................ us
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Nov 21, 2007 6:03:15 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\US\emca_2007-11-21_06-03-01-PM.log.
Nov 21, 2007 6:03:18 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Nov 21, 2007 6:08:18 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Nov 21, 2007 6:08:32 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 21, 2007 6:09:33 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 21, 2007 6:09:33 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://standbyserver.samiindia.com
:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 21, 2007 6:09:33 PM
Automatically start and stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
oracle@linuxDomain:~> emctl status dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://linuxDomain.site:5501/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.1.0/Db_1/linuxDomain.site_lamp/sysman/log
Try to connect to the database Control
http://urip:urportnumber/em
Oracle@linuxDomain:~> emca -repos create
STARTED EMCA at Jul 10, 2007 5:00:50 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: lamp
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 10, 2007 5:01:05 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.1.0/Db_1/cfgtoollogs/emca/lamp/emca_2007-07-10_05-00-50-PM.log.
Jul 10, 2007 5:01:05 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jul 10, 2007 5:03:49 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jul 10, 2007 5:03:50 PM
oracle@linuxDomain:~> emca -config dbcontrol db
STARTED EMCA at Jul 10, 2007 5:06:16 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: lamp
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /u01/app/oracle/product/10.1.0/Db_1
Database hostname ................ linuxDomain.site
Listener port number ................ 1521
Database SID ................ lamp
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Jul 10, 2007 5:06:36 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.1.0/Db_1/cfgtoollogs/emca/lamp/emca_2007-07-10_05-06-16-PM.log.
Jul 10, 2007 5:06:36 PM oracle.sysman.emcp.DatabaseChecks performReposChecks
WARNING: 'shared_pool_size' must be greater than or equal to 80 MB.
Jul 10, 2007 5:06:43 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jul 10, 2007 5:08:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jul 10, 2007 5:08:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://linuxDomain.site:5501/em <<<<<<<<<<<
How to create Oracle Enterprise manager repository in 10g R2
C:\>set oracle_sid=us
C:\>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://standbyserver.samiindia.com:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is not running.
------------------------------------------------------------------
Logs are generated in directory D:\oracle\product\10.2.0\db_1/standbyserver.sami
india.com_us/sysman/log
C:\>emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://standbyserver.samiindia.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...The requested service
has already been started.
More help is available by typing NET HELPMSG 2182.
C:\>emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Nov 21, 2007 5:55:10 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: us
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 21, 2007 5:55:27 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\US\emca_2007-11-21_05-55-10-PM.log.
Nov 21, 2007 5:55:28 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Nov 21, 2007 5:55:49 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Nov 21, 2007 5:58:11 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 21, 2007 5:58:12 PM
C:\>emca -config dbcontrol db -repos create
STARTED EMCA at Nov 21, 2007 6:03:01 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: us
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1
Database hostname ................ standbyserver.samiindia.com
Listener port number ................ 1521
Database SID ................ us
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Nov 21, 2007 6:03:15 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\US\emca_2007-11-21_06-03-01-PM.log.
Nov 21, 2007 6:03:18 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Nov 21, 2007 6:08:18 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Nov 21, 2007 6:08:32 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Nov 21, 2007 6:09:33 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 21, 2007 6:09:33 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://standbyserver.samiindia.com
:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 21, 2007 6:09:33 PM
Automatically start and stop the DB-Console
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
oracle@linuxDomain:~> emctl status dbconsole
TZ set to Asia/Calcutta
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://linuxDomain.site:5501/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.1.0/Db_1/linuxDomain.site_lamp/sysman/log
Try to connect to the database Control
http://urip:urportnumber/em
Thursday, July 5, 2007
Virtual Private DataBase
In 10g, VPD has beed extedned to inculde column - level VPD. In which security policies are applied only when a particular column or columns are accessed etc..
Initial Config:
VPD- allows you to customize the ways different users see the same table.
ie., VPD - Requires the use of a package named DPMS_RLS. From the SYSDBA - Privilege account grant execute privilege on the DBMS_RLS package to all users
Virtual Private Database (VPD) Enhancements
* Column-Level VPD Policy
* Column Masking
* Policy Types
* Application Context Support for Parallel Queries
Column-Level VPD Policy
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL> GRANT EXECUTE ON DBMS_RLS TO BABU;
Grant succeeded.
SQL> CONNECT BABU/BABU@KEYSTONE
Connected.
SQL> DROP TABLE EMP;
Table dropped.
SQL> CREATE TABLE EMP ( ENO INT, ENAME VARCHAR2(22), SAL INT);
Table created.
SQL> INSERT INTO EMP VALUES (101,'BABU',5000);
1 row created.
SQL> INSERT INTO EMP VALUES (102,'TAJ',6000);
1 row created.
SQL> INSERT INTO EMP VALUES (103,'SRI',7000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
SQL> CREATE OR REPLACE FUNCTION JOB_COL_VPD (OOWNER IN VARCHAR2,OJNAME IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 CON VARCHAR2(100);
4 BEGIN
5 CON := 'SAL=5000' ;
6 RETURN (CON);
7 END JOB_COL_VPD;
8 /
Function created.
SQL> BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SYS_RELEVANT_COLS => 'SAL');
8 END;
9 /
DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL');
8* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
SQL> BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
Column Masking
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL',
8 SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ
103 SRI
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
FGA- Now inculude DML statements in addition to quweies.
SQL> SHOW USER
USER is "BABU"
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL>
SQL> SHOW USER
USER is "SYS"
SQL> TRUNCATE TABLE FGA_LOG$;
Table truncated.
SQL>
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY
3 (
4 OBJECT_SCHEMA => 'BABU',
5 OBJECT_NAME => 'EMP',
6 POLICY_NAME => 'FIRST_FGA',
7 AUDIT_CONDITION => NULL,
8 AUDIT_COLUMNS => 'ENO,SAL,ENAME',
9 STATEMENT_TYPES => 'INSERT,UDPATE,DELETE,SELECT');
10 END;
11 /
DBMS_FGA.ADD_POLICY
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> SHOW ERROR
No errors.
SQL> SELECT * FROM USER_ERRORS
2 /
no rows selected
SQL> begin
2 DBMS_FGA.add_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST', audit_condition => NULL , audit_column => 'ENO,ENAME,SAL', statement_types => 'SELECT,INSERT,UPDATE,DELETE');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> connect babu/babu@keystone
Connected.
SQL>
SQL> show user
USER is "BABU"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$yZPDgfriR8OKWMUf5FlP+Q==$0 TABLE
EMP TABLE
BONUS TABLE
DEPT TABLE
SALGRADE TABLE
PLAN_TABLE TABLE
INVOICE_LINE TABLE
7 rows selected.
SQL> select * from EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
SQL> INSERT INTO EMP VALUES ( 104,'MD', 10000);
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> UPDATE EMP SET SAL=500 WHERE ENO=104;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> DELETE FROM EMP WHERE ENO=101;
1 row deleted.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
102 TAJ 6000
103 SRI 7000
104 MD 500
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL>
SQL> SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------------------
select * from EMP
INSERT INTO EMP VALUES ( 104,'MD', 10000)
UPDATE EMP SET SAL=500 WHERE ENO=104
DELETE FROM EMP WHERE ENO=101
SELECT * FROM EMP
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
3* END;
SQL> /
DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'DROP_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST' );
3* END;
SQL> /
PL/SQL procedure successfully completed.
Uniform Audit Trail
The DBA_COMMON_AUDIT_TRAIL view has been added to display the complete audit trail:
* DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
* DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
* DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
Initial Config:
VPD- allows you to customize the ways different users see the same table.
ie., VPD - Requires the use of a package named DPMS_RLS. From the SYSDBA - Privilege account grant execute privilege on the DBMS_RLS package to all users
Virtual Private Database (VPD) Enhancements
* Column-Level VPD Policy
* Column Masking
* Policy Types
* Application Context Support for Parallel Queries
Column-Level VPD Policy
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL> GRANT EXECUTE ON DBMS_RLS TO BABU;
Grant succeeded.
SQL> CONNECT BABU/BABU@KEYSTONE
Connected.
SQL> DROP TABLE EMP;
Table dropped.
SQL> CREATE TABLE EMP ( ENO INT, ENAME VARCHAR2(22), SAL INT);
Table created.
SQL> INSERT INTO EMP VALUES (101,'BABU',5000);
1 row created.
SQL> INSERT INTO EMP VALUES (102,'TAJ',6000);
1 row created.
SQL> INSERT INTO EMP VALUES (103,'SRI',7000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
SQL> CREATE OR REPLACE FUNCTION JOB_COL_VPD (OOWNER IN VARCHAR2,OJNAME IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 CON VARCHAR2(100);
4 BEGIN
5 CON := 'SAL=5000' ;
6 RETURN (CON);
7 END JOB_COL_VPD;
8 /
Function created.
SQL> BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SYS_RELEVANT_COLS => 'SAL');
8 END;
9 /
DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL');
8* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
SQL> BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
Column Masking
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL',
8 SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);
9* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ
103 SRI
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
FGA- Now inculude DML statements in addition to quweies.
SQL> SHOW USER
USER is "BABU"
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL>
SQL> SHOW USER
USER is "SYS"
SQL> TRUNCATE TABLE FGA_LOG$;
Table truncated.
SQL>
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY
3 (
4 OBJECT_SCHEMA => 'BABU',
5 OBJECT_NAME => 'EMP',
6 POLICY_NAME => 'FIRST_FGA',
7 AUDIT_CONDITION => NULL,
8 AUDIT_COLUMNS => 'ENO,SAL,ENAME',
9 STATEMENT_TYPES => 'INSERT,UDPATE,DELETE,SELECT');
10 END;
11 /
DBMS_FGA.ADD_POLICY
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> SHOW ERROR
No errors.
SQL> SELECT * FROM USER_ERRORS
2 /
no rows selected
SQL> begin
2 DBMS_FGA.add_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST', audit_condition => NULL , audit_column => 'ENO,ENAME,SAL', statement_types => 'SELECT,INSERT,UPDATE,DELETE');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> connect babu/babu@keystone
Connected.
SQL>
SQL> show user
USER is "BABU"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$yZPDgfriR8OKWMUf5FlP+Q==$0 TABLE
EMP TABLE
BONUS TABLE
DEPT TABLE
SALGRADE TABLE
PLAN_TABLE TABLE
INVOICE_LINE TABLE
7 rows selected.
SQL> select * from EMP;
ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000
SQL> INSERT INTO EMP VALUES ( 104,'MD', 10000);
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> UPDATE EMP SET SAL=500 WHERE ENO=104;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> DELETE FROM EMP WHERE ENO=101;
1 row deleted.
SQL> SELECT * FROM EMP;
ENO ENAME SAL
---------- ---------------------- ----------
102 TAJ 6000
103 SRI 7000
104 MD 500
SQL> CONNECT SYS AS SYSDBA
Connected.
SQL>
SQL> SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------------------------------------------------
select * from EMP
INSERT INTO EMP VALUES ( 104,'MD', 10000)
UPDATE EMP SET SAL=500 WHERE ENO=104
DELETE FROM EMP WHERE ENO=101
SELECT * FROM EMP
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
3* END;
SQL> /
DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'DROP_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
SQL> ed
Wrote file afiedt.buf
1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST' );
3* END;
SQL> /
PL/SQL procedure successfully completed.
Uniform Audit Trail
The DBA_COMMON_AUDIT_TRAIL view has been added to display the complete audit trail:
* DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
* DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
* DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
Wednesday, July 4, 2007
Linux DataBase Creation
Oracle Version : 10.2.0.1.0
Operating Sys : Suse Linux 9.3
Step 1: Here create oracle OFA folder.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> mkdir lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir adump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir bdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir cdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir dpdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir pfile
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir udump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/oradata> mkdir lamp
Step 2: Create new password file for your new instance.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/bin> orapwd file=/u01/app/oracle/product/10.1.0/Db_1/dbs/pwdlamp.ora entries=5 password=oracle force=y
Step 3: To start a new instance, say newsid, you create an initnewsid
oracle@linuxDomain:~> export ORACLE_SID=lamp
oracle@linuxDomain:~> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 16:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
SQL>
Step 4: Execute bellow script.
1 CREATE DATABASE "LAMP"
2 MAXINSTANCES 8
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXLOGHISTORY 1
7 DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSTEM01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSAUX01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/TEMP01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/UNDOTBS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K
12 MAXSIZE UNLIMITED
13 CHARACTER SET WE8MSWIN1252
14 NATIONAL CHARACTER SET AL16UTF16
15 LOGFILE GROUP 1 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO01.LOG') SIZE 10240K,
16 GROUP 2 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO02.LOG') SIZE 10240K,
17 GROUP 3 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO03.LOG') SIZE 10240K
18 USER SYS IDENTIFIED BY "&&syspwd"
19* USER SYSTEM IDENTIFIED BY "&&systempwd"
SQL> /
old 18: USER SYS IDENTIFIED BY "&&syspwd"
new 18: USER SYS IDENTIFIED BY "oracle"
old 19: USER SYSTEM IDENTIFIED BY "&&systempwd"
new 19: USER SYSTEM IDENTIFIED BY "oracle"
Database created.
Step 5 : Create users tablespace.
SQL> CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/USERS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
2 EXTENT MANAGEMENT LOCAL
3 SEGMENT SPACE MANAGEMENT AUTO;
Step 6: You need run the following script in your database.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql
Step 7: Create Server parameter file using parameter file.
SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora';
File created.
Step 8 : Start your database.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
Operating Sys : Suse Linux 9.3
Step 1: Here create oracle OFA folder.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> mkdir lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir adump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir bdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir cdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir dpdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir pfile
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir udump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/oradata> mkdir lamp
Step 2: Create new password file for your new instance.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/bin> orapwd file=/u01/app/oracle/product/10.1.0/Db_1/dbs/pwdlamp.ora entries=5 password=oracle force=y
Step 3: To start a new instance, say newsid, you create an initnewsid
oracle@linuxDomain:~> export ORACLE_SID=lamp
oracle@linuxDomain:~> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 16:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
SQL>
Step 4: Execute bellow script.
1 CREATE DATABASE "LAMP"
2 MAXINSTANCES 8
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXLOGHISTORY 1
7 DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSTEM01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSAUX01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/TEMP01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/UNDOTBS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K
12 MAXSIZE UNLIMITED
13 CHARACTER SET WE8MSWIN1252
14 NATIONAL CHARACTER SET AL16UTF16
15 LOGFILE GROUP 1 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO01.LOG') SIZE 10240K,
16 GROUP 2 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO02.LOG') SIZE 10240K,
17 GROUP 3 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO03.LOG') SIZE 10240K
18 USER SYS IDENTIFIED BY "&&syspwd"
19* USER SYSTEM IDENTIFIED BY "&&systempwd"
SQL> /
old 18: USER SYS IDENTIFIED BY "&&syspwd"
new 18: USER SYS IDENTIFIED BY "oracle"
old 19: USER SYSTEM IDENTIFIED BY "&&systempwd"
new 19: USER SYSTEM IDENTIFIED BY "oracle"
Database created.
Step 5 : Create users tablespace.
SQL> CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/USERS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
2 EXTENT MANAGEMENT LOCAL
3 SEGMENT SPACE MANAGEMENT AUTO;
Step 6: You need run the following script in your database.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql
Step 7: Create Server parameter file using parameter file.
SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora';
File created.
Step 8 : Start your database.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
Subscribe to:
Posts (Atom)
