RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Scenario: When creating a physical standby database using rman duplication command

bash-4.2$ rman target sys/pwd@primary auxiliary sys/pwd @stby

After running the above command, then the following error shows:

RMAN-04006: error from auxiliary database: 

ORA-01017: invalid username/password; logon denied

Solution:

Case 1:

SQL> show parameter REMOTE_LOGIN_PASSWORD

NAME                                 TYPE                              VALUE

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

remote_login_passwordfile            string                            EXCLUSIVE

SQL>show parameter REMOTE_OS_AUTHENT

NAME                                 TYPE                              VALUE

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

remote_os_authent                    boolean                           FALSE

By default remote_login_passwordfile is EXCLUSIVE and remote_os_authent is FALSE.

If  other than EXCLUSIVE and FALSE then run the following command, which should resolve the problem.

SQL> alter system set REMOTE_LOGIN_PASSWORD= EXCLUSIVE

SQL> alter system set REMOTE_OS_AUTHENT = FALSE

Case 2:

I have such problem where I never explicitly set a password for the SYS user in the newly created database and I could still connect locally. I have fixed that by the following way:

1.      Execute the following command in primary/target database.

SQL>ALTER USER SYS IDENTIFIED BY pwd;

2.      The command update the password file automatically $ORACLE_HOME/dbs/orapwORACLE_SID

3.      Copy password file to standby server

Transparent Data Encryption (TDE) In Oracle 12c

-- Creating Keystore directory
$ mkdir -p /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home

-- Editing sqlnet.ora file as following:
$ vi /orafs/app/oracle/12.2.0.1/db_1/network/admin/sqlnet.ora

### Advance security wallet location
ENCRYPTION_WALLET_LOCATION =
 (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home)
    )
  )


-- Bouncing Database 
SQL > shutdown immediate;
SQL > startup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: NOT_AVAILABLE

-- Creating the keystore
SQL> administer key management create keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: CLOSED

-- Opening the keystore

SQL> administer key management set keystore open identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN_NO_MASTER_KEY

-- Creating a MASTER KEY
SQL> administer key management set key identified by "wlt123" with backup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN


-- Creating auto login setup :

SQL> administer key management create auto_login keystore from keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";





 ======================== Checking TDE =======================

---- Encrypting column of an existing table:

SQL> alter table test.account modify (ACCNAME encrypt);

Table altered.

-- You can turn off the encryption for a table:
SQL> alter table test.account modify (ACCNAME decrypt);

Table altered.


--- Encrypting a tablespace online using TDE encryption

SQL> alter tablespace test_tsp ENCRYPTION online ENCRYPT FILE_NAME_CONVERT = ('test_dbs.dbf', 'test_dbs_encrypt.dbf');

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name = 'test_tsp';

FILE_NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test_dbs_encrypt.dbf


--- Decrypting the same tablespace online using TDE encryption
SQL> alter tablespace saidur_tsp ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('test_dbs_encrypt.dbf', 'test_dbs.dbf');

Tablespace altered.


 ============================== End ===================== 

No standby redo logfiles available for T-1 (Log Shipping when log switch occurs)

In Oracle 12c single instance Dataguard environment ,

 Standby  alert log shows below information; 

Primary database is in MAXIMUM PERFORMANCE mode

RFS[1]: Assigned to RFS process (PID:18416050)

RFS[1]: No standby redo logfiles available for T-1 

RFS[1]: Opened log for T-1.S-1164 dbid 1226709381 branch 1005380631

Archived Log entry 84 added for thread 1 sequence 1164 rlc 1005380631 ID 0x55f53b9e LAD3 :

Solutions1:

Checked both primary and standby database, the standby redo logs have been created. It is found that the thread id is 1 in both primary and standby db.

Drop all standby online redo logs on standby databases, and recreate then again by excluding “thread 1” .

Finally restart the recovery process, everything is fine.

We have found this kind of error for single instance database in 12c, 

which is a new feature or may be bug issue?


Solutions2:

If you have configured a standby redo log on one or more standby databases in the configuration, ensure the size of the standby redo log files on each standby database exactly matches the size of the online redo log files on the primary database.

At log switch time, if there are no available standby redo log files that match the size of the new current online redo log file on the primary database:

• The primary database will shut down if it is operating in maximum protection mode,
  or
• The RFS process on the standby database will create an archived redo log file on the standby database and write the following message in the alert log:

  No standby log files of size <#> blocks available.
  

For example, if the primary database uses two online redo log groups whose log files are 100K, then the standby database should have 3 standby redo log groups with log file sizes of 100K.

Also, whenever you add a redo log group to the primary database, you must add a corresponding standby redo log group to the standby database. This reduces the probability that the primary database will be adversely affected because a standby redo log file of the required size is not available at log switch time.

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14239/troubleshooting.htm#i639505

Step by Step 12c Opatch Update Prcess

1. Backup the OPatch directory and Copy the new OPatch file into the Oracle Home directory then run then command:

bash-4.2$ cp p6880880_122010_AIX64-5L.zip /orafs/app/oracle/12.2.0.1/db_1/p6880880_122010_AIX64-5L.zip

bash-4.2$  unzip p6880880_122010_AIX64-5L.zip

2. copy and unzip the PSU file to the following location and then unzip the file

bash-4.2$ bash-4.2$ cp p28662603_122010_AIX64-5L.zip 

/orafs/app/oracle/12.2.0.1/db_1/OPatch/p28662603_122010_AIX64-5L.zip

bash-4.2$  unzip p28662603_122010_AIX64-5L.zip

