• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Threaded_execution=true Prevents OS Login As Sysdba in Oracle Database 12c

by admin

Connecting “/ as sysdba” is successful if threaded_execution=false. It fails if threaded_execution=true. Lets verify this with an actual example:

1. Set the threaded_execution parameter value as true.

SQL> show parameter threaded_execution;

NAME                                  TYPE         VALUE
------------------------------------  -----------  ------------------------------
threaded_execution                    boolean      FALSE
SQL> connect / as sysdba
Connected.

SQL> alter system set threaded_execution=true scope=spfile;  
System altered.

2. Shutdown and start the database instance for the changes to take effect.

SQL> shutdown immediate;
Database closed.
Database dismounted.
SQL> startup
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size    2289016 bytes
Variable Size  322962056 bytes
Database Buffers   79691776 bytes
Redo Buffers    8429568 bytes
Database mounted.
Database opened.

3. Verify if you can login into the database now.

SQL> connect / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 7 07:24:59 2014

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

ERROR:
ORA-01017: invalid username/password; logon denied

As you can see from the output, user can not login into the database. The only solution here is to use the username password for sys user.

$ sqlplus sys/XXXXXX as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 7 07:25:05 2014

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


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

You can also verify the threaded_execution parameter value.

SQL> show parameter threaded_execution

NAME                                  TYPE          VALUE
------------------------------------  -----------   ------------------------------
threaded_execution                    boolean       TRUE

4. Change the threaded_execution parameter value back to false to be able to login directly into the database without providing password.

SQL> alter system set threaded_execution=false scope=spfile;

System altered.

5. Take a restart of database instance for the changes to take effect.

SQL> shutdown immediate;
startup;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size    2289016 bytes
Variable Size  322962056 bytes
Database Buffers   79691776 bytes
Redo Buffers    8429568 bytes
Database mounted.
Database opened.

6. Verify if you can login into the database directly now.

SQL> connect / as sysdba
Connected.
SQL>

Conclusion

When the initialization parameter threaded_execution is set to TRUE, it enables the multithreaded Oracle model, operating system authentication is not supported. Attempts to connect to the database using operating system authentication (for example, CONNECT / AS SYSDBA or CONNECT / ) will fail in that case.

Oracle 12c New Feature – Multi-Threaded architecture of processes

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. Define redo log files in an Oracle RAC environment
  2. How to drop and recreate TEMP Tablespace in Oracle
  3. How to Monitor Process Memory Usage on Oracle Pluggable Databases
  4. TRUNCATE TABLE not releasing space from tablespace
  5. How to Clone PDB With Oracle Key Vault (OKV)
  6. How to list all the named events set for a database
  7. Oracle Database 12c2 : CPU_COUNT is Wrong
  8. How to Drop Undo Tablespace in Oracle Database
  9. Table Vs. Materialized View
  10. How to Export and Import Data Guard Broker Configuration in Oracle 19c

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright