Query to Check available Space of an Oracle Database

Login to the database as SYS user

sqlplus as / sysdba

Then copy and paste below sql query in the command line or PL/SQL editor

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size",

round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space",

round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select    bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used,

(select sum(bytes) as p from dba_free_space) free

group by free.p;

 

Windows: How To Delete and Recreate the TNS Listener Service When the Service Fails To Start Consistently At Machine Reboot (Doc ID 313250.1)

APPLIES TO:

Oracle Net Services - Version 8.1.7.4.0 and later
Microsoft Windows x64 (64-bit) - Version: 2008 R2
Microsoft Windows (32-bit)
Microsoft Windows (32-bit)Microsoft Windows

GOAL

Oracle TNS Listener service fails to start automatically at boot time (e.g. after moving Windows Server to new location) or fails to start manually.

SOLUTION

You need to re-create the Windows service associated to the Oracle listener by deleting the Windows service and creating a new service for the Oracle TNS Listener.

1. Deleting the service

At your earliest convenience, or next scheduled maintenance window, delete and recreate the Windows service associated to the Oracle TNS Listener. You can do this either with the help of a tool (recommended) or by hand-editing the registry. In both cases you need to stop the listener service before making any changes.

Using a tool
There are quite a few tools to manipulate Windows services, in the Resource Kit or from third parties. Also there is a tool called SC and which is available in the base Windows distribution (at least for Windows XP and Windows 2003).

1. Lookup the windows service name that you want to remove — use Properties in the Windows Services panel to copy the full name
2. Open a Command Prompt window and run the following:

   sc delete <your_windows_Service_name>

3. Check that the Windows service has been removed — use F5/Refresh in the Windows Services panel

In case you do not have the SC tool available then you need to download the Windows Resource Kit — there you will find the instsrv.exe tool.

1. Lookup the windows service name that you want to remove — use Properties in the Windows Services panel to copy the full name
2. Open a Command Prompt Window and run the following:

   instsrv /deleteService <your_windows_Service_name>

3. Check that the Windows service has been removed — use F5/Refresh in the Windows Services panel

Manually editing the Windows Registry

Warning: editing the Windows Registry by hand is dangerous and may render your system unusable. Proceed at your own risk !

You need to follow these steps:

• Launch the Registry Editor (e.g. Start / Run / regedit )
• Drill down to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
• Locate and delete the the key which contains your Oracle Home name and your listener name (e.g. OracleOraHomeTNSListenerName)
• Reboot the system

 

2. Creating the new service

When started and logged on as the Oracle user, go to a Command Prompt and start the listener:

lsnrctl start <listener_name>

Replace <listener_name> with the name of your listener; if you are operating on the default listener then you may use the value LISTENER or leave it empty.

An OS error 1060 will be seen (which is normal as the Windows service is missing and is being created); the listener should start correctly.

3. Test the listener

Once started the listener is started, check that a Windows service for the TNS Listener was created in the Window Services panel and set to startup automatically (change if necessary). Then test the automatic listener startup by performing another reboot.

 

How to recover missing datafiles?

ORA-01110: ORA-01565:  ORA-27041

Recovery of SID went wrong though the DB opened successfully because unfortunately we used old control file and missed almost 11 data files in controlfile.sql file and which created soft link in dbs path.

In order to overcome the situation below steps performed and database become consistent. If in case in the future if we face same issue, we can use below steps confidently.

1) Find the missing datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

/oracle/SID/121/dbs/MISSING00268                     268 PSAPSID  ONLINE

/oracle/SID/121/dbs/MISSING00269                     269 PSAPSID ONLINE

 

2) Take tablespace or datafiles offline:

Come with below error because data file already in offline

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ offline;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ offline;

Database altered.

 

3) Rename missing datafiles with original files: 

SQL> alter database rename file ‘/oracle/SID/121/dbs/MISSING00268’ to ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’;

SQL> alter database rename file ‘/oracle/SID121/dbs/MISSING00269’ to ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’;

4) Shutdown and mount the database:

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6.4425E+10 bytes

Fixed Size                  3729840 bytes

Variable Size            7381976656 bytes

Database Buffers         5.6908E+10 bytes

Redo Buffers              130486272 bytes

Database mounted.

5) Perform media recovery:

SQL> recover datafile 268;

Media recovery complete.

SQL> recover datafile 279;

Media recovery complete.

6) Open the database:

SQL> alter database open;

Database altered.

7) check the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;  – After recovery data files online_status went recover to offline.

8) Bring the datafiles online:

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_242/gp1.data242’ online;

Database altered.

SQL> alter database datafile ‘/oracle/SID/sapdata/sapdata2/gp1_243/gp1.data243’ online;

Database altered.

9) check again the status of datafiles:

SQL> set line 250

SQL> col FILE_NAME for a45

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;

 

ORA-12557: TNS:protocol adapter not loadable

