Re: unable to do a remote sys connection to 12.2 container database

Maybe I live already in the future, therefore 10-08-20 appears to me in the past :-)

Ahmed Fikri <gherrami_at_gmail.com> schrieb am Fr., 14. Feb. 2020, 20:29:

> It seems that the sys user has a expiry_date = 10-AUG-20. I'm not sure if
> that has anything to do with the problem, but it looks strange.
> Could you see in dba_users?
>
> Am Do., 13. Feb. 2020 um 13:51 Uhr schrieb Jeffrey Beckstrom <
> jbeckstrom_at_gcrta.org>:
>
>> user system is connecting to the correct database.
>>
>> We did try changing the sys password to just letters/numbers and connect
>> still fails. Actually the sys account gets locked due to the failures.
>> >>> Keith Moore <keithmooredba_at_gmail.com> 2/12/20 4:14 PM >>>
>> When you connect as system using the same tnsnames entry, have you
>> verified you are connecting to the database you think you are connecting to?
>>
>> Is the RAC by any chance and maybe you are connecting to a different
>> instance?
>>
>> You could try changing the sys password when connected locally and verify
>> the password file is being updated.
>>
>> Keith
>>
>> On Feb 12, 2020, at 2:47 PM, Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>> wrote:
>>
>> SYSTEM_at_newcdb>Select * from v$pwfile_users;
>>
>> USERNAME
>>
>> --------------------------------------------------------------------------------
>> SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS
>> ----- ----- ----- ----- ----- ----- ------------------------------
>> PASSWORD_PROFILE
>>
>> --------------------------------------------------------------------------------
>> LAST_LOGIN
>>
>> ---------------------------------------------------------------------------
>> LOCK_DATE EXPIRY_DA
>> --------- ---------
>> EXTERNAL_NAME
>>
>> --------------------------------------------------------------------------------
>> AUTHENTI COM CON_ID
>> -------- --- ----------
>> SYS
>> TRUE TRUE FALSE FALSE FALSE FALSE OPEN
>> DEFAULT
>>
>> 10-AUG-20
>>
>> PASSWORD YES 0
>>
>> C##RTAADMIN
>> TRUE FALSE FALSE FALSE FALSE FALSE OPEN
>> DEFAULT
>>
>> 09-AUG-20
>>
>> PASSWORD YES 0
>>
>> BACKUP_DB
>> FALSE FALSE FALSE TRUE FALSE FALSE OPEN
>> DEFAULT
>> 09-JAN-20 05.22.23.000000000 PM -05:00
>> 07-JUL-20
>>
>> PASSWORD NO 3
>>
>>
>> SYSTEM_at_newcdb>
>> >>> Ahmed Fikri <gherrami_at_gmail.com> 2/12/20 3:38 PM >>>
>> I encountered the same problem in the past.
>> Can you check whether you the sys user in this view:
>> Select * from v$pwfile_users;
>> It might be that someone revoked the sysdba from the sys user
>> Regards
>> Ahmed
>>
>> Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> schrieb am Mi., 12. Feb. 2020,
>> 20:54:
>>
>>> oracle_sid is newcdb
>>>
>>> password is just letters and numbers
>>> >>> Sayan Malakshinov <xt.and.r_at_gmail.com> 2/12/20 2:29 PM >>>
>>> Is ORACLE_SID=newcdb? Have you tried simple passwords without special
>>> characters?
>>>
>>> ср, 12 февр. 2020 г., 22:23 Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>:
>>>
>>>> did a
>>>> connect / as sysdba
>>>> alter user sys identified by xxxxxx
>>>> connect sys/xxxxx_at_newcdb as sysdba
>>>> ora-1017
>>>> >>> Seth Miller <sethmiller.sm_at_gmail.com> 2/12/20 1:13 PM >>>
>>>> It really sounds like you are using the wrong password. The only way to
>>>> be sure is to use OS auth to connect and change the sys password as Sayan
>>>> suggested.
>>>>
>>>> On Wed, Feb 12, 2020 at 9:21 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>>>> wrote:
>>>>
>>>>> [dbsorat08:/oracle/db/12_2_0_1/dbs:newcdb] $ orapwd describe
>>>>> file=orapwnewcdb
>>>>> Password file Description : format=12.2
>>>>>
>>>>> >>> "Sweetser, Joe" <JSweetser_at_icat.com> 2/12/20 10:10 AM >>>
>>>>> (really far) Left field idea. Can you check the format of your
>>>>> password file?
>>>>> I had issues with 19c (and the format of the password file) where I
>>>>> had to recreate my password file with the 12.2 format (it was 12 after
>>>>> creating a database). Probably not directly related to your issue but…
>>>>> $ orapwd describe file=orapwqa19c1
>>>>> Password file Description : format=12
>>>>> $ mv orapwqa19c1 orapwqa19c1.format12
>>>>> $ orapwd file=orapwqa19c1 entries=10 format=12.2
>>>>> Enter password for SYS:
>>>>> $ orapwd describe file=orapwqa19c1
>>>>> Password file Description : format=12.2
>>>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
>>>>> Behalf Of *Sayan Malakshinov
>>>>> *Sent:* Wednesday, February 12, 2020 7:45 AM
>>>>> *To:* Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>
>>>>> *Cc:* oracle-l-freelist <oracle-l_at_freelists.org>; angelo <
>>>>> angelolistas_at_gmail.com>; Chris Taylor <
>>>>> christopherdtaylor1994_at_gmail.com>
>>>>> *Subject:* Re: unable to do a remote sys connection to 12.2 container
>>>>> database
>>>>> Could you try also connect using OS authentication and change sys
>>>>> password using alter user...
>>>>> ср, 12 февр. 2020 г., 17:41 Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>:
>>>>>
>>>>> xxxxxxxx:/oracle/db/12_2_0_1/dbs:] $ ls -al orapwnewcdb
>>>>> -rw-r-----. 1 oradmin oinstall 11264 Feb 12 09:37 orapwnewcdb
>>>>> xxxxxxx:/oracle/db/12_2_0_1/dbs:] $
>>>>> >>> Sayan Malakshinov <xt.and.r_at_gmail.com> 2/12/20 9:35 AM >>>
>>>>> And show please file permissions for your pwfile. ls -la
>>>>> ср, 12 февр. 2020 г., 17:33 Sayan Malakshinov <xt.and.r_at_gmail.com>:
>>>>>
>>>>> Hi,
>>>>> Have you tried to set authentication_services=(none) in sqlnet.ora?
>>>>> ср, 12 февр. 2020 г., 17:28 Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>:
>>>>>
>>>>> I am trying to connect to the cdb not the pdb.
>>>>> connect sys/xxxx_at_newcdb as sysdba - fails
>>>>> connect system/xxxxx_at_newcdb - works
>>>>> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 2/12/20 9:21 AM
>>>>> >>>
>>>>> Jeff,
>>>>> What is the service_name registered with the listener for the PDB, and
>>>>> does it match the service_name you're providing on the command line:
>>>>> sqlplus sys/xxxx_at_newcdb as sysdba (is "newcdb" the service_name
>>>>> registered with the listener for the PDB?)
>>>>> Chris
>>>>> On Wed, Feb 12, 2020 at 8:42 AM Jeffrey Beckstrom <
>>>>> jbeckstrom_at_gcrta.org> wrote:
>>>>>
>>>>> I am trying to do a TNS connection as sys to the CDB and that's when I
>>>>> get the invalid username/password error. If I try a TNS connection as
>>>>> system to the CDB it works so TNS is correct.
>>>>> >>> angelo <angelolistas_at_gmail.com> 2/12/20 8:33 AM >>>
>>>>> Hello Jeff.
>>>>> If you try to connect onto your cdb using sys as sysdba, before.
>>>>> and next, change your session from cdb to container. e.g.
>>>>> SQL> alter session set container=newpdb;
>>>>> SQL> show con_id;
>>>>> Does it work ?
>>>>> what about services_name from pdb? In TNS, must be different from cdb.
>>>>> below, my db tnsnames for example.
>>>>> CDB =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST =
>>>>> xxxdb01.lanocpsp.allspoc.oraclevcn.com
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fxxxdb01.lanocpsp.allspoc.oraclevcn.com%2F&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924404567&sdata=LN5oKhznGC9DbkX4SidnrvRMZWzsaP8OPFaAQuG11RQ%3D&reserved=0>)(PORT
>>>>> = 1521))
>>>>> (CONNECT_DATA =
>>>>> (SERVER = DEDICATED)
>>>>> (SERVICE_NAME = CDB01_gru196.lanocpsp.allspoc.oraclevcn.com
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fcdb01_gru196.lanocpsp.allspoc.oraclevcn.com%2F&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924414563&sdata=hmdf6Dr1Y2pRgPiTZcX%2F0Bdfx7g4KbH73yZexXkQnx8%3D&reserved=0>
>>>>> )
>>>>> )
>>>>> )
>>>>>
>>>>> PDB =
>>>>> (DESCRIPTION =
>>>>> (ADDRESS = (PROTOCOL = TCP)(HOST =
>>>>> xxxdb01.lanocpsp.allspoc.oraclevcn.com
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fxxxdb01.lanocpsp.allspoc.oraclevcn.com%2F&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924414563&sdata=4Bf5F%2BmTVuJOhhEqa7nn5VqaC3qSWMKVic%2BMDn8%2F06w%3D&reserved=0>)(PORT
>>>>> = 1521))
>>>>> (CONNECT_DATA =
>>>>> (SERVER = DEDICATED)
>>>>> (SERVICE_NAME = pdb01.lanocpsp.allspoc.oraclevcn.com
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Fpdb01.lanocpsp.allspoc.oraclevcn.com%2F&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924424556&sdata=Mona%2FUrNiad2fmklc3G8L%2BJY8C5zKKvWz9cVvS7ECtM%3D&reserved=0>
>>>>> )
>>>>> )
>>>>> )
>>>>> On Wed, 12 Feb 2020 at 09:51, Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
>>>>> wrote:
>>>>>
>>>>> We have had non-container databases until now. We just built a
>>>>> container and added a PDB to it. We can connect to the container as user
>>>>> "system" as well as a user to the PDB via TNS. However, if we try connect
>>>>> sys/xxxxxx_at_newcdb as sysdba, we get an invalid username/password
>>>>> error. If we try the same connection on the server after setting oracle_sid
>>>>> and not using tns, then it works. However, if we add the TNS string, it
>>>>> then fails. We have created a password file and remote_login_passwordfile
>>>>> is set to exclusive. We have followed our same steps as we would for
>>>>> setting this up for an non-CDB database. We are at a loss as to what is
>>>>> wrong. Any help would be appreciated.
>>>>> Jeffrey Beckstrom
>>>>> Lead Database Administrator
>>>>> Information Technology Department
>>>>> Greater Cleveland Regional Transit Authority
>>>>> 1240 W. 6th Street
>>>>> Cleveland, Ohio 44113
>>>>>
>>>>> ------------------------------
>>>>> This email has been scanned for spam and viruses. Click here
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fattseg.cloud-protect.net%2Findex01.php%3Fmod_id%3D11%26mod_option%3Dlogitem%26mail_id%3D1581514403-I0hHpL_vQOl2%26r_address%3Djbeckstrom%2540gcrta.org%26report%3D1&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924424556&sdata=hSqL8wL6mgwfxArt85lYihzgvrA5zgNbIi9sJR1EWFs%3D&reserved=0>
>>>>> to report this email as spam.
>>>>>
>>>>> ------------------------------
>>>>> This email has been scanned for spam and viruses. Click here
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fattseg.cloud-protect.net%2Findex01.php%3Fmod_id%3D11%26mod_option%3Dlogitem%26mail_id%3D1581517284-i5rj7HAk6gz8%26r_address%3Djbeckstrom%2540gcrta.org%26report%3D1&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924424556&sdata=G3K4QF6nCWRDUXBeSAgX4BaUfNTOMUf4arGofqk2sHo%3D&reserved=0>
>>>>> to report this email as spam.
>>>>>
>>>>> ------------------------------
>>>>> This email has been scanned for spam and viruses. Click here
>>>>> <https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fattseg.cloud-protect.net%2Findex01.php%3Fmod_id%3D11%26mod_option%3Dlogitem%26mail_id%3D1581518140-025vOXKdWIif%26r_address%3Djbeckstrom%2540gcrta.org%26report%3D1&data=02%7C01%7CJSweetser%40icat.com%7C8f70d35e63a94b9b122e08d7afca58eb%7C5d3bf30e9adb4c17b2425c17523e6e5e%7C0%7C0%7C637171155924434551&sdata=W4Y0www842NDsvghxb9ClDl9pgP8%2FXK7y10QjkX7VEA%3D&reserved=0>
>>>>> to report this email as spam.
>>>>>
>>>>> This e-mail transmission and any attachments that accompany it may
>>>>> contain information that is privileged, confidential or otherwise exempt
>>>>> from disclosure under applicable law and is intended solely for the use of
>>>>> the individual's to whom it was intended to be addressed. If you have
>>>>> received this e-mail by mistake, or you are not the intended recipient, any
>>>>> disclosure, dissemination, distribution, copying or other use or retention
>>>>> of this communication or its substance is prohibited. If you have received
>>>>> this communication in error, please immediately reply to the author via
>>>>> e-mail that you received this message by mistake and also permanently
>>>>> delete the original and all copies of this e-mail and any attachments from
>>>>> your computer. Please note that coverage cannot be bound or altered by
>>>>> sending an email. You must receive written confirmation from a
>>>>> representative of our firm to put coverage in force or make changes to an
>>>>> existing policy.
>>>>> ------------------------------
>>>>> This email has been scanned for spam and viruses. Click here
>>>>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1581520211-nSJY438esRrv&r_address=jbeckstrom%40gcrta.org&report=1>
>>>>> to report this email as spam.
>>>>>
>>>>
>>>> ------------------------------
>>>> This email has been scanned for spam and viruses. Click here
>>>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1581531198-ASwHQa7HLrDc&r_address=jbeckstrom%40gcrta.org&report=1>
>>>> to report this email as spam.
>>>>
>>>>
>>> ------------------------------
>>> This email has been scanned for spam and viruses. Click here
>>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1581535801-LI3hzMiGIga3&r_address=jbeckstrom%40gcrta.org&report=1>
>>> to report this email as spam.
>>>
>>>
>>
>> ------------------------------
>> This email has been scanned for spam and viruses. Click here
>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1581539895-NToYPkblMWci&r_address=jbeckstrom%40gcrta.org&report=1>
>> to report this email as spam.
>>
>>
>>
>>
>> ------------------------------
>> This email has been scanned for spam and viruses. Click here
>> <https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1581542082-au9YngjugXHc&r_address=jbeckstrom%40gcrta.org&report=1>
>> to report this email as spam.
>>
>>

--
http://www.freelists.org/webpage/oracle-l