Learn Database Concepts

[root@rac1 Desktop]# cd /dev [root@rac1 dev]# ls sd* sda  sda1  sda2  sdb  sdb1  sdc  sdc1  sdd  sdd1  sde  sde1  sdf  sdg [root@rac1 dev]# /usr/sbin/oracleasm listdisks DISK1 DISK2 DISK3 DISK4 [root@rac1 dev]# fdisk /dev/sdf Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel with disk identifier 0xd8725891. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable. Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite) WARNING: DOS-compatible mode is deprecated. It's strongly recommended to          switch off the mode (command 'c') and change display units to          sectors (command 'u'). Command (m for help): n Command action    e   extended    p   primary partition (1-4) p Partition num...

EXPDP ERROR : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT Export: Release 11.2.0.4.0 - Production on Thu Sep 24 11:04:40 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39021: Database compatibility version 11.2.0.4.4.4.4 is not supported. Solution : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT version=11.2.0

EXPDP ERROR : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT Export: Release 11.2.0.4.0 - Production on Thu Sep 24 11:04:40 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39021: Database compatibility version 11.2.0.4.4.4.4 is not supported. Solution : oracle@Linux1:[/u01/app/oracle/dpump] $ expdp atoorpu directory=DPUMP dumpfile=SCOTT_24Sep2015.dmp logfile=SCOTT_24Sep2015.log schemas=SCOTT version=11.2.0

I n the events when you want to know what all patches have been applied to the oracle database, you can just goto oracle_home/opatch and type lsinventory. but you can also do the same by using the below query in the database.  Which Patch has been applied? SET linesize 200 pagesize 200 col action_time FOR a28 col version FOR a10 col comments FOR a35 col action FOR a25 col namespace FOR a12 SELECT * FROM registry$history; ACTION_TIME                  ACTION                    NAMESPACE    VERSION            ID COMMENTS                         BUNDLE_SERIES ---------------------------- ------------------------- ------------ ---------- ---------- ----------------------------------- ------------------------------ 24-AUG-13 12.03.45.119862 PM APPLY             ...

I was trying to setup Oracle RAC on my local machine and grid installation worked fine untill 65-70% and I got this weird error. After researching many blogs and sites. I was able to overcome that error and succesfully install the Grid Infrastructure. [PRCF-2010 : All connections to the remote nodes got refused. Cannot proceed with the file transfer. There has to be at least one remote node to send the installer files! I have some issues with the instalation of the 11G R2 Grid... Passed all the prereq checks, everything working but when reaching the - Performing Remote Operations stage it freezes. Checked the errors and when reaching that stage an error is dumped in the oraInstall.err file: Exception in thread "Install API Thread" java.lang.NullPointerException at oracle.cluster.deployment.ractrans.ClientHandlerSupervisor.threadCleanup(ClientHandlerSupervisor.java:926) at oracle.cluster.deployment.ractrans.RACTransfer.cleanup(RACTransfer.java:1749) at oracle.cluster.deploymen...

Here is the step by step process on how to add diskgroup to existing ASM instance via ASMCA. on cmd prompt set the ASM insatance and then invoke asmca. by using below cmd. $ asmca

Here is the step by step process on how to add diskgroup to existing ASM instance via ASMCA. on cmd prompt set the ASM insatance and then invoke asmca. by using below cmd. $ asmca

Count rows in all tables or similar tables in database: I have come across many situations as a DBA when I have to count the total number of rows in a tables in a table across the database. I have written this simple code to help us with that. If you are a DBA you might also say why not just query the num_rows from all_tables or user_tables. But that is not possible as it might not give you the accurate count, as we all know that you cant get the right numbers with all_tables column, at least not until you have gather the stats for all these tables before you run the query. -----   COUNT ROWS FROM ALL TABLES AND PRINT THEM : DECLARE   result sys_refcursor;   strTableOwner VARCHAR2(100);   strTableName1 VARCHAR2(100);   strQuery      VARCHAR2(4000);   rec           NUMBER; BEGIN   OPEN result FOR SELECT owner,table_name FROM all_tables WHERE table_name IN (   'ABC') order by owner;     -->> yo...

In this case I have  requirement where I need to update account_status column in same table with user status. When ever user deleted flag is 0 Lets create a table: Create table users users (username varchar2(20)),fullname varchar2(30),account_status varchar2(10) default OPEN,deleted number(1), LOCK_DATE date); Now insert some values: insert into users values('ARVIND111','ARVIND KUMAR','',,''SYSDATE); insert into users values('RAGHU111','RAGHU RAM','','',SYSDATE); insert into users values('RAJ111','RAJ KUMAR','',''SYSDATE); insert into users values('HARI111','HARI KRISHNA','',''SYSDATE); Lets say you want to update a column with the account status 'OPEN' or 'LOCKED'. when ever we have a update in another column. Sample : In the below case, when ever we update deleted 0, we will update another column saying the account_status is open. If deleted ...

