Learn Database Concepts

Auditing changes within the database are becoming more and more important. As well as auditing changes to data, auditing DDL changes can be just as critical. This allows the DBA to not only know what changes have been made but also what haven’t. Being able to see what changes have been made to the database can make debugging much quicker. This solution consists of two tables, one sequence and one trigger.   The Tables and Sequence : CREATE TABLE ddl_events ( eventId          NUMBER(10,0),   eventDate        DATE,   oraLoginUser     VARCHAR2(30),   oraDictObjName   VARCHAR2(30),   oraDictObjOwner  VARCHAR2(30),   oraDictObjType   VARCHAR2(30),   oraSysEvent      VARCHAR2(30),   machine          VARCHAR2(64),   program  ...

Find accounts unused for days                                                  Summary Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled. SELECT RPAD(da.username, 12) "Username", TRUNC(SYSDATE - MAX(da.TIMESTAMP)) "Days Inactive", LPAD(du.account_status, 16) "Status", LPAD(TO_CHAR(MAX(da.TIMESTAMP), 'DD-MON-YYYY'), 16) "Last Login" FROM dba_users du, dba_audit_session da WHERE da.action_name LIKE 'LOG%' -- AND da.username NOT IN ('SYS','SYSTEM') -- itemize accounts to exclude -- AND du.profile != '' -- or profiles to exclude AND du.username = da.username -- AND du.account_status = 'OPEN' -- could look at just OPEN if desired GROUP BY da.userna...

I found this interesting article online & would like to share: The role of the DBA is an anomaly in the IT environment. DBAs are often extremely critical yet hidden from view; highly paid yet individual contributors; and very knowledgeable yet rarely consulted. So why does an extremely critical, highly paid, and very knowledgeable resource have a high risk profile for quitting? What factors can an IT manager consider when trying to increase retention rates of DBAs? The following are the top 10 reasons DBAs quit, and leave their companies at risk: Burnout – constant overtime, holiday disruptions, and frequent weekend work On-Call – being on-call too often (or all the time) with frequent incidents More Pay – the best DBAs are highly valuable, and are always in high demand Recognition – real or perceived inadequate praise, recognition, or acknowledgement Constant Firefighting – always reacting to issues with inability to put in proactive measures Insufficient Involvement – often ...

 ORA-24247: network access denied by access control list (ACL) Yesterday I was trying to send mail from my Oracle Database and I got below mentioned error :- ERROR at line 1: ORA-24247: network access denied by access control list (ACL) ORA-06512: at "CINP01314", line 255 ORA-06512: at line 21  In 11g Database , we need to create and configure ACL(access control list). In order to use PL/SQL network utility pakages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services. Steps to configure :- 1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server. SQL> alter system set smtp_out_server= '10.10.10.10' ; ############### only for UTL_MAIL package this parameter needs to be set################# system altered 2. Create an access control list file :- begin DBMS_NETWORK_ACL_ADMIN .create_acl ( acl => 'ACL_PERMISSION.xml', description => 'Enables mail...

Recently i was working on moving some data file on the primary server and i have set the standby_file_management='MANUAL'. and  then I saw the following error on my standby server. ALTER DATABASE RECOVER managed standby database disconnect from session Attempt to start background Managed Standby Recovery process (ORCL) Thu Mar 20 16:07:33 2014 MRP0 started with pid=35, OS id=22622 MRP0: Background Managed Standby Recovery process started (ORCL) started logmerger process Thu Mar 20 16:07:38 2014 Managed Standby Recovery not using Real Time Apply MRP0: Background Media Recovery terminated with error 1111 Errors in file /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_pr00_22624.trc: ORA-01111: name for data file 214 is unknown - rename to correct file ORA-01110: data file 214: '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214' ORA-01157: cannot identify/lock data file 214 - see DBWR trace file ORA-01111: name for data file 214 is unknown - rename to correct file ORA-0111...

Frist Create file vi rman_script.sh then add the following lines in rman_script.sh file #!/bin/bash ################################################################### #  this script will delete the archive and obsolete file on standby server  ########## ################################################################### ORACLE_HOME=/u01/app/oracle/product/11.2/db_1; export ORACLE_HOME; ORACLE_SID=ORCL; export ORACLE_SID; rman msglog " now.log " <<EOF connect target /; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET; run { CROSSCHECK BACKUP; DELETE NOPROMPT OBSOLETE; DELETE NOPROMPT EXPIRED BACKUP; delete noprompt archivelog until time 'SYSDATE-7'; crosscheck archivelog all; } Exit; EOF Note:- It will delete all the archive of 7 days back. (SYSDATE-7) . then save it and exit then give the executing permission to rman_script.sh chmod 777 rman_script.sh ...

Count All the Rows in all tables in a schema  you can do it various options Option 1.   you can write a sql to get the out output an the then run the output to get the count for all tables . select 'select count (*) as '||owner||'_'||table_name||' from ' ||owner||'.'||table_name||';' from all_tables tables where owner='XXX' order by 1; Then run output manually: select count (*) as USER1_ASP_AGREEMENT_SCHEDULE from USER1.ASP_AGREEMENT_SCHEDULE; select count (*) as USER2_ASP_CITI_USER_ACCOUNT from USER2.USER_ACCOUNT; Option 2.    This simple sql will get you all the rows count for all tables in schemas that you want. SELECT OWNER,table_name, num_rows counter from DBA_TABLES WHERE owner in  ('USER1','USER2') ORDER BY 1,2; sample output : select count(*) from USER1.QP_INFO; select count(*) from USER1.REFUND_ENTRY;   Option 3 . 1. Using DBA_TABLES+ You can gather the Database statistics using DBMS_STATS.GATHER_DATABASE_STATS; ...

Purging a Subset of Records from the Database Audit Trail You can manually remove records from the database audit trail tables. This method can be useful if you want to remove a specific subset of records. You can use this method if the database audit trail table is in any tablespace, including the SYSTEM tablespace. For example, to delete audit records that were created later than the evening of February 28, 2009 but before March 28, 2009, enter the following statement: DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM'); Alternatively, to delete all audit records from the audit trail, enter the following statement: DELETE FROM SYS.AUD$; Only the user SYS or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail. Note: If the audit trail is full and connections are being audited (that is, if...

We always to test the database for various reasons we will need to create a tables  with a lot of data in it.We can use below script that will allow us to create a table and populate the table with 10,00,000 rows with simple plsql block. This is just for demo purpose and have been tested.   -- Create and populate a small table.    CREATE TABLE lookup ( id NUMBER(10), description VARCHAR2(50) ); ALTER TABLE lookup ADD ( CONSTRAINT lookup_pk PRIMARY KEY (id) ); INSERT INTO lookup (id, description) VALUES (1, 'ONE'); INSERT INTO lookup (id, description) VALUES (2, 'TWO'); INSERT INTO lookup (id, description) VALUES (3, 'THREE'); COMMIT;           -- here we will create and populate a larger table that we will later partition.  CREATE TABLE big_table ( id NUMBER(10), created_date DATE, lookup_id NUMBER(10), data VARCHAR2(50) ); DECLARE l_lookup_id lookup.id%TYPE; l_create_date ...