Troubleshooting issues in Oracle, RedHat Linux, Windows and lot more!.

ORA-12560  is common error on Windows that results from any number of different causes. ORA-12560 is also very common exception when we have two Oracle homes. Mostly ORA-12560 occurs when we have Oracle Client configuration related issues but some times it may be due to Server side and network related issues. To resolve ORA-12560 we need to ensure following points: - Ensure Database Service is up. - Ensure listener is up and pointing to desired database service. - Ensure all oracle variables are configured correctly (ORACLE_BASE, ORACLE_HOME, ORACLE_SID, PATH, TNS_ADMIN). - On Windows: Ensure Full Access is granted to ORACLE_HOME and its sub-directories. In Windows OS: --------------- C:\Users\Home>set ORACLE_HOME=F:\app\Home2\product\11.2.0\dbhome_1 C:\Users\Home>SET ORACLE_SID=RED C:\Users\Home>sqlplus "/as sysdba" Enter user-name: ERROR: ORA-12560: TNS:protocol adapter error C:\Users\Home>oradim -start -sid RED C:\Users\Home>sqlplus &qu

If you want to clear screen in sqlplus window there is small cmd that you need to remember which is below, SQL> select username from dba_users; USERNAME -------------------- BOCMS BOAUDIT  CKI8727 GRU9151 MNA9189 FRE9283  KKO8750  KMU8649 HME9041 ZLP1492 DBE9176  QRA8651  PFI8706 SQL> host cls Once you hit enter the screen will be cleared. SQL> select username from dba_users; USERNAME -------------------- BOCMS BOAUDIT  CKI8727 GRU9151 MNA9189 FRE9283  KKO8750  KMU8649 HME9041 ZLP1492 DBE9176  QRA8651  PFI8706 SQL>clear screen Once you hit enter the screen will be cleared.

SQL*Plus Commands SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to: Enter, edit, store, retrieve, and run SQL statements List the column definitions for any table Format, perform calculations on, store, and print query results in the form of reports Access and copy data between SQL databases The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics: /  – Execute the current SQL statement in the buffer – same as RUN ACCEPT – Accept a value from the user and place it into a variable APPEND – Add text to the end of the current line of the SQL statement in the buffer AUTOTRACE – Trace the execution plan of the SQL statement and gather statistics BREAK – Set the formatting behavior for the output of SQL statements BTITLE – Place a title on the bottom of each page in the printout from a SQL statement CHANGE – Replace text on the current line of the

We can display the table very clearly in sqlplus by following simple steps, 1. First set the page size and line size Eg: set pagesize 10000 linesize 100 2.Format the column of the table that you are querying Eg: column columnname format a20 Eg: column Telephone format 99999999

1)With the help of Alter database Command We can make the database offline or online with the help of the Alter database command. The Alter Database command to make the database offline is: ALTER DATABASE database name SET Offline If we want to make the database online we can use the following Alter Database command: ALTER DATABASE database name SET Online 2)With the help of the Db_options We can also use the db_options command to make a database offline or online.To make a database offline we can use the following command: sp_dboption databasename , ' offline' , true To make the database online we can use the following command: sp_dboption databasename , ' offline' , false 3)With the help of Sql server management studio We can also use the Sql server management studio to make a database offline as shown in the given figure. To make database offline we have to follow steps show in fig 1 and to bring back the database online we needs to

When you do full database export it fails will following errors as below. Export: Release 11.2.0.1.0 - Production on Mon Sep 30 12:04:38 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=user1 directory=data_pump_dir dumpfile=user1.dmp logfile=user1.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('user1&

If you have two databases on the same server, how to  connect in SQL*Plus? ---------- C:\>set ORACLE_SID=orcl C:\>echo %oracle_sid% orcl C:\>SQLPLUS /NOLOG SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 30 12:26:08 2013 Copyright (c) 1982, 2010, Oracle.  All rights reserved. SQL> conn / as sysdba Connected. SQL> show user; USER is "SYS" SQL> SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- ORCL SQL> C:\>set ORACLE_SID=orcl1 C:\>echo %oracle_sid% orcl1 C:\>SQLPLUS /NOLOG SQL*Plus: Release 11.2.0.1.0 Production on Mon Sep 30 12:26:08 2013 Copyright (c) 1982, 2010, Oracle.  All rights reserved. SQL> conn / as sysdba Connected. SQL> show user; USER is "SYS" SQL> SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- ORCL1 SQL

When you connect using sysdba or sysoper, then you are sys or public user, no matter what username/password you provide : $ sqlplus / as sysdba SQL*Plus: Release 10.1.0.3.0 - Production on Mon Jan 2 18:00:59 2006 Copyright (c) 1982, 2004, Oracle.  All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> sho user USER is "SYS" SQL> conn / as sysoper Connected. SQL> sho user USER is "PUBLIC" SQL> conn scott/tiger as sysdba Connected. SQL> sho user USER is "SYS" SQL> conn test/test as sysoper Connected. SQL> sho user USER is "PUBLIC"

SQL> desc dba_objects;  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OWNER                                              VARCHAR2(30)  OBJECT_NAME                                        VARCHAR2(128)  SUBOBJECT_NAME                                     VARCHAR2(30)  OBJECT_ID                                          NUMBER  DATA_OBJECT_ID                                     NUMBER  OBJECT_TYPE                                        VARCHAR2(19)  CREATED                                            DATE  LAST_DDL_TIME                                      DATE  TIMESTAMP                                          VARCHAR2(19)  STATUS                                             VARCHAR2(7)  TEMPORARY                                          VARCHAR2(1)  GENERATED                                          VARCHAR2(1)  SECONDARY                                          VARCHAR2(1)  NAM

SQL> show parameter sga_max_size ; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_max_size big integer 1G SQL> show parameter sga_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sga_target big integer 900M SQL> show parameter pool; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_keep string buffer_pool_recycle string global_context_pool_size string java_pool_size big integer 0 large_pool_size big integer 0 olap_page_pool_size big integer 0 shared_pool_reserved_size big integer 30M shared_pool_size big integer 600M streams_pool_size big integer 252M If you  want to increase streams_pool_size by this command. alter system set STREAMS_POOL_SIZE =16M sid = 'orcl';

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_4214.htm

If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema. This role tells you the grantee, granted_role, whether they have admin option granted, and whether the role is their default role: SQL> desc dba_role_privs Name         Null?    Type ------------ -------- ------------ GRANTEE               VARCHAR2(30) GRANTED_ROLE NOT NULL VARCHAR2(30) ADMIN_OPTION          VARCHAR2(3) DEFAULT_ROLE          VARCHAR2(3) To find a list of all users with DBA privilege execute the following code: SQL> select * from dba_role_privs where granted_role='DBA'; GRANTEE   GRANTED_ROLE ADM DEF --------- ------------ --- --- SYS       DBA          YES YES SYSTEM    DBA          YES YES

Oracle Installation Prerequisites Perform either the Automatic Setup or the Manual Setup to complete the basic prerequisites. The Additional Setup is required for all installations. Automatic Setup If you plan to use the "oracle-validated" package to perform all your prerequisite setup, follow the instructions at  http://public-yum.oracle.com  to setup the yum repository for OL, then perform the following command. # yum install oracle-validated All necessary prerequisites will be performed automatically. It is probably worth doing a full update as well, but this is not strictly speaking necessary. # yum update Manual Setup If you have not used the "oracle-validated" package to perform all prerequisites, you will need to manually perform the following setup tasks. In addition to the basic OS installation, the following packages must be installed whilst logged in as the root user. This includes the 64-bit and 32-bit versions of some packages.