Question:  What causes the ORA-12577 error below?  I am running on Windows.

ORA-12557: TNS:protocol adapter not loadable

The ORA-12577 error is related to Windows Environment or Oracle Home PATH because sqlplus command works smoothly when I execute it inside ORACLE_HOME\bin.

Answer:  There are two solutions to this issue:

1 - put the Oracle DB Home in front of the other paths in the PATH environment variable.

2 - Remove ORACLE_HOME From environment Variable and re-boot PC

Oracle author Osama Mustafa notes a solution to the ORA-12577 error.

RUN: SYSDM.CPL to open Windows System Properties

Click on Advanced Tab > Environment Variables…

Click the Path variable under System Variable, then click Edit…

Change the order between Oracle Client Home and Oracle DB Home:

From: D:\oracle\product\10.2.0\client_1\bin;D:\oracle\product\10.2.0\db_1\bin;

To: D:\oracle\product\10.2.0\db_1\bin;D:\oracle\product\10.2.0\client_1\bin;

In other words, put the Oracle DB Home in front of the other path.

 How to set FILESYSTEMIO_OPTIONS for use with RSA Identity Governance and Lifecycle on a remote Oracle database where Automatic Storage Management (ASM) is not being used for better storage performance 

Article Number

000035132

Applies To

RSA Product Set: RSA Identity Governance and Lifecycle
RSA Version/Condition: All
Platform (Other): Non-ASM Oracle database instances (soft-appliance and/or remote databases)
 

Issue

For non-ASM (Oracle Automatic Storage Management) database implementations, RSA recommends the explicit setting of the FILESYSTEMIO_OPTIONS initialization parameter to SETALL in order to enable direct and asynchronous disk I/O. It can make a huge difference in storage performance. This is for non-ASM Oracle database implementations only.

To determine if your Oracle database is an ASM implementation:

Login as SYSDBA and run the following SQL command:

$ sqlplus / as SYSDBA
SQL> SELECT * FROM v$asm_client:

If this view returns no rows, then your system is a non-ASM Oracle implementation.


For more information on the FILESYSTEMIO_OPTIONS initialization parameter, visit the Oracle web site.  

• Click for information on Oracle 11g
• Click for more information on Oracle 12c.

Task

Check the current value of this parameter to see if it needs to be set. The default value is typically none.

Login as sysdba, then run the following:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

If the value is something other than SETALL, it needs to be modified.  For example,

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none

Resolution

Modify the FILESYSTEMIO_OPTIONS setting to SETALL, restart Oracle, and check that the setting has been modified:

1. Modify the setting:

SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

2. Restart Oracle

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

3. Check that the setting has been updated:

SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

For example,

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 4 09:53:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SET LINE 300
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           none
SQL>
SQL>
SQL> ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=SETALL SCOPE=SPFILE;

System altered.

SQL>
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> STARTUP
ORACLE instance started.

Total System Global Area 7063207936 bytes
Fixed Size                  2940568 bytes
Variable Size            1308623208 bytes
Database Buffers         5737807872 bytes
Redo Buffers               13836288 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> SHOW PARAMETER FILESYSTEMIO_OPTIONS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
filesystemio_options                 string                           SETALL
SQL>

 

ORA-01012: not logged on error

HOME » ORA-01012: NOT LOGGED ON ERROR

ORA-01012: not logged on error while trying to start the oracle database.

Solution:

To resolve this error  remove the orphaned shared memory segment using sysresv utility. sysresv command will list the currently allocated IPC resources for shared memory and remove the shared memory segment using ipcrm -m command.

Now start the database

 

 Determine an index needs to be rebuilt

First, the relevant index should be analyzed. You can do this with the following command.

SQL> analyze index <username>.<index_name> validate structure;

Index analyzed.

The analysis process fills the table “sys.index_stats”. This table contains only one row and therefore only one index can be analyzed at a time. Information about the relevant index can be obtained from the sys.index_stats table in the analyzed session.

SQL> select del_lf_rows,lf_rows,height,lf_rows,lf_blks from sys.index_stats;

DEL_LF_ROWS    LF_ROWS    HEIGHT    LF_ROWS LF_BLKS

----------- ---------- ---------- ---------- --------------------------------------------------

  842     41356545           3       41356545 109441

After the analysis, according to the data in the “sys.index_stats” table, if any of the following conditions occur you can decide whether rebuild the index or not.

If the percentage of deleted rows exceeds 30% of the total. So if del_lf_rows / lf_rows> 0.3 in the sys.index_stats table.

If ‘HEIGHT’ is greater than 4.

If the number of rows in the index(LF_ROWS) is much less than (LF_BLKS). This indicates that too many records have been deleted from the index.

When one of these conditions occurs you can rebuild the index as follows.

SQL> alter index <username>.<index_name> rebuild online;

Index altered.