ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Error occurred due to Switchover having active session

I am trying to switchover the primary database to standby database then i am getting the following error:

SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

Solution:
For disconnect the active user you can use the clause WITH SESSION SHUTDOWN.

-- From primary if getting error:
PRIM> alter database commit to switchover to physical standby with session shutdown;

-- From Standby if getting error:
Standby> alter database commit to switchover to primary with session shutdown;

Note:
You can verify before switch over command with switchover_status column in v$database.
It tell you that any session is active at time of switchover or not.

Following value means session is active. use this command at both primary or Standby before switch over.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Value of Switchover at primary and Standby as follow then it good to go for switchover the Dataguard environment

STAMDBY> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

PRIM> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Advertisements

Steps verify for Switchover Dataguard to physical standby

Steps verify for Switchover Dataguard to physical standby

Before Switchover the Dataguard in production Server. You need to check the value of switch over column in v$database.
If it having below value then you can go to switch over the primary to DR server. Following values means both Primary and DR are ready for switchover.

1. Verify the Switch over column value:

PRIM>select switchover_status from v$database ;

Switchover_Status
-----------------
TO Standby

STANDBY>select switchover_status from v$database ;

Switchover_Status
-----------------
TO PRIMARY

Note: If both parameter are in this stage then we have to do the switchover

Switchover status column values meaning as follows:
Not Allowed : from primary or Dr database either its not allowed to switch-over.
Session Active : means active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation.
Switchover Pending : means switch-over request has been received but not processed.
Switchover Latent : means switch-over was in pending mode did not complete and went back to the primary database.
To Primary : From Standby Database means: no active sessions present which allowed to switch over to a primary database.
To Standby : From Primary Database means:no active sessions present which allowed to switch over to a standby database.
Recovery Needed : From Standby DB means, it has not received the switch-over request.

2. Verify the gap status

select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS GAP_STATUS
--------- ------------------------
VALID NO GAP

Note: If standby and primary have gap then resolved it first.

3. Check file location at both side in case of physical standby

select file_name from dba_data_files;
select file_name from dba_temp_files;

4. Check parameter value of archive destination.

show parameter log

5. If we found the value of switchover is SESSION ACTIVE then

select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

-- Then use with session shutdown clause with switch over command.
PRIM>alter database commit to switchover to physical standby with session shutdown;
Standby>alter database commit to switchover to primary with session shutdown;

Step to resize standby redolog files in Dataguard

Step to resize standby redolog files

In Data-guard, if we recreate or add the standby redolog files due to thread number issue or Size mentioned wrong then in that case we need to drop or create the standby redo log files in DB standby database.

Error due to thread and size:

No standby redo logfiles selected

RFS[602]: No standby redo logfiles of size 1024000 blocks exist
RFS[602]: No standby redo logfiles selected (reason:7)

RFS[4]: No standby redo logfiles created for T-1
RFS[4]: Opened log for T-1.S-57586 dbid 618415567 branch 953210132

Following are the steps to drop and create new standby database:

1. Check Primary Redo Thread Number and size.

SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;

THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
------- ------ --------- ---------- --- ----------
1 1 57586 209715200 NO CURRENT
1 2 57584 209715200 YES INACTIVE
1 3 57585 209715200 YES INACTIVE

Note: 209715200/1014/1024 = 200 MB

2. Check Standby Thread number and Size for Standby redo logs.

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
------- ------ --------- ---------- --- ----------
0 4 0 52428800 YES UNASSIGNED
0 5 0 52428800 YES UNASSIGNED
0 6 0 52428800 YES UNASSIGNED
0 7 0 52428800 YES UNASSIGNED

Note: 52428800/2014/1024= 50MB
If you find anything different from both size or thread number then recreate the standby redo to avoids error in alert log.
As you see standby is unassigned status means it is not used by standby database.

3. Stop the dataguard recovery process in Standby database:

-- for standby db which is under recovery, recovery needs to be stopped first
alter database recover managed standby database cancel;

4. Drop the existing Standby redo files:
Note: There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
In this example we configure 6 standby redo files.

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

5. Create new Standby logfile with THREAD Clauses.

alter database add standby logfile thread 1 group 4 size 200m;

