Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ssl connection to 12c db failed with ORA-01017: invalid username/password; logon denied

satyanarayana.mekala-OracleMar 15 2017 — edited Mar 16 2017

Hi,

I am trying to establish ssl connection to oracle db with autologin wallet.

I have followed below document to create ssl certificates for server side and client:

http://www.oracle.com/technetwork/topics/wp-oracle-jdbc-thin-ssl-130128.pdf from page 21,22

I have placed server certificates on server side and client certificate on client side(As you can see in sqlnet.ora files)

In my 12c Oracle DB, I have created an user as below

++++++++++

create user client_test identified externally as ‘CN=client_test,C=US’;

grant create session to client_test;

++++++++++

But when I do login from sqlplus, I am getting below error;

Am I missing anything in this setup?

bash-4.1$ sqlplus /@MATSDBSSL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 15 05:49:46 2017

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

bash-4.1$

+++++++++++++++++++++++++++++++

Listener on oracle DB as looks good.

+++++++++++++++++++++++++++++++

bash-4.1$ /scratch/aime1/work/MATSDB/bin/lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 15-MAR-2017 02:50:25

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=slc12lxl.us.oracle.com)(PORT=1234)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 15-MAR-2017 02:49:20

Uptime 0 days 0 hr. 1 min. 5 sec

Trace Level support

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /scratch/aime1/work/MATSDB/network/admin/listener.ora

Listener Log File /scratch/aime1/work/MATSDB/diag/tnslsnr/slc12lxl/listener/alert/log.xml

Listener Trace File /scratch/aime1/work/MATSDB/diag/tnslsnr/slc12lxl/listener/trace/ora_127178_140712168318400.trc

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=slc12lxl.us.oracle.com)(PORT=1234)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=slc12lxl.us.oracle.com)(PORT=2484)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1234)))

Services Summary...

Service "MATSDB.us.oracle.com" has 1 instance(s).

Instance "MATSDB", status READY, has 1 handler(s) for this service...

Service "MATSDBXDB.us.oracle.com" has 1 instance(s).

Instance "MATSDB", status READY, has 1 handler(s) for this service...

The command completed successfully

bash-4.1$

+++++++++

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Root Certificates

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-bash-4.1$ orapki wallet display -wallet root

Oracle PKI Tool : Version 12.1.0.2

Enter wallet password:

Requested Certificates:

User Certificates:

Subject: CN=root_test,C=US

Trusted Certificates:

Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: CN=root_test,C=US

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

truststore Certificates

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-bash-4.1$ orapki wallet display -wallet truststore

Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Trusted Certificates:

Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: CN=root_test,C=US

Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Server side Certificates

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-bash-4.1$ orapki wallet display -wallet server

Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

Subject: CN=server_test,C=US

User Certificates:

Subject: CN=server_test,C=US

Trusted Certificates:

Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: CN=root_test,C=US

Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

client side Certificates

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-bash-4.1$ orapki wallet display -wallet client_wallet

Oracle PKI Tool : Version 12.1.0.2

Requested Certificates:

User Certificates:

Subject: CN=client_test,C=US

Trusted Certificates:

Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US

Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US

Subject: CN=root_test,C=US

-bash-4.1$

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++++++++++++

Server side :

+++++++++++++

listener.ora

+++++++++++++

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.242.233.79)(PORT = 1234))

(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.242.233.79)(PORT = 2484))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1234))

)

ADR_BASE_LISTENER = /scratch/aime1/work/MATSDB/

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/scratch/aime1/work/MATSDB/bin/server)))

TRACE_LEVEL_LISTENER = SUPPORT

TRACE_FILE_LISTENER = listener

TRACE_DIRECTORY_LISTENER = /scratch/aime1/work/MATSDB/network/trace

LOG_FILE_LISTENER = listener

LOG_DIRECTORY_LISTENER = /scratch/aime1/work/MATSDB/network/trace

LOGGING_LISTENER = ON

+++++++++++++

sqlnet.ora

+++++++++++++

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS)

SSL_VERSION=3.0

ADR_BASE = /scratch/aime1/work/MATSDB

SQLNET.WALLET_OVERRIDE = TRUE

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/scratch/aime1/work/MATSDB/bin/server)))

SSL_CLIENT_AUTHENTICATION=FALSE

TRACE_DIRECTORY_SERVER = /scratch/aime1/work/MATSDB/bin/network/trace

trace_level_server = SUPPORT

+++++++++++++

client side:

+++++++++++++

sqlnet.ora

+++++++++++++

SSL_VERSION=3.0

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /scratch/username1/instantclient_12_1/logs

SQLNET.WALLET_OVERRIDE = TRUE

WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/username1/ssl_thin/client_wallet)))

SSL_CLIENT_AUTHENTICATION=TRUE

DIAG_ADR_ENABLED=OFF

TRACE_DIRECTORY_CLIENT=/scratch/username1/instantclient_12_1/trace

