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

Below are the some of the sql queries which helps to find the log directories in oracle database server. select * from v$logfile; select * from v$log_history; show parameter debug; select * from dba_profiles; select profile,resource_name,resource_type,limit from dba_profiles; select window_name, repeat_interval, duration from dba_scheduler_windows where window_name like 'WEEK%';

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace. Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped. When you drop a tablespace, the file pointers in the control file of the associated database are removed. You can optionally direct Oracle Database to delete the operating system files (datafiles) that constituted the dropped tablespace. If you

SQL Queries to drop oracle tablespace, -- drop tablespace: syntax: DROP TABLESPACE <tablespace_name>; eg: DROP TABLESPACE tspace; -- drop tablespace including contents: syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS; eg: DROP TABLESPACE tspace INCLUDING CONTENTS; -- drop tablespace including contents & datafiles:     syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES; eg: DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES; -- drop tablespace including contents & datafiles -- where referential constraints exist:     syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; -- drop tablespace after datafile was accidentally dropped: conn / AS sysdba CREATE TABLESPACE testbed DATAFILE 'c:\temp\testbed.dbf' SIZE 10M BLOCKSIZE 4096 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K

desc dba_tablespaces select * from dba_tablespace_usage_metrics select * from dba_directories select * from user_tablespaces Select * from dba_data_files Select * from dba_free_space

Creating Tablespaces Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the  SYSTEM  tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The  SYSTEM  tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the  SYSTEM  tablespace or take it offline. The  SYSAUX  tablespace, which acts as an auxiliary tablespace to the  SYSTEM  tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the  SYSTEM  tablespace, management of the  SYSAUX  tablespace requires a higher level of security and you cannot re

Oracle Network Configuration In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration. Assumptions Listener.ora Tnsnames.ora Sqlnet.ora Testing The example files below are relevant for an Oracle installation and instance with the following values. Parameter   Value HOST hostname ORACLE_HOME /u01/app/oracle/product/9.2.0 ORACLE_SID ORCL DOMAIN world Listener.ora The listerner.ora file contains server side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the server. Here is an example of a listener.ora file from Windows 2000. LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TC

How do I copy Oracle Database from one server to another server? Defenitely there will be a need to copy Production Oracle DB at Demonstration/Training, QA/Acceptance or Development environment. Most of the times we will be using Oracle Export utility. step-by-step how to copy database to another server. Note: you may want to change DB name while copying it to another destination. This case does not change the procedure much and steps required to do so will be specially highlighted in the article. Also, you may want to change Oracle DBID of the Database. the reason is that Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. For changing Oracle DBID for a database please refer to the corresponding HOWTO. OK. Here we go. We will imagine a situation when you need top copy PRO (production) DB into TST (test) DB on test server... 1. Instance service creation. (If you

Pre-Requisite: You should have the Oracle database 10g, which you want to migerate. Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6) Step 1) Installing Oracle 11g Home We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home. Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0 then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0 Just a parallel 11.1.0 directory can be created and we can install oracle home in this location. Start the installation using the below command ./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst Screen 1 – Select Product Install select “Oracle Database 11g” Screen 2 – Select Installation Method Choose “Advanced Installation” Screen 3 – Specify Inventory directory and creditials Note: We are providing local inventory here inside the corresponding ORACLE_HOME l

Oracle Import getting stuck Processing object type SCHEMA_EXPORT/ /database1/rdbm6/export> tail -200f impdp_D.SPORTS_03192012.log ;;; Import: Release 11.2.0.2.0 - Production on Mon Mar 19 15:34:10 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_FULL_02":  "/******** AS SYSDBA" REMAP_SCHEMA=SPORTS:SPORTS DIRECTORY=dpump_dir DUMPFILE=expdp_D.SPORTS_02282012.dmp LOGFILE = impdp_D.SPORTS_03192012.log REMAP_TABLESPACE = SPORTS_DATA_TS:SPORTS_DATA_TS , SPORTS_INDEX_TS:SPORTS_INDEX_TS Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SPORTS" already exists Processing object type SCHEMA_EXPORT/ROLE_GRANT Proc

Here is the way to find to SGA and PGA Values SQL> select * from v$sga; NAME                      VALUE -------------------- ---------- Fixed Size           ########## Variable Size        ########## Database Buffers     ########## Redo Buffers         ########## SQL> show sga; Total System Global Area  530288640 bytes Fixed Size                  2256864 bytes Variable Size             394264608 bytes Database Buffers          125829120 bytes Redo Buffers                7938048 bytes SQL> select max (pga_used_mem) / 1024/1024 M from v$process;          M ---------- 123.972422 SQL> show parameter pga NAME                                 TYPE        VALUE ------------------------------------ ----------- ---------------- pga_aggregate_target                 big integer 0 SQL> select * from v$pgastat; NAME                      VALUE UNIT -------------------- ---------- -------------------- aggregate PGA target ########## b

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