bash-4.2$  cd 28662603

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

$ORACLE_HOME/OPatch/opatch lsinventory

bash-4.2$  $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

3.  apply opatch :

bash-4.2$  $ORACLE_HOME/OPatch/opatch apply

4. Run ./datapatch after patching :

sqlplus /nolog

Connect / as sysdba

startup

quit

bash-4.2$  cd $ORACLE_HOME/OPatch

bash-4.2$  ./datapatch -verbose

DB Upgrade from 11.2.0.3 to 12.2.0.1

DB upgrade  from 11g to 12c using DBUA

Step 1: 

At first install the 12c binary and then follow the following activities.

Copy spfilea.ora and password file to /orafs/app/oracle/12.2.0.1/db_1/dbs/ 

Step 2:

$ORACLE_HOME/jdk/bin/java -jar /orafs/app/oracle/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade_log

bash-4.2$ sqlplus / as sysdba

SQL> @preupgrade_fixups.sql

Step 3:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 4. EM Remove:

cd /orafs/app/oracle/12.2.0.1/db_1/rdbms/admin/

SQL>SET ECHO ON

SQL>SET SERVEROUTPUT ON

SQL>@emremove.sql

Step 5:

SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE

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

sec_case_sensitive_logon             boolean     FALSE

SQL> alter system set sec_case_sensitive_logon=true;

System altered.

Step 6:

bash-4.2$ vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Step 7:

Add the following line on the /etc/oratab

bash-4.2$ vi /etc/oratab

dbname:/orafs/app/oracle/11.2.0.3/db_1:N

Step 8: run DBUA

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

Restore RMAN Backup TO a Different Server

This blog will detail the steps to restore a database on a new host using RMAN. 

Step_1. Create Pfile

Step_2. Startup nomount:

SQL> startup nomount pfile='/rmanfs/pfile.ora';
----------------------------------------------------
Transfer the full backup with control file to the desired server.

Step_3. Restore Controlfile:

RMAN> restore controlfile from '/rmanfs/backup/o1_mf_s_99261014_.bkp';
RMAN> catalog start with '/rmanfs/backup';


Step_4: Mount Database

SQL> alter database mount;

Step_5: Restrore DB


ORACLE_UNQNAME=dbname; export ORACLE_UNQNAME
ORACLE_BASE=/orafs/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=dbname; export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

$ORACLE_HOME/bin/rman  target /

run
{
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;

set newname for datafile 1 to '/dbsystemfs/oradata/system01.dbf';
set newname for datafile 2 to '/dbsystemfs/oradata/sysaux01.dbf';
set newname for datafile 3 to '/undofs/undo_tbs01.dbf';
set newname for datafile 4 to '/dbsystemfs/oradata/ababil/users/users01.dbf';
set newname for tempfile 1 to '/tempfs/oradata/temp/temp01.dbf';

restore database;
switch datafile all;
switch tempfile all;
release channel disk1;
release channel disk2;
}


Step_6: Recover Database;

RMAN> recover database;(RMAN> recover database until scn 21046745532;)
RMAN> alter database open resetlogs;

How to Disable the triger on standby database and how to resolve the error- ORA-00604: error occurred at recursive SQL level 1

We have added an AFTER LOGON ON DATABASE,AFTER DDL ON DATABASE and AFTER SERVERERROR ON DATABASE trigger.
Standby databases are not writable and so this is not going to work.

As a result every users connecting to the standby database were complaining:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
ORA-02063: preceding 3 lines
00604. 00000 -"error occurred at recursive SQL level %s"
*Cause:An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Another issue raises the replication does not work i.e. Data sync is no working.

Realising my mistake I dropped/disable the trigger but this didn’t make the problem go away on the standby database.

A quick look in the alert log on the primary and there was a logon failure related to Data Guard:

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
PING[ARCl]: Heartbeat failed to connect to standby 'adgrpt'. Error is 16191.

I checked the SYS passwords on both nodes in case an underlying problem had suddenly occurred but all was ok.

Looking at the output we have a big clue here,
as it looks like the error the users were getting:

ORA-16000: database open for read-only access

Why are we still getting that when I’ve dropped the trigger on the primary database? It is immediately apparent that
 I have broken Data Guard and my drop is in a log file awaiting shipping to the standby. This is a catch 22:
I can’t apply the log because the trigger won’t let Data Guard authenticate and Data Guard can’t authenticate because of the presence of the trigger.

Two questions sprang to my mind:

    Can I disable the trigger?
    Why is the trigger affecting the SYS user?

I can’t workaround the trigger in any documented way because any attempt to do so on the physical standby is prevented by its read-only status, for example:

SQL> grant ADMINISTER DATABASE TRIGGER to appuser;
grant ADMINISTER DATABASE TRIGGER to appuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Here user SYS is connecting with a password not “as sysdba” and so we are hitting the logon trigger.

we can the following system parameter .

alter system set "_system_trig_enabled"=FALSE;

I can then restart the recovery with:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

And of course not forgetting to re-enable system triggers:

alter system set "_system_trig_enabled"=TRUE;

It should be pointed out that this method is nice because it doesn’t involve restarting the standby and therefore interrupting any running reports
but if you don’t like undocumented parameters or you have other system triggers you depend on you can instead:

    Shut down the standby.
    Restart the standby in MOUNT mode. This means that any triggers will not fire.
    Start the recovery.
    If the trigger has been dropped on the primary it should replicate to the secondary.
    Open the database.


Source URL: https://oraganism.wordpress.com/2015/08/26/non-working-logon-trigger-on-data-guard-standby-database/