At the LSNRCTL prompt: $ lsnrctl LSNRCTL> set current_listener LISTENER  Current Listener is LISTENER LSNRCTL> set log_file /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) LISTENER parameter "log_file" set to /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log The command completed successfully LSNRCTL> save_config Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) Saved LISTENER configuration parameters. Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak The command completed successfully LSNRCTL> exit Note : If you get this error " TNS-01251: Cannot set trace/log directory under ADR" refer to below link click on this >>  TNS-01251

[oracle@linux01 trace]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 11:24:18 Copyright (c) 1991, 2009, Oracle.  All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set log_file listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) TNS-01251: Cannot set trace/log directory under ADR  Solution : DIAG_ADR_ENABLED_LISTENER=OFF     -- >> add this line to you listener.ora reload the listener. $ lsnrctl reload  Now lets try again to reset: LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set log_file /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) LISTENER parameter "log_file" set to /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log The command completed ...

[oracle@linux01 trace]$ lsnrctl LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 16-SEP-2015 11:24:18 Copyright (c) 1991, 2009, Oracle.  All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set log_file listener1 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) TNS-01251: Cannot set trace/log directory under ADR  Solution : DIAG_ADR_ENABLED_LISTENER=OFF     -- >> add this line to you listener.ora reload the listener. $ lsnrctl reload  Now lets try again to reset: LSNRCTL> set current_listener LISTENER Current Listener is LISTENER LSNRCTL> set log_file /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux01)(PORT=1521))) LISTENER parameter "log_file" set to /u01/app/oracle/diag/tnslsnr/linux01/listener/trace/listener1.log The command completed ...

I know this is a common problem that I have come across many time, while installing oracle software on Virtual machine  >>  Linux (Guest OS). I thought of sharing this info as this might help others to .. Problem / Error : I was trying to install oracle software on virtual box. I keep getting this error  information provided for listener is currently in use by another software . (or)  Port you have provided xxxx (1523) is being used by another program. Solution :  Step 1 : Make sure you login as Root user. Step 2 : make sure your valid ip address  in   >>    /etc/hosts Step 3 : Make sure no other program is using listener port   >>  netstat -ntap | grep 1521 In my case initially it had wrong ip address in the /etc/hosts  file.

I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people. This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's. sample code: declare     result sys_refcursor;     V_OWNER Varchar2(100):='SCHEMA_NAME'; --- update this with schema you want to audit.     strTableOwner Varchar2(100); strTableName Varchar2(100);     strQuery varchar2(300); begin open result for     select owner,table_name from user_tables where     table_name in ('ABC','ABC1',ABC2')  and owner = 'V_OWNER' order by table_name; loop     fetch result into strTableOwner,strTableName;     exit when result%notfound;           DBMS_OUTPUT.P...

I was playing around with some plsql code today.Just posting this sample plsql code as I thought this can help people. This is a sample code that will search for all tables with name  ABC,ABC1,ACB2 in all schema's in database and execute a NOAUDIT against these tables. This sql can be modified to accommodate any changes where you want to run a query against all tables & schema's. sample code: declare     result sys_refcursor;     strTableName Varchar2(100); strTableName1 Varchar2(100);     strQuery varchar2(4000); begin open result for     select owner,table_name from all_tables where     table_name in ('ABC','ABC1',ABC2') order by table_name; loop     fetch result into strTableName,strTableName1;     exit when result%notfound;           DBMS_OUTPUT.PUT('NOAUDIT DELETE,UPDATE on '||strTableName||'.'||strTableName1||';');     strQuery := 'NOAUDIT DELETE,UPDATE on '||strTab...

BACKUP ORACLE HOME AND INVENTORY Oracle Home and Inventory Backup ----------------------------------------- tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz Note:  tar -cvf <destination_location> <source_location> ORACLE_HOME & ORACLE_CRS_HOME backup’s (for all nodes in RAC) $ tar -zcvf db_1_bak.tar.gz db_1 $ cd /u01/app/oracle/product/11.2 $ ls -al total 353532 drwxr-xr-x  3 oracle oinstall      4096 Sep  6 17:32 . drwxrwxr-x  3 oracle oinstall      4096 Sep 12  2013 .. drwxr-xr-x 80 oracle oinstall      4096 Jun 11 02:03 db_1 -rw-r--r--  1 oracle oinstall 361627648 Sep  6 17:34 db_1_bak.tar.gz Controlfile Backup --------------------- alter database backup controlfile to trace;  show parameter user_dump_dest (go to udump dest and make the note of controlfile trace)