alter database add standby logfile THREAD 1 group 4 ('D:\ORACLEXE\STANDBYREDO04.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 6 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 7 ('D:\ORACLEXE\STANDBYREDO06.log') SIZE 200M;

--Example for two members
--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.log') SIZE 200M;

--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;

Note: in my case standby redo size(50M) is different with primary redo size(200M). so I corrected that one also while created new standby.

6. Now verify the thread number and redo log

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

7. Start the Recovery of the dataguard in oracle.

alter database recover managed standby database disconnect from session;

RFS[4]: No standby redo logfiles created for T-1 dataguard

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

In Standby Environment of dataguard Server, We are getting following alert log error:

RFS[4]: No standby redo logfiles created for T-1
RFS[4]: Opened log for T-1.S-57586 dbid 618415567 branch 953210132

It show that No standby redo log file of thread 1 is present as per message, it also mention Thread number as 1.
On checking the Primary and Secondary database with thread column in views:

1. Check Primary Redo Thread Number:

SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;

THREAD# GROUP# SEQUENCE# BYTES     ARC STATUS
------- ------ --------- --------- --- ----------
1  	1      57586  	 209715200 NO  CURRENT  
1  	2      57584  	 209715200 YES INACTIVE  
1  	3      57585  	 209715200 YES INACTIVE  

2. Check Standby Thread number for Standby redo logs configured for DR Server.

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

THREAD# GROUP# SEQUENCE#  BYTES     ARC  STATUS 
------- ------ --------- ---------- ---   ----------
0       4      0         52428800  YES  UNASSIGNED  
0       5      0         52428800  YES  UNASSIGNED  
0  	6      0         52428800  YES  UNASSIGNED  
0  	7      0         52428800  YES  UNASSIGNED  
0  	8      0         52428800  YES  UNASSIGNED  
0  	9      0         52428800  YES  UNASSIGNED  

Note:Both Thread Number is different in value, so we need to drop and recreate the Standby with specify the THREAD caluse.

3. Stop the dataguard recovery process in Standby database:

-- for standby db which is under recovery, recovery needs to be stopped first
alter database recover managed standby database cancel;

4. Drop the existing Standby redo files:
Note: There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
In this example we configure 6 standby redo files.

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 8;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 9;

5. Create new Standby logfile with THREAD Clauses.

alter database add standby logfile thread 1 group 4 size 200m;

alter database add standby logfile THREAD 1 group 4 ('D:\ORACLEXE\STANDBYREDO04.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 6 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 7 ('D:\ORACLEXE\STANDBYREDO06.log') SIZE 200M;

--Example for two members
--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.log') SIZE 200M;

--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;

Note: in my case standby redo size(50M) is different with primary redo size(200M). so I corrected that one also while created new standby.

6. Now verify the thread number and redo log

SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;

7. Start the Recovery of the dataguard in oracle.

alter database recover managed standby database disconnect from session;

Use of log_archive_config parameter in Oracle Dataguard

Use of log_archive_config parameter in Oracle Dataguard

Log archive config is used to send redo logs to remote location and receipt of remote redo logs. Db unique name is defined in both init file. DB_name is same.

Values of Parameter:

SEND: Enable the sending of redo log to Remote location.
NOSEND: Disable the sending of redo log to remote location.
RECEIVE: Enable the receipt of remotely archived redo logs.
NORECEIVE: Disable the receipt of remotely archived redo logs.
DB_CONFIG: list of 9 services provider name in data-guard configuration.
NODB_CONFIG:Eliminates the list of service provider names

Note: DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter.

Example
The standby database will have the value as :

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,PRIM_STBY)';

Error
ORA-16191: Primary log shipping client not logged on standby

Solution
Error may fixed with by setting following parameters as follows:

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,PRIM_STBY)’ — new entry, using the DB_UNIQUE_NAME of both databases

ORA-19527: physical standby redo log must be renamed

ORA-19527: physical standby redo log must be renamed

Error:

ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 7 thread 0: 'G:\ORACLE\ORADATA\STANDBY05.LOG'

At DR site, we got the error which describe us to rename the standby redo log. But on checking the both DR primary and Standby has same physical location.
It did not harm the data guard replication service but we need to get rid of this error message. It seems to be false alarm in DR alert log.

Solution:
To get rid of this error message in alert log. You need to set the dummy value of parameter log_file_name_convert.
If physical structure has same directories in both Primary & Standby. you can set the dummy value of this parameter as follows:

DR> ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

After the parameter was set, the ORA message was no longer seen in the alert.log

Authenticate with REMOTE LOGIN PASSWORDFILE Parameter in Oracle

Authenticate method with REMOTE LOGIN PASSWORDFILE Parameter in Oracle

Remote login passwordfile is specify that need to verify from the password file or OS authentication for login into the Oracle Server.
Default value of parameter is exclusive. For modify the value need to reboot the Oracle Database Server.

Values:
EXCLUSIVE: Password file can be used by only one database and having sys or non sys users.
SHARED: In this one or more database can used the same Password file and having sys or non sys users.
NONE: Oracle use OS authentication. it not consider password file.

Example of using Password file:

1. Check the parameter detail of remote login passwordfile.

SQL> show parameter remote_login
NAME TYPE VALUE
------------------------- ------ ----------
remote_login_passwordfile string EXCLUSIVE

2. Try to login with SYS users

C:\Users\e3019447>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 8 10:29:42 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

3. Change the value of parameter to NONE.

SQL> alter system set remote_login_passwordfile=NONE scope=both;
alter system set remote_login_passwordfile=NONE scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;
System altered.

4. Restart the database for parameter changes effects:

shutdown
startup

Note: Check also the parameter NTS or NONE in SQLNET.ora if you are on windows server.
NTS means window authentication
NONE means password file authentication
Otherwise got ERROR:
ORA-01031: insufficient privileges
Solution: On Windows if you change remote login passwordfile parameter to NONE then for login you have sqlnet.ora parameter set as NTS value.

5. Start the database and check connectivity

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 8 10:36:39 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

Error: “ORA-01031: insufficient privileges”
Invalid username and password from Server or Client Side connectivity issue
One of our customer says he is able to connect from Server side but when he tried to connect from client side he is getting error of “ORA-01031: insufficient privileges”
Solution:
1. If you are connecting on Server SQLPLUS then it use SQLNET value as NTS. If you provide sys user name and password on same server
then it connected but if you tried to connect from client side then it give you error.
2. Remote_login_passwordfile parameter to have ‘exclusive’
3. Password file present for the instance where you try to connect it.