Oracle: December 2016

Saturday, December 17, 2016

RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol

My oracle database: 9i on HP-UX

My Catalog database: 12c on RHEL 6

Today while taking RMAN backup from backup tool we are getting below error

[Major] From: ob2rman@myoradec01.domain.com"prod" Time: 10/15/16 17:03:34

The database reported error while performing requested operation.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol

I wanted to try connecting from the server to the catalog database

Myoradev01:prod:/opt/oracle/product/9.2.0.8.64/network/admin $ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

connected to target database: PROD (DBID=176732808)

RMAN> connect catalog prod_user/xxxx@rcvc

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-04004: error from recovery catalog database: ORA-28040: No matching authentication protocol

RMAN>

According to oracle

“Connections to a 12c database server can only be made from a 10.2 and later client.”

But if you make changes to sqlnet.ora on the catalog it will work, to do so go to catalog database and modify $ORACLE_HOME/network/admin/sqlnet.ora file as below

audbs1:rm:/opt/oracle/product/12.1.0.2.64/network/admin $ vi sqlnet.ora

AUTOMATIC_IPC=ON

SQLNET.INBOUND_CONNECT_TIMEOUT=0

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8 à ADDED this Line

Try to connect to catalog database again from 9i database

Myoradev01:prod:/opt/oracle/product/9.2.0.8.64/network/admin $ rman target /

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

connected to target database: PROD (DBID=176732808)

RMAN> connect catalog prod_user/xxxx@rcvc

connected to recovery catalog database

Now I can able to connect and my Issue is resolved.

Refer:

RMAN Catalog (12c) Connection Failed For 9.2.0.6.0 Database (Doc ID 1922190.1)

Saturday, December 3, 2016

ORA-01031: insufficient privileges

Server: HP-UX

Oracle DB: 10g

While connecting as sysdba user to one of my 10g database it gives me an error

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:27:01 2016

Enter password:

ERROR:

ORA-01031: insufficient privileges

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 11:58:09 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

SQL> show parameter password

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> select * from v$pwfile_users;

no rows selected à Seems to be an issue now

SQL> grant sysdba to sys;

grant sysdba to sys

ERROR at line 1:

ORA-01999: password file mode has changed from 'exclusive' to 'shared'

SQL> sho parameter pfile

NAME TYPE VALUE

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

spfile string /opt/oracle/product/10.1.0.5.64/dbs/spfileMYCUSTDB.ora

myoraserver:MYCUSTDB:/opt/oracle/admin/MYCUSTDB/bdump $ cd $ORACLE_HOME/dbs

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ ls -lr orapw*

i can see my passwordfile, lets backup existing password file and recreate new with same password

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ mv orapwMYCUSTDB orapwMYCUSTDB.Nov03

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ orapwd file=orapwMYCUSTDB password=xxxxxx

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:02:07 2016

Enter password:

ERROR:

ORA-01031: insufficient privileges

Enter user-name:

Still same error when connecting as sys user…………

Myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus / as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 3 12:24:09 2016

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> ALTER SYSTEM SET remote_login_passwordfile=shared scope=SPFILE;

System altered.

********* Need to Bounce the database *********

SQL> select name from v$database;

NAME

---------

MYCUSTDB

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2046820352 bytes

Fixed Size 1299208 bytes

Variable Size 1179397368 bytes

Database Buffers 855638016 bytes

Redo Buffers 10485760 bytes

Database mounted.

Database opened.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

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

SYS TRUE TRUE

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:24:58 2016

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> show parameter password

NAME TYPE VALUE

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

remote_login_passwordfile string SHARED

Now i can able to connect without any issues.............Now revert back to EXCLUSIVE

SQL> ALTER SYSTEM SET remote_login_passwordfile=EXCLUSIVE scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2046820352 bytes

Fixed Size 1299208 bytes

Variable Size 1179397368 bytes

Database Buffers 855638016 bytes

Redo Buffers 10485760 bytes

Database mounted.

Database opened.

SQL> show parameter password

NAME TYPE VALUE

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

remote_login_passwordfile string EXCLUSIVE

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

myoraserver:MYCUSTDB:/opt/oracle/product/10.1.0.5.64/dbs $ sqlplus sys@MYCUSTDB as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Nov 4 12:28:42 2016

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

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

SYS TRUE TRUE

Reference:

ORA-1999: When Granting SYSDBA to User (Doc ID 103576.1)

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured

Primary database: PRIMDB

Standby db unique name: PRIMDB_SB4

OS: RHEL6

Oracle database: 12.1.0.2

Catalog database:

Primary:

oradev8:primdb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Nov 2 11:08:31 2016

connected to target database: PRIMDB (DBID=1183475863)

RMAN> connect catalog primdb_rman/primdbpr2010@rcv

connected to recovery catalog database

RMAN> configure db_unique_name 'PRIMDB_SB4' connect identifier 'PRIMDB_SB4';

new RMAN configuration parameters:

CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER 'PRIMDB_SB4';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIMDB are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER 'PRIMDB_SB4';

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default

Standby:

orastandby8:primdb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Nov 2 11:07:26 2016

connected to target database: PRIMDB (DBID=1183475863)

RMAN> connect catalog primdb_rman/primdbpr2010@rcv

connected to recovery catalog database

RMAN> resync catalog;

starting partial resync of recovery catalog

partial resync complete

RMAN> show all for db_unique_name all;

RMAN configuration parameters for database with db_unique_name PRIMDB are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER 'PRIMDB_SB4';

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default

RMAN configuration parameters for database with db_unique_name PRIMDB_SB4 are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2555 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE DB_UNIQUE_NAME 'PRIMDB_SB4' CONNECT IDENTIFIER 'PRIMDB_SB4';

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/12.1.0.2.64/dbs/snapcf_primdb.f'; # default

UNREGISTER db unique name:

If you want to remove DB_UNIQUE_NAME from show all, Connect to target and catalog and use below command

RMAN> unregister DB_UNIQUE_NAME 'db_unique_name';

database db_unique_name is "xxxx", db_name is "xxxx" and DBID is 771234705

Want to unregister the database with target db_unique_name (enter YES or NO)? yes

database with db_unique_name xxxxxx unregistered from the recovery catalog

Reference:

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1)