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.

How to use the purge command in adrci

 

How to use the purge command in adrci

Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:

adrci> purge -age 48 -type trace

Other examples (2160 hrs = 90 days):

purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :

adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):

set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched

Remove incidents that are older than 90 days:

adrci> purge -age 2160 -type incident

Another check of the incident list shows that the number has been reduced:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

 

Enable and disable data guard broker

https://logic.edchen.org/how-to-stop-data-guard-services/

1. Login to primary db server

$ dgmgrl sys/Manager_1@sslcdb

DGMGRL> show configuration

2. Check the apply and transport status:

Disable Data Guard with Broker

DGMGRL> show database sslcdb

Database - SSLCDBDR

Role:               PRIMARY

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

@stanby

$ dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> show database sslcdbdr

Database - SSLCDBDR

  Role:               PHYSICAL STANDBY

3. DGMGRL Stop Transport

disable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-OFF';

Succeeded.

4. DGMGRL Stop Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-OFF';

Succeeded.

5. Disable DG Configuration

DGMGRL> DISABLE CONFIGURATION;

Disabled.

Enable Data Guard with Broker

6. Disable DG Configuration

DGMGRL> ENABLE CONFIGURATION;

Enableabled.

7. DGMGRL Start Transport

Enableable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-ON';

Succeeded.

8. DGMGRL Start Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-ON';

Succeeded.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> select name, value from v$parameter where name = 'dg_broker_start';

NAME                       VALUE

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

dg_broker_start            TRUE

 SQL query to get DB info

spool dbinfo.html
set markup html on
select * from v$version;
select * from v$instance;
select * from v$parameter;
select * from v$resource_limit;
select * from dba_users;
select * from dba_registry;
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
ORDER BY a.ksppinm;

set markup html off
spool off