LOG_DIRECTORY_CLIENT=/scratch/username1/instantclient_12_1/trace

TRACE_LEVEL_SERVER=SUPPORT

TRACE_LEVEL_CLIENT=SUPPORT

TRACE_UNIQUE_CLIENT=ON

+++++++++++++

tnsnames.ora

+++++++++++++

MATSDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.242.233.79)(PORT = 1234))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = MATSDB.us.oracle.com)

)

MATSDBSSL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.242.233.79)(PORT = 2484))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = MATSDB.us.oracle.com)

)

LISTENER_MATSDB =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.242.233.79)(PORT = 1234))

+++++++++++++

This post has been answered by Vlad Visan-Oracle on Mar 15 2017

Jump to Answer

mseberg

I would review this :

What Is the Redo Log ( Its a little over a page )

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/onlineredo001.htm

Best Regards

mseberg

Srini Chavali-Oracle

Check the Concepts Guide - http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/process.htm#CNCPT1251

HTH
Srini

712622

Go through this link...

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/memory.htm

845641

My question is not WHAT IS REDO LOG or about how LGWR writes to redo log files? You have given me links which i already know? my question is simple which process transferrs data from buffer cache to log buffer and how ? which is not given in these concepts guide.. and also what is IMU buffers ? is it the one from which it transfers changed buffers to log buffer ? Because when waiting for event [ log file switch (private strand flush incomplete) ] oracle says LGWR waits for DBWR to complete flushing redo from IMU buffers into the redo log buffer; when DBWR is complete LGWR can then finish writing the current log, and then switch log files. THANKS

PavanG

It is the sever process that performs the copy from the buffer to the log buffer.

1 person found this helpful

AronTunzi

IMU means "In-memory undo"

From the link of Srini you can read that:

LGWR writes one contiguous portion of the buffer to the online redo log. By separating the tasks of modifying database buffers, performing scattered writes of dirty buffers to disk, and performing fast sequential writes of redo to disk, the database improves performance.

In the following circumstances, LGWR writes all redo entries that have been copied into the buffer since the last time it wrote:

A user commits a transaction (see "Committing Transactions").

An online redo log switch occurs.

Three seconds have passed since LGWR last wrote.

The redo log buffer is one-third full or contains 1 MB of buffered data.

DBWn must write modified buffers to disk.

Jonathan Lewis

Answer

842638 wrote:
Hi EXPERTS,

Im on 11G R2, RHEL 5 my question that when data is modified in database buffer cache how immediately it is copied to redo log buffer. I mean which process copies it. I read it in oracle documentation that when waiting for event [ log file switch (private strand flush incomplete) ] LGWR waits for DBWR to complete flushing redo from IMU buffers into the redo log buffer; when DBWR is complete LGWR can then finish writing the current log, and then switch log files.

In general each session writes its own redo into private buffers (a matched pair - one for the forward changes, one for the undo block changes - it's the latter that's named the IMU pool) and copies the private redo into the public log buffer on commit. There are various events that will cause the private redo to be flushed to the public thread before the commit. One of these is the special case that you've mentioned where DBWR instigates a flush.

If DBWR has to copy a datablock from the db cache to disc it has to copy the latest version of the block, but any redo that describes changes to that block has to be written first. So if DBWR finds that a block it wants to write is subject to private redo it flushes the redo to the log buffer and applies it to the data buffer before calling lgwr to write the log buffer to disc - once the log writer has written dbwr can write that data block to disc.

Private strand flush incomplete is the event you get from a foreground session when something else (such as DBWR) is doing the flush for it and it's waiting to generate more redo. It can't put it into the private buffer because that buffer is now locked against change because of the flush; it can't put it into the public buffer otherwise the redo might get there before the sessions earlier redo, so it has to wait temporarily for the private buffer to become unlocked because that's the only way it can tell that the flush is complete and it's now safe to carry on - and when it carries on it has to switch to the public buffer.

I've just finished rewriting half a dozen pages of my book explaining this, so a short answer that hits a few of the highlights is all you can expect in a forum note.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
http://www.jlcomp.demon.co.uk

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.

There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)

Marked as Answer by 845641 · Sep 27 2020

845641

thanks jonathan for this deep reply.. you are just a genius always and pavanG also for answering me straight in few lines rather than giving me links of those complete huge manuals... thanks again

user12050217

If you think server process copies the buffer to the log buffer; then my questions are:
1.Which buffer i.e. redo log bufer, database buffer ?
2.Then who writes in Redologs i.e. server process or LGWR ?
3.If server process writes, then what does LGWR ?
4.If server process is writing in redologs, then why and when DBWR signal to LGWR ?

Thank you.

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 13 2017

Added on Mar 15 2017

#general-database-discussions

11 comments

2,426 views

Oracle Database Discussions

ssl connection to 12c db failed with ORA-01017: invalid username/password; logon denied

Comments

Post Details