Oracle 11g Logo

Alter database open failed with ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656]

After power failure at my customer end, database unable to open with above-mentioned oracle error message. Due to power failure controlfile got logical corruption.

This Problem is caused by Storage Problem of the Database Files. The Subsystem (eg. SAN) crashed while the Database was open. The Database then crashed because the Database Files were not accessible anymore. This caused a lost Write into the Online RedoLogs and/or causing logical corruption in controlfile so Instance Recovery is not possible and raising the ORA-600.

Error log:

2017-05-08 14:11:22.391000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc (incident=1968168):
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1968168/db1_ora_6324_i1968168.trc
Aborting crash recovery due to error 600
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_ora_6324.trc:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [160296], [591], [656], [], [], [], [], [], [], []
ORA-600 signalled during: ALTER DATABASE OPEN...
2017-05-08 14:11:23.773000 +05:30
Trace dumping is performing id=[cdmp_20170508141123]

Solution:

Retrive details of all the controlfiles as below:

[oracle@db1 ~]$ sqlplus / as sysdba
SQL> Show parameter control_files;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/oradata/db1/control01.ctl,/u02/oradata/flash_recovery_area/db1/control02.ctl

Findout current redo log member when the power got failed.

SQL> select a.member, a.group#, b.status from v$logfile a ,v$log b where a.group#=b.group# and b.status='CURRENT' ;
MEMBE                        RGROUP#    STATUS
---------------------------- ---------- ----------------
/u02/oradata/db1/redo03.log  3          CURRENT
SQL> Shutdown abort;
ORACLE instance shut down.

Consider backup of controlfile, so that we will keep the current state of controlfile in the case of any worst.

[oracle@db1 ~]$ cp /u02/oradata/db1/control01.ctl /u02/oradata/db1/control01.ctl_backup
[oracle@db1 ~]$ cp /u02/oradata/flash_recovery_area/db1/control02.ctl /u02/oradata/flash_recovery_area/db1/control02.ctl_backup

Startup mount database:

[oracle@db1 ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> Startup mount;
ORACLE instance started.
Total System Global Area 5010685952 bytes
Fixed Size 2212936 bytes
Variable Size 3489663928 bytes
Database Buffers 1476395008 bytes
Redo Buffers 42414080 bytes
Database mounted.

Recover database with the help of following command and input current redo log member fetched above:

SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 130697634 generated at 05/08/2017 10:59:22 needed for thread1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2017_05_08/o1_mf_1_160296_%u_.arc
ORA-00280: change 130697634 for thread 1 is in sequence #160296
Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/db1/redo03.log
Log applied.
Media recovery complete.
SQL>
SQL> Alter database open resetlogs ;

Database altered.
SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ WRITE           DB1

Cheers!! Stay Tune. 🙂

Oracle 11g Logo

Query to find out progress of transaction recovery by SMON

Have you killed the long running query?

SMON is solely responsible for recovering transactions when you kill any large running query( truncate and delete ) by killing OS process or aborting database, SMON will take all possible CPU to rolling back previous state and its highly time-consuming task.
And frustrated DBA/Person will think bouncing database will resolve this problem completely, as obvious “shutdown immediate” will take equal amount of time to rollback and finally database being “aborted” by the user.
Kindly note: Shutting down or aborting database is not the solution, and won’t reduce the amount of work SMON need to be performed to complete rollback.

Following query will help you to identify the total amount of work to be rolled back by SMON, simply progress of transaction recovery. Run it multiple time.

SQL> SELECT usn, state, undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions;

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       112623     4658       107965     09-MAR-2017 17:30:16
 737       RECOVERING       275333     1755       273578     09-MAR-2017 21:49:03

<<After some time>>

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       28647      9324       19323      09-MAR-2017 17:16:50
 737       RECOVERING       240190     2792       237398     09-MAR-2017 20:33:12

Note:

On the same time, your ADRCI database log will be flooded with following:

2017-03-09 17:17:25.063000 +05:30
SMON: Restarting fast_start parallel rollback

No action is required. No corruption applies unless the error is followed by other internal errors on the object.

Thanks

Stay tune. 🙂

Oracle 11g Logo

Generate AWR – Automatic Workload Repository (AWR) in oracle 11g

Many times DBA need to monitor specific loaded/heavy database related activity in order to fix database performance, for that generating AWR report is perfect solution and he will get all necessary information he needed to tune the database. This article is step by step approach to generate AWR report.

Step-I:

Before loaded activity, issue following command to take snapshot of database as start point of AWR report.

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Step-II: Perform your activity.

Perform your activity.

Step-III: Right after the activity, issue

Right after the activity, issue following command to take again snapshot of database as end point of AWR report.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Note: Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Step-IV:

Generate AWR report: (Follow the instructions and provide input acrrodingly)

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

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html

Select the appropriate instance:

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
4119037639 1 PRODDB ifsprod localhost.lo
caldomain
4119037639 1 UATDB ifsuat localhost.lo
caldomain
* 4119037639 1 PRODDB ifsprod PR
Enter value for dbid: 4119037639
Enter value for inst_num: 1
Using 1 for instance number

Specify the number of days of snapshots to choose from:

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ifsprod IFSPROD 66276 26 Nov 2016 00:00 1
66277 26 Nov 2016 00:30 1
66278 26 Nov 2016 01:01 1
66279 26 Nov 2016 01:30 1
66280 26 Nov 2016 02:00 1
66281 26 Nov 2016 02:30 1
66282 26 Nov 2016 03:00 1
66283 26 Nov 2016 03:30 1
66284 26 Nov 2016 04:00 1
66285 26 Nov 2016 04:30 1
66286 26 Nov 2016 05:00 1
66287 26 Nov 2016 05:30 1
66288 26 Nov 2016 06:00 1
66289 26 Nov 2016 06:31 1
66290 26 Nov 2016 07:00 1
66291 26 Nov 2016 07:30 1
66292 26 Nov 2016 08:00 1
66293 26 Nov 2016 08:30 1
66294 26 Nov 2016 09:00 1
66295 26 Nov 2016 09:30 1
66296 26 Nov 2016 10:00 1
66297 26 Nov 2016 10:30 1
66298 26 Nov 2016 11:00 1
66299 26 Nov 2016 11:30 1
66300 26 Nov 2016 12:00 1
66301 26 Nov 2016 12:30 1
66302 26 Nov 2016 13:00 1
66303 26 Nov 2016 13:30 1
66304 26 Nov 2016 14:00 1
66305 26 Nov 2016 14:30 1
66306 26 Nov 2016 14:41 1
66307 26 Nov 2016 14:45 1

Specify the Start point and end point for the snapshot, input from above:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66306
Begin Snapshot Id specified: 66306
Enter value for end_snap: 66307
End Snapshot Id specified: 66307

Name the report:

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_66306_66307.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: AWR_between_2_manual_snapshot
.
..
...
Report written to AWR_between_2_manual_snapshot
SQL> exit

search for html format snapshot in current directory.

Thanks. Stay Tune. 🙂

Oracle 11g Logo

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

My one of the client database backup have been failed since one week due to “RMAN-06183 datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE”. Error itself is pretty explanatory. The size of the system01.dbf datafile had exceeded the RMAN parameter MAXSETSIZE, i.e. 15G

Error log:
RMAN>
Starting backup at 2016-11-03 10:41:39
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1008 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2016-11-03 10:41:41
channel ORA_DISK_1: finished piece 1 at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/backupset/2016_11_03/o1_mf_ncnnf_TAG20161103T104140_d1okrg13_.bkp tag=TAG20161103T104140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2016-11-03 10:41:43
Starting Control File and SPFILE Autobackup at 2016-11-03 10:41:43
piece handle=/cbsrmanbackup/rman/VCBCBS/autobackup/2016_11_03/o1_mf_s_926937704_d1okrjqq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2016-11-03 10:41:45
Starting backup at 2016-11-03 10:41:49
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/03/2016 10:41:49
RMAN-06183: datafile or datafile copy /vcboradata/oradata/datafile/system01.dbf (file number 1) larger than MAXSETSIZE
Recovery Manager complete.

RMAN parameters:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name VCBCBS are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
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 15 G;
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/cbsrmanbackup/rman/snapcf_vcbcbs.f';

Confirm current size of the system01.dbf, as below, used size is 15336.1 which is greater then 15G.

RMANTBS /vcboradata/oradata/datafile/rmantbs.dbf 1000 70.9 929.06
SYSAUX /vcboradata/oradata/datafile/sysaux01.dbf 1400 880.3 519.75
SYSTEM /vcboradata/oradata/datafile/system01.dbf 15400 15336.1 63.94
UNDOTBS1 /vcboradata/oradata/datafile/undotbs01.dbf 4725 97.3 4627.75
USERS /vcboradata/oradata/datafile/users01.dbf 50 15.9 34.13

Workaround:
Increase the size of MAXPIECESIZE RMAN parameter from 15G to 33G. (You can increase it as per your convenience)
More on MAXPIECESIZE:
This parameter is use to set limits on the size of backup pieces. You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets.

RMAN> CONFIGURE MAXSETSIZE TO 33G;
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 15 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 33 G;
new RMAN configuration parameters are successfully stored

After above changes, backup successfully completed.

Have a nice time ahead.

Stay Tune. 🙂

Oracle 11g Logo

ORA-08104: this index object xxxxx is being online built or rebuilt and ORA-00031: session marked for kill

My second attempt to rebuild below mentioned index failed with “ORA-08104-this index object 22624 is being online built or rebuilt”. I am getting this oracle error because my first attempt to rebuild same index failed due to abnormal session termination, it was incomplete rebuild.

Error log:

SQL> ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS;
ALTER INDEX UPASSUSR.IDX_TRANSLOG_OBJECTNAME REBUILD online TABLESPACE UPASSTBS
*
ERROR at line 1:
ORA-08104: this index object 22624 is being online built or rebuilt

According to oracle support(Doc ID 375856.1) we can rid out of the issue by cleaning garbage with the help of dbms_repair.online_index_clean function. But no luck after successful execution of dbms_repair.online_index_clean function.

declare
lv_ret BOOLEAN;
begin
lv_ret := dbms_repair.online_index_clean(22624);
end;
/
PL/SQL procedure successfully completed.

Same clean-up logs popped up in ADRCI with same object no(i.e.22624) but no luck.

2016-09-27 13:21:06.373000 +05:30
online index (re)build cleanup: objn=22624 maxretry=2000 forever=0

Even, I tried dropping index and mark as unusable but unlucky again.

SQL> drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME;
drop index UPASSUSR.IDX_TRANSLOG_OBJECTNAME
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

So, I have decided to kill that session with the help of “ALTER SYSTEM KILL SESSION”, I got require details with the help of following SQL:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE, S.PORT, 
S.LOGON_TIME, SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID 
AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS
AND O.OBJECT_NAME = 'AOUP_TRANS_LOG';

After 60 seconds of waiting, SQL command ended up with “ORA-00031: session marked for kill” oracle error. 🙁

SQL> ALTER SYSTEM KILL SESSION '3423,39597';
ALTER SYSTEM KILL SESSION '3423,39597'
*
ERROR at line 1:
ORA-00031: session marked for kill

Finally, I got the solution by killing process at OS level:
kill -9 spid     (from above)

Thank god, Index rebuild was successful.

Object flag before killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2562

Object flag after killing OS process:

SQL> select obj#,flags from ind$ where obj#=22624;
OBJ#       FLAGS
---------- ----------
22624      2050

Have a great time ahead.

Stay Tune. 🙂

Oracle 11g Logo

ORA-03113: end-of-file on communication channel while startup

My one of the client reported me ORA-03113 oracle error message. He is facing problem while starting up database. Usually this error occurs when connection between Client and Server process was broken OR It can be any big problem.

SQL> startup
ORACLE instance started.
Total System Global Area 8584982528 bytes
Fixed Size 2262088 bytes
Variable Size 4462742456 bytes
Database Buffers 4093640704 bytes
Redo Buffers 26337280 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2588
Session ID: 1705 Serial number: 5

On further diagnosis of ADRCI logs, I found disk space size assigned to Flash recovery area falling short. ADRCI logs are as follows:

2016-09-12 12:46:00.960000 +05:30
Successful mount of redo thread 1, with mount id 3346245764
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=564
2016-09-12 12:46:02.333000 +05:30
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_2588.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
 then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
 BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
 reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
 system command was used to delete files, then use RMAN CROSSCHECK and
 DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to 'D:\APP\ADMINISTRATOR\FAST_RECOVE
RY_AREA\MNSBDB\ARCHIVELOG\2016_09_12\O1_MF_1_454_%U_.ARC'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\trace\mnsbdb_ora_25
88.trc:
ORA-16038: log 8 sequence# 454 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 8 thread 1: 'D:\APP\ADMINISTRATOR\ORADATA\MNSBDB\REDO08.LO
G'
USER (ospid: 2588): terminating the instance due to error 16038
ARC0: STARTING ARCH PROCESSES
2016-09-12 12:46:03.347000 +05:30
Logins disabled; aborting ARCH process startup (1092)
ARC0: Archival disabled due to shutdown: 1092
Shutting down archive processes
Archiving is disabled
System state dump requested by (instance=1, osid=2588), summary=[abnormal instan
ce termination].
System State dumped to trace file D:\APP\ADMINISTRATOR\diag\rdbms\mnsbdb\mnsbdb\
trace\mnsbdb_diag_4064.trc
Dumping diagnostic data in directory=[cdmp_20160912124603], requested by (instan
ce=1, osid=2588), summary=[abnormal instance termination].
2016-09-12 12:46:05.266000 +05:30
Instance terminated by USER, pid = 2588

So, I finally assigned more size to Flash recovery area using following command, and database opened successfully.

 SQL> startup mount
 ORACLE instance started.
 Total System Global Area 8584982528 bytes
 Fixed Size 2262088 bytes
 Variable Size 4462742456 bytes
 Database Buffers 4093640704 bytes
 Redo Buffers 26337280 bytes
 Database mounted.
SQL> alter system set db_recovery_file_dest_size=100G scope=both;
SQL> alter database open;

I strongly recommend you to monitor your every database move from ADRCI, this will easy for your to understand problem area and act accordingly.

Your comments highly appreciated.

Stay Tune. 🙂

Oracle 11g Logo

How to drop database manually, without using Database Configuration Assistant(DBCA)

Before proceed for entire database deletion, kindly consider full database backup for your future reference, if required.

Connect to target database with sys user, and issue following query to check database mode:

[oracle@testserver ~]$ sqlplus / as sysdba
SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
READ WRITE           BLADE2

Normally shutdown your database:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

startup your database in mount mode with exclusive restrict, as follows:

SQL> startup mount exclusive restrict;
ORACLE instance started.
Total System Global Area 1.0088E+10 bytes
Fixed Size 2215984 bytes
Variable Size 5704257488 bytes
Database Buffers 4362076160 bytes
Redo Buffers 19640320 bytes
Database mounted.

Database is in mount mode:

SQL> select open_mode,name from v$database;
OPEN_MODE            NAME
-------------------- ---------
MOUNTED              BLADE2

Issue following SQL command in order to drop database, this will drop entire database with datafiles, control files and log files,etc.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Database dropped successfully.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

RMAN-06900 RMAN-06901 ORA-19921

Received RMAN-06900,RMAN-06901 and ORA-19921 while logged in to RMAN prompt to verify daily backups. (OS:Redhat linux 6.5)

WARNING message:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:34:39 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19921: maximum number of 128 rows exceeded
RMAN>

On further diagnosing and suggested by OERR utility, I have realized there is one old active session from Aug 23, as mentioned below.

[oracle@primary ~]$ oerr ORA 19921
19921, 00000, "maximum number of %s rows exceeded"
// *Cause: The maximum number of rows in the V$RMAN_STATUS or V$RMAN_OUTPUT
// table has been exceeded.
// *Action: Close some of existing and unused RMAN connections and sessions.

So lets find out old session with ps -ef:

[oracle@primary logs]$ ps -ef|grep rman
oracle 543 32693 0 10:50 pts/4 00:00:00 grep rman
oracle 19783 19782 47 Aug23 ? 1-07:13:42 rman target /

There is one old active session above, OS ID: 19783, since 23 aug.

[oracle@primary ~]$ date
Fri Aug 26 10:57:13 IST 2016
[oracle@primary logs]$ kill -9 19783

After successfully killed old session, we are no more facing mentioned list of warnings, as below:

[oracle@primary logs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 26 10:50:54 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BLADE1 (DBID=3381279798)
RMAN>

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

rename/relocate SYSTEM, SYSAUX or User Tablespace datafiles

This is traditional approach to move/rename SYSTEM, SYSAUX or users tablespace. In this method, we need database downtime as we can’t take SYSTEM or SYSAUX tablespace offline.

I recommend full cold backup before performing mentioned activity.

Consider scenario where I want to move SYSTEM and SYSAUX tablespace to new location.

Step I:

After considering full cold backup of your database, shutdown database with normal or immediate mode only.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step II:

After shut down database normally, I am going to copy datafiles from original location to its new location.

Following query will provide tablespace name and its datafiles details.

SQL> select file_name,tablespace_name from dba_data_files;

Copying datafiles:

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/app/oracle/oradata/RTS_NEW/system01.dbf
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf

After copy, verify size of datafiles on both locations to check whether copy successful or not.

Step III:

Start database in mount mode:

[oracle@PR oradata]$ sqlplus / as sysdba 
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 478153504 bytes
Database Buffers 272629760 bytes
Redo Buffers 2768896 bytes
Database mounted.

Step IV:

After successful mount, rename the datafiles to its locations as below:

SQL> alter database rename file '/u01/app/oracle/oradata/RTS/system01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/system01.dbf';
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/RTS/sysaux01.dbf' to '/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf';
Database altered.

Step V:

Open database:

SQL> alter database open;
Database altered.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Verify that system and sysaux datafiles are moved/renamed successful to new location.

SQL> select file_name,tablespace_name from dba_data_files;
/u01/app/oracle/oradata/RTS_NEW/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/RTS_NEW/system01.dbf SYSTEM

SYSTEM and SYSAUX datafiles relocated successfully to new location.

This can be done with the help of RMAN, but only non-system tablespaces can be relocated. Click me to know more about it.

OR

You can relocate datafiles without getting offline in oracle 12c.

Thanks, Stay Tune. 🙂

Oracle 11g Logo

ORA-00600: internal error code, arguments:[4194] ORA-00603: ORACLE server session terminated by fatal error. PMON terminating the instance due to error 472

Oracle instance terminated immediate after instance startup due to following mentioned oracle error:

Cause:
A mismatch has been detected between Redo records and rollback (Undo) records.
We are validating the Undo record number relating to the change being applied against the maximum undo record number recorded in the undo block. This error is reported when the validation fails. This error may indicate a rollback segment corruption.
Note: This may require a recovery from a database backup depending on the situation.

Adrci logs are as follows:

Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc (incident=1014790):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
replication_dependency_tracking turned off (no async multimaster replication found)
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014790/db1_smon_11168_i1014790.trc
2016-06-30 11:13:35.355000 +05:30
Starting background process QMNC
QMNC started with pid=25, OS id=11249
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Completed: ALTER DATABASE OPEN
2016-06-30 11:13:36.558000 +05:30
db_recovery_file_dest_size of 163840 MB is 1.12% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery stopped at EOT rba 13618.106.16
Block recovery completed at rba 13618.106.16, scn 0.3980712368
Doing block recovery for file 3 block 160
Resuming block recovery (PMON) for file 3 block 160
Block recovery from logseq 13618, block 56 to scn 3980712329
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.59.16, scn 0.3980712330
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_smon_11168.trc:
ORA-01595: error freeing extent (10) of rollback segment (3))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Starting background process SMCO
SMCO started with pid=29, OS id=11268
Trace dumping is performing id=[cdmp_20160630111337]
2016-06-30 11:13:37.819000 +05:30
Starting background process CJQ0
CJQ0 started with pid=28, OS id=11276
2016-06-30 11:13:41.579000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014942):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014942/db1_j001_11285_i1014942.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j004_11291.trc:
2016-06-30 11:13:43.111000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc (incident=1014934):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014934/db1_j000_11283_i1014934.trc
Trace dumping is performing id=[cdmp_20160630111343]
2016-06-30 11:13:45.937000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j000_11283.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc (incident=1014943):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014943/db1_j001_11285_i1014943.trc
2016-06-30 11:13:46.903000 +05:30
Trace dumping is performing id=[cdmp_20160630111346]
2016-06-30 11:13:48.279000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j001_11285.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111349]
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1019488/db1_j001_11285_i1019488.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Trace dumping is performing id=[cdmp_20160630111351]
2016-06-30 11:13:53.489000 +05:30
Doing block recovery for file 3 block 26014
Resuming block recovery (PMON) for file 3 block 26014
Block recovery from logseq 13618, block 56 to scn 3980712372
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13618 Reading mem 0
 Mem# 0: /u02/oradata/db1/redo_1_1.log
 Mem# 1: /u02/oradata/db1/redo_1_2.log
 Mem# 2: /u02/oradata/db1/redo_1_3.log
Block recovery completed at rba 13618.106.16, scn 0.3980712374
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc (incident=1014702):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/db1/db1/incident/incdir_1014702/db1_pmon_11144_i1014702.trc
2016-06-30 11:13:55.766000 +05:30
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_pmon_11144.trc:
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []
PMON (ospid: 11144): terminating the instance due to error 472
Instance terminated by PMON, pid = 11144

Workaround:
Startup database in mount mode:

SQL> startup mount;

Check current undo tablespace:

SQL> show parameter undo_tablespace;

Alter undo management to ‘MANUAL’:

SQL> alter system set undo_management='MANUAL' scope=spfile;

Bounce database to open mode, meanwhile please verify adrci logs, if there is problem.

SQL> shut immediate;
 SQL> startup;

Create new undo tablespace if everything is fine in adrci logs:

SQL> create undo tablespace newundotbs datafile '/u02/oradata/db1/newundotbs01.dbf' size 5G autoextend on next 300M maxsize 31G;

Change default undo tablespace to new one:

SQL> alter system set undo_tablespace='NEWUNDOTBS' scope=spfile;

Alter undo management to ‘AUTO’:

SQL> alter system set undo_management='AUTO' scope=spfile;

Bounce the database:

SQL> shut immediate;
SQL> startup;

Verify current undo tablespace:

SQL> show parameter undo_tablespace

done.

Thank you.
Stay Tune. 🙂

Oracle 11g Logo

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS and KUPW$WORKER.DISPATCH_WO ORA-01187: cannot read from file because it failed verification tests

Data pump export backup failed due to below mentioned oracle errors. It was failed due to verification tests failed on temp file: temp01. As a part of solution, I have delete old temporary tablespace and added new temporary tablespace with new temp file.

Data Pump Export Error Logs:

Export: Release 11.2.0.1.0 - Production on Mon Jun 20 11:47:01 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_09": system/******** directory=data_pump_bkup dumpfile=full_db_export.dmp logfile=export.log full=y
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS []
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WO
ORA-01187: cannot read from file because it failed verification te
ORA-01110: data file 201: '/u02/oradata/db1/temp01.dbf'
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
object line object
handle number name
0x1ed213be8 19028 package body SYS.KUPW$WORKER
0x1ed213be8 8191 package body SYS.KUPW$WORKER
0x1ed213be8 8980 package body SYS.KUPW$WORKER
0x1ed213be8 1651 package body SYS.KUPW$WORKER
0x1b97859d0 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_09" stopped due to fatal error at 11:

Solution:

Here is article on Delete old temporary tablespace and add new temporary tablespace with new temp file.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

TNS-12535: TNS:operation timed out AND TNS-00505: Operation timed out

My one of the client recently implemented firewall for security purpose, it was successful activity. When all the application server connected to database server, below mentioned error logged into an alert log file frequently. It was clear clue, firewall messed up with the client-server connections.

It was the problem with Firewall “idle session timeout” value. If the client-server connection idle for more than defined value then it was forcefully dropped by firewall.
Error log:

Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
Time: 13-APR-2016 19:26:50
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
 ns secondary err code: 12560
 nt main err code: 505 
TNS-00505: Operation timed out
 nt secondary err code: 110
 nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.xxx.xxx.xxx)(PORT=xxxx))

Work around for TNS-12535 and TNS-00505:

We can remove or increase the firewall setting for maximum idle time. but it’s not recommended, because who know how much time client-server connection going to be idle.

So, append following parameter to RDBMS_HOME/network/admin/sqlnet.ora file on the database server. Resulting, database server check application server every <n> minutes to see if it is up by sending packets. So the connections are found as active by firewall and they will not terminated forcefully.

SQLNET.EXPIRE_TIME=n
Where <n> is a non-zero value set in minutes.

Reference: Doc ID 1628949.1

Your suggestions and comments are highly appreciated, if any.

Thank you. 🙂

Oracle 11g Logo

Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV

Previously we covered How to open Manual Physical Standby Database in READ ONLY mode.

Let us discuss one scenario and then you will realize the use of Incremental backup from SCN.

Scenario:

Consider the production database configured with Manual data guard, and unfortunately any archive log file gets corrupted or lost. Here we solely depend on that corrupted archive log to continue database replication, in this case we can fix the problem with RMAN incremental backup from SCN. Input SCN from standby database current state and backup incremental from that SCN from Primary database, and recover standby database. By this method, we no more need that corrupted archive log file.

OR

There is huge archive log sequence gap between primary and standby database, and we need to recover it in one go.

Before taking Incremental backup from SCN, we need to perform some pre-requisites on primary and standby database server, are as follows:

First:

Check listener status, it should be running and listening to databases.

[oracle@PR ~]$ lsnrctl status

Second:

Check database mode and role, On primary site, database should be in READ WRITE mode and role should be PRIMARY.

On standby site, database should be in MOUNTED mode and role should be PHYSICAL STANDBY.

Steps to take incremental backup from SCN:

Step-I

On primary database, take standby controlfile backup:

SQL> alter database create standby controlfile as '/home/oracle/backup/standby_control.ctl';
Database altered.

Step-II

Get current SCN from Standby database:

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 957835

Step-III

On primary database, take RMAN incremental backup from SCN, take SCN as an input from Step-II:

[oracle@PR ~]$ rman target /
RMAN> backup device type disk incremental from scn 957835 database format '/home/oracle/backup/Inc_backup_%U';
Starting backup at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/RTS/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/RTS/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/RTS/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
using channel ORA_DISK_1
backup will be obsolete on date 20-MAY-16
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-MAY-16
channel ORA_DISK_1: finished piece 1 at 13-MAY-16
piece handle=/home/oracle/backup/Inc_backup_06r5f6g7_1_1 tag=TAG20160513T045757 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAY-16

Step-IV

Transfer backup to standby database server:

[oracle@DR backup]$ pwd
/home/oracle/backup
[oracle@DR backup]$ ll
total 59728
-rw-r--r--. 1 oracle oinstall 41615360 May 13 04:58 Inc_backup_05r5f6en_1_1
-rw-r--r--. 1 oracle oinstall 9797632 May 13 04:58 Inc_backup_06r5f6g7_1_1
-rw-r--r--. 1 oracle oinstall 9748480 May 13 04:36 standby_control.ctl

Step-V

On Standby database, Restore Incremental backup:

one:

Shutdown standby database and startup in nomount mode:

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 448793376 bytes
Database Buffers 301989888 bytes
Redo Buffers 2768896 bytes

Two:

Restore standby controlfile:

[oracle@DR ~]$ rman target /
RMAN> restore controlfile from '/home/oracle/backup/standby_control.ctl';
Starting restore at 13-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/RTS/control01.ctl
output file name=/u01/app/oracle/oradata/RTS/control02.ctl
Finished restore at 13-MAY-16

Three:

Alter database to MOUNT mode:

SQL> alter database mount;
Database altered.

Four:

Catalog RMAN incremental backup files to RMAN repository(In my case, i.e. controlfile):

[oracle@DR ~]$ rman target /
RMAN> catalog start with '/home/oracle/backup/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/backup/
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/backup/Inc_backup_06r5f6g7_1_1
File Name: /home/oracle/backup/standby_control.ctl
File Name: /home/oracle/backup/Inc_backup_05r5f6en_1_1

Five:

Recover standby database:

RMAN> recover database;
Starting recover at 13-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/RTS/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/RTS/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/RTS/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/RTS/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/Inc_backup_05r5f6en_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/Inc_backup_05r5f6en_1_1 tag=TAG20160513T045757
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
starting media recovery
unable to find archived log
archived log thread=1 sequence=0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2016 05:13:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 972205

Above recovery successful and expecting next archive log and starting SCN 972205, which is greater than old current SCN of standby database.( Ref: Step-II )
Ensure archive log gap sequence between primary and standby database, it should be zero or one or two, because logfiles might have switched due to heavy load on your production primary database.

Cheers!! Standby database successfully recovered from incremental backup. Now onward you can continue replication with new archive log files.

 

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on failover(Activate) of standby database.

Thank you 🙂

Oracle 11g Logo

Manual Physical Standby Failover – Activate Standby Database – Oracle 11g Manual Data Guard Part-V

In case of disaster with Primary Database site, or not available for production for any reason then we can activated standby database as a primary production database.

Note: If you are trying to activate standby database for production use, please get permission from senior authority to do so.

Previously, we covered “Incremental backup from SCN – Oracle 11g Manual Data Guard Part-IV”.

On Standby database, steps to perform failover OR Activate standby database:

One:

Check database listener, it should be running:

[oracle@DR ~]$ lsnrctl status

Two:

Check database mode and role, It should be MOUNTED and PHYSICAL STANDBY:

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

Three:

Copy archive logs from primary database, those yet to apply on standby database before activate it.

SQL> recover standby database until cancel;
AUTO

Four:

Activate standby database as primary database:

[oracle@DR ~]$ sqlplus / as sysdba
SQL> alter database activate standby database;
Database altered.

Five:

Shutdown standby database and startup:

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 448793376 bytes
Database Buffers 301989888 bytes
Redo Buffers 2768896 bytes
Database mounted.
Database opened.

Cheers!! Standby database successfully activated as Primary database. Failover successful.

Post activity check, ensure database mode and role, it should be READ WRITE and PRIMARY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Note: Please perform full database backup after failover.
Note: Once old primary database is available for use then you can reconfigure standby database.

Your suggestions and comments are highly appreciated, if any.

Stay tune 🙂

Oracle 11g Logo

How to open Manual Physical Standby Database in READ ONLY mode – Oracle 11g Manual Data Guard Part-III

You can use Manual Physical Standby database for query access for query offloading and reporting purposes by making a standby database to read only mode, resulting we can use the primary database exclusively for core purpose.(i.e. OLTP)

Note:

Simply you can’t apply archive logs to standby database server when database is in READ ONLY mode, but you can continue to transfer your archive logs to standby database server, Once your standby database back to MOUNT mode than you can apply your remaining archive logs.

Previously, We covered Manual data guard switch over and switch back activity step by step approach.

Steps are as follows:

Step-I

Verify database name, its mode and role by following command:

SQL> select name,open_mode,database_role from v$database;

The database is in mount state and its role is Physical Standby, as expected, good to go.

Step-II

Transfer all remaining archive logs from the primary database server to standby database server and apply it (If there is an archive log gap), before opening a standby database to READ ONLY mode. For more information on it, you can refer Step – XVI from my one of post on “Configuring Manual Data Guard”.

Step-III

Delete existing tempfile[s]:

select tf.name from v$tablespace ts,v$tempfile tf where ts.TS#=tf.TS# and ts.name=’TEMP’;
alter database tempfile ‘Above_temp_file[s]’ drop;

Step-IV

Open your Standby database in READ ONLY mode from MOUNTED mode:

SQL> alter database open read only;

Step-V

Create new tempfile[s]:

SQL> alter tablespace TEMP add tempfile '/path/temp02.dbf' size 200m autoextend on maxsize unlimited;

Step-VI

Repeat Step-I to verify standby database mode and its role.

 

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on Incremental backup from SCN.

Thank you. 🙂

Oracle 11g Logo

Switch over and switch back – Oracle 11g Manual Data Guard Part-II

In my Previous article we have covered How to configure oracle 11g Manual Data Guard, Now we will look into switch-over and switch-back activity.

Switch-over Pre-requisites:

One:

Check listener status on primary and standby database.

Primary Database Listener:

[oracle@PR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:49:31
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:44:06
Uptime 0 days 0 hr. 5 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
Service "RTSXDB" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Standby Database Listener:

[oracle@DR ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 11-MAY-2016 22:51:55
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DR.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 11-MAY-2016 22:45:18
Uptime 0 days 0 hr. 6 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DR.localdomain)(PORT=1521)))
Services Summary...
Service "RTSDR" has 1 instance(s).
 Instance "RTS", status READY, has 1 handler(s) for this service...
The command completed successfully

Two:

Check database mode on primary and standby database.

Primary Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS READ WRITE

Standby Database Mode:

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
RTS MOUNTED

Three:

Check database role on primary and standby database.

Primary Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PRIMARY

Standby Database:

SQL> select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
RTS PHYSICAL STANDBY

Four:

No datafiles should be in recovery mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('RECOVER');
no rows selected

Five:

No datafiles should be in offline mode on Primary and Standby database.

SQL> SELECT FILE# FILESTAT FROM V$DATAFILE WHERE STATUS in ('OFFLINE');
no rows selected

Six:

No datafiles should be in backup mode on Primary and Standby database.

SQL> select file# from v$backup where status='ACTIVE';
no rows selected

Switch-over steps:

Step-A:

Shutdown primary database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step-B:

Copy control file to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/prtemp/
[oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/prtemp/

Step-C:

Copy SPFile to temporary location on primary database.

[oracle@PR ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/prtemp/

Step-D:

Copy Redo log files to temporary location on primary database.

[oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS/
[oracle@PR RTS]$ ll *.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:08 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 22:46 redo03.log
[oracle@PR RTS]$ cp *.log /home/oracle/prtemp/

Step-E:

Copy needed archive log to DR site.

[oracle@PR RTS]$ cd /home/oracle/archdir/
[oracle@PR archdir]$ scp * oracle@DR:/home/oracle/archdir/

Step-F:

Get primary database to READ ONLY mode.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.
SQL> alter database open read only;
Database altered.

Step-G:

Apply all needed archive logs to standby database.

SQL> recover standby database until cancel;
ORA-00279: change 957836 generated at 04/26/2016 02:49:33 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_15_910140016.dbf
ORA-00280: change 957836 for thread 1 is in sequence #15
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 967269 generated at 04/26/2016 23:34:52 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_16_910140016.dbf
ORA-00280: change 967269 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/archdir/1_15_910140016.dbf' no longer needed for this recovery
ORA-00279: change 967272 generated at 04/26/2016 23:34:53 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_17_910140016.dbf
ORA-00280: change 967272 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/archdir/1_16_910140016.dbf' no longer needed for this recovery
.
..
...
ORA-00279: change 969653 generated at 05/09/2016 23:48:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/archdir/1_29_910140016.dbf
ORA-00280: change 969653 for thread 1 is in sequence #29
ORA-00278: log file '/home/oracle/archdir/1_28_910140016.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/home/oracle/archdir/1_29_910140016.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Step-H:

Shutdown standby database.

SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Step-I:

Copy control file to temporary location on standby database.

[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /home/oracle/drtemp/
[oracle@DR ~]$ cp /u01/app/oracle/oradata/RTS/control02.ctl /home/oracle/drtemp/

Step-J:

Copy SPFile to temporary location on standby database.

[oracle@DR dbs]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileRTS.ora /home/oracle/drtemp/

Step-K:

On Standby database, copy all files(from /home/oracle/drtemp/ directory) to primary database server(to /home/oracle/from_dr directory).

[oracle@DR ~]$ cd /home/oracle/drtemp/
[oracle@DR drtemp]$ ll
total 19044
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:24 control02.ctl
-rw-r-----. 1 oracle oinstall 2560 May 11 23:27 spfileRTS.ora
[oracle@DR drtemp]$ scp * oracle@PR:/home/oracle/from_dr

Step-L:

On Primary database, copy all files(from /home/oracle/prtemp/ directory) to standby database server(to /home/oracle/from_pr directory).

[oracle@PR ~]$ cd /home/oracle/prtemp/
[oracle@PR prtemp]$ ll
total 172656
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 May 11 23:10 control02.ctl
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo02.log
-rw-r-----. 1 oracle oinstall 52429312 May 11 23:12 redo03.log
-rw-r-----. 1 oracle oinstall 2560 May 11 23:10 spfileRTS.ora
[oracle@PR prtemp]$ scp * oracle@DR:/home/oracle/from_pr

Step-M:

On Standby database, replace copied files(source directory: /home/oracle/prtemp/) to its appropriate locations:

[oracle@DR from_pr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@DR from_pr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@DR from_pr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora
[oracle@DR from_pr]$ cp *.log /u01/app/oracle/oradata/RTS/

Step-N:

Perform role switching on old standby database: (old standby database to new Primary database)

one:

Startup mount old standby database.

[oracle@DR ~]$ sqlplus / as sysdba
...
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 755769344 bytes
Fixed Size 2217184 bytes
Variable Size 473959200 bytes
Database Buffers 276824064 bytes
Redo Buffers 2768896 bytes
Database mounted.

Two:

Recover database.

SQL> recover database;
Media recovery complete.

Three:

Drop tempfiles(From temporary tablespace) from old standby database. (Note: If this is your first switch over then you may skip this step)

Four:

Open database:

SQL> alter database open;
Database altered.

Five:

Create new tempfile:

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/RTS/temp02.dbf' size 100m autoextend on maxsize unlimited;
Tablespace altered.

On Primary database, replace copied files(source directory: /home/oracle/drtemp/) to its appropriate locations:

[oracle@PR from_dr]$ cp control01.ctl /u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR from_dr]$ cp control02.ctl /u01/app/oracle/oradata/RTS/control02.ctl
[oracle@PR from_dr]$ cp spfileRTS.ora $ORACLE_HOME/dbs/spfileRTS.ora

Shutdown old primary database.

SQL> shut immediate;

Step-O:

Perform role switching on old primary database: (old primary database to new standby database)

One:

Start database with nomount state:

SQL> startup nomount;

Two:

Alter database to mount state with standby database:

SQL> alter database mount standby database;
Database altered.

Cheers!! Switch over activity successfully carried out, now lets perform post checks on both the database servers:

Post checks:

Check database mode and role on new primary database, it shall be READ WRITE and Primary.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS READ WRITE PRIMARY

Check database mode and role on new standby database, it shall be MOUNTED and PHYSICAL STANDBY.

SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RTS MOUNTED PHYSICAL STANDBY

We are done with switch over activity.

Switch-back activity steps:

There is no difference in switch-over and switch-back steps. Use above steps to do it.
Cheers!!

Your suggestions and comments are highly appreciated, if any.

Stay tuned with my next article on How to open Standby Database in Read ONLY mode.

Thank you. 🙂

Oracle 11g Logo

Steps to configure Manual Data Guard – Oracle 11g Manual Data Guard Part-I

This article is about step by step approach to configure Manual Physical Standby Data Guard in Oracle 11g. In my case, Ingredients to simulate Manual Physical Standby Manual data guard environment are as below:

2 VM’s, PR and DR with enough CPU and RAM in order to run oracle database.

Primary server configuration:

  • CentOS 6.5
  • Server name: PR
  • IP: 192.168.239.130
  • Oracle 11g software plus oracle instance.
  • Oracle SID/Global_name: RTS
  • Oracle db_unique_name: RTS

Standby server configuration:

  • CentOS 6.5
  • Server name: DR
  • IP: 192.168.239.131
  • Oracle 11g software only.
  • Oracle SID/Global name: RTS
  • Oracle db_unique_name: RTSDR

Note:

Oracle version on Primary and Standby should be identical. i.e. In my case it’s 11.2.0.1.0
db_unique_name on standby and primary database should be different.
Primary and Standby server should ping each other by IP as well as its server name. In order to ping with its server name, edit /etc/hosts file accordingly.

PR /etc/hosts file:

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.130    PR.localdomain    PR
192.168.239.131    DR

DR /etc/hosts file:

[root@dr ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.239.131    DR.localdomain    DR
192.168.239.130    PR

Lets start the configuration:

Step -I

Verify your database is in Archive Log Mode or NO archive log mode, if not than my one of the post will help you to configure your Oracle 11g database is in archive log mode. Primary database need to be in Archive log mode.

Step -II

Enable Force Logging on Primary database(Hostname:PR)

Following query will help you find out your database is in force logging or not:

SQL> column force_logging format a15
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
NO

Following query will help you to enable force logging:

SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------
YES

Step – III

Backup primary database with the help of RMAN:

 [oracle@PR ~]$ rman target /
 connected to target database: RTS (DBID=1470135364)
 RMAN> run {
 allocate channel c1 device type disk format '/home/oracle/prdg/bkup/%U';
 backup as compressed backupset full database plus archivelog;
 release channel c1;
 }

Copy RMAN backup to standby database(Hostname: DR)

 [oracle@PR ~]$ cd /home/oracle/prdg/bkup
 [oracle@PR bkup]$ ll
 total 266332
 -rw-r-----. 1 oracle oinstall 12165120 Apr 18 03:55 09r3ah13_1_1
 -rw-r-----. 1 oracle oinstall 259457024 Apr 18 03:56 0ar3ah17_1_1
 -rw-r-----. 1 oracle oinstall 1097728 Apr 18 03:56 0br3ah3s_1_1
 -rw-r-----. 1 oracle oinstall 4096 Apr 18 03:56 0cr3ah3v_1_1
 [oracle@PR bkup]$ scp * oracle@DR:/home/oracle/drdg/bkup/

Step – IV

Create Standby Controlfile on Primary database(Hostname:PR)

SQL> alter database create standby controlfile as '/home/oracle/prdg/stndby_control.ctl';
Database altered.

Copy Standby Controlfile to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control01.ctl
[oracle@PR ~]$ scp /home/oracle/prdg/stndby_control.ctl oracle@DR:/u01/app/oracle/oradata/RTS/control02.ctl

Step – V

Create parameter file(i.e. initRTS.ora) on Primary database(Hostname:PR). If you are not using spfile than skip this step.

SQL> create pfile='/home/oracle/prdg/initRTS.ora' from spfile;

Copy parameter file(i.e. initRTS.ora) to standby database(Hostname: DR)

[oracle@PR ~]$ scp /home/oracle/prdg/initRTS.ora oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VI

Copy password file to standby database(Hostname: DR). (Note: create password file if not created for your database.)

[oracle@PR ~]$ cd $ORACLE_HOME/dbs
[oracle@PR dbs]$ ll orapwRTS
-rw-r-----. 1 oracle oinstall 2048 Apr 18 03:23 orapwRTS
[oracle@PR dbs]$ scp orapwRTS oracle@DR:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

Step – VII

Create Required Directory Structure on standby database server(Hostname: DR). In my case I am going to replicate same directory structure as mentioned in parameter file.

=== initRTS.ora ===
 *.audit_file_dest='/u01/app/oracle/admin/RTS/adump'
 *.control_files='/u01/app/oracle/oradata/RTS/control01.ctl','/u01/app/oracle/oradata/RTS/control02.ctl'
 *.diagnostic_dest='/u01/app/oracle'
 *.log_archive_dest_1='LOCATION=/home/oracle/archdir'
 === End ===

Directory structure on standby database server.(Hostname: DR)

 [oracle@DR ~]$ mkdir -p /u01/app/oracle/admin/RTS/adump
 [oracle@DR ~]$ mkdir -p /u01/app/oracle/oradata/RTS/
 [oracle@DR ~]$ mkdir -p /u01/app/oracle
 [oracle@DR ~]$ mkdir -p /home/oracle/archdir

Step – VIII

Configure tnsname.ora and listener.ora and Start listener on standby database(Hostname: DR)

 === tnsnames.ora ===
 LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR.localdomain)(PORT = 1521))
 )
 )
 ADR_BASE_LISTENER = /u01/app/oracle
 === End ===
 ===listener.ora ===
 RTS =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTS)
 )
 )
 RTSDR =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = DR)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = RTSDR)
 )
 )
 === End ===

Step – IX

Add following parameters to initRTS.ora file on Standby database. (Hostname: DR)

*.db_unique_name='RTSDR'

In case of your datafile and logfile location on standby is different from primary than add following parameter to initRTS.ora file.

*.db_file_name_convert=('location on primary','location on Stanndby')
*.log_file_name_convert=('location on primary','location on Stanndby')

Step – X

Connect Standby Database

 $ export ORACLE_SID=RTS
 sqlplus / as sysdba
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
 SQL>

Step – XI

Create spfile from existing parameter file (i.e.initRTS.ora) and bounce the database:

 SQL> create spfile from pfile;
 SQL> Shut immediate;
 SQL> startup mount;

Step – XI

Restore and recover database with the help of RMAN backed up files from primary database. (Step – III)

 $ rman target /

On standby database, Practically there is no backup taken from RMAN, but if you issue following command to list out existing backups, than RMAN will show you full database backup information of primary database.(Step – III) Because RMAN repository is stored in control file and we have copy pasted standby control file from primary to standby database.

 RMAN> list backup of database summary;
 using target database control file instead of recovery catalog
 List of Backups
 ===============
 Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
 ------- -- -- - ----------- --------------- ------- ------- ---------- ---
 10 B F A DISK 18-APR-16 1 1 YES TAG20160418T035519

Just cross check and delete invalid entry from control file with the help of following RMAN commands:

 RMAN> crosscheck backup;
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=25 device type=DISK
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/01r3vadq_1_1 RECID=1 STAMP=910141882
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/02r3vadu_1_1 RECID=2 STAMP=910141886
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/03r3vaga_1_1 RECID=3 STAMP=910141963
 crosschecked backup piece: found to be 'EXPIRED'
 backup piece handle=/home/oracle/prdg/bkup/04r3vagc_1_1 RECID=4 STAMP=910141964
 Crosschecked 4 objects
RMAN> delete noprompt force expired backup;

Step – XII

Catalog all RMAN backup file entry to control file with following:

 RMAN> catalog start with '/home/oracle/drdg/bkup/';
 using target database control file instead of recovery catalog
 searching for all files that match the pattern /home/oracle/drdg/bkup/
 List of Files Unknown to the Database
 =====================================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1
 Do you really want to catalog the above files (enter YES or NO)? yes
 cataloging files...
 cataloging done
 List of Cataloged Files
 =======================
 File Name: /home/oracle/drdg/bkup/0ar3ah17_1_1
 File Name: /home/oracle/drdg/bkup/09r3ah13_1_1
 File Name: /home/oracle/drdg/bkup/0cr3ah3v_1_1
 File Name: /home/oracle/drdg/bkup/0br3ah3s_1_1

Step – XIII

Restore and recover database with the help of following RMAN commands:

 run
 {
 restore database;
 recover database;
 }

Above recovery session will be end up with RMAN error message like mentioned below. Recovery successful.

 RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of recover command at 04/26/2016 02:18:57
 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 954414

Step – XIV

Shutdown database and Startup Standby Database(Mount mode):

 sqlplus / as sysdba
 SQL> shutdown immediate;
 ORA-01109: database not open
 Database dismounted.
 ORACLE instance shut down.
 SQL> startup nomount;
 ORACLE instance started.
 Total System Global Area 960372736 bytes
 Fixed Size 2219152 bytes
 Variable Size 562037616 bytes
 Database Buffers 390070272 bytes
 Redo Buffers 6045696 bytes
SQL> alter database mount standby database;
 Database altered.

Step – XV

Cheers!! Our Manual Data Guard configuration have configured successfully.

Verify Manual Data Guard configuration on Standby database with the help of following SQL query:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS MOUNTED PHYSICAL STANDBY

Issue same above SQL query on primary database:

 SQL> select name,open_mode,database_role from v$database;
 NAME OPEN_MODE DATABASE_ROLE
 --------- -------------------- ----------------
 RTS READ WRITE PRIMARY

Step – XVI

Verify your Manual Data Guard configuration is working properly or not with the help of generating and applying archive logs on standby database:

Step – A

Generate multiple archive logs on primary database, issue following SQL query multiple times:

 SQL> alter system switch logfile;
 System altered.
 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Step – B

Transfer above generated archive logs to standby database server @ path: /home/oracle/archdir/ (As per parameter: *.log_archive_dest_1 set for standby database)

scp * oracle@DR:/home/oracle/archdir/

//Above SCP linux command will transfer all the archive logs from mentioned directory. It will overwrite old files. (Note: I used this shortcut because I am demonstrating you on my test environment, In case of production, please copy only newly generated files)

Step – C

Issue following SQL to query for maximum of sequence number of applied archived log on standby database.(Hostname: DR)

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 3

Apply copy pasted archive logs on standby database as below,

 SQL> recover standby database until cancel;
 ORA-00279: change 1008623 generated at 04/18/2016 03:56:47 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_18_908844551.dbf
 ORA-00280: change 1008623 for thread 1 is in sequence #18
 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
 AUTO
 ORA-00279: change 957138 generated at 04/26/2016 02:20:59 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_5_910140016.dbf
 ORA-00280: change 957138 for thread 1 is in sequence #5
 ORA-00278: log file '/home/oracle/archdir/1_4_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957141 generated at 04/26/2016 02:21:00 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_6_910140016.dbf
 ORA-00280: change 957141 for thread 1 is in sequence #6
 ORA-00278: log file '/home/oracle/archdir/1_5_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957144 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_7_910140016.dbf
 ORA-00280: change 957144 for thread 1 is in sequence #7
 ORA-00278: log file '/home/oracle/archdir/1_6_910140016.dbf' no longer needed for this recovery
 ORA-00279: change 957147 generated at 04/26/2016 02:21:01 needed for thread 1
 ORA-00289: suggestion : /home/oracle/archdir/1_8_910140016.dbf
 ORA-00280: change 957147 for thread 1 is in sequence #8
 ORA-00278: log file '/home/oracle/archdir/1_7_910140016.dbf' no longer needed for this recovery
 ORA-00308: cannot open archived log '/home/oracle/archdir/1_8_910140016.dbf'
 ORA-27037: unable to obtain file status
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

All transfered archive logs have applied on standby database successfully, now check maximum of sequence number of applied archived log.

 SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;
 MAX(SEQUENCE#)
 --------------
 7

Maximum of sequence number of applied archived log on primary and standby is same, our configuration successful. Cheers!!!

Your comment and suggestion are highly appreciated.

Stay Tuned with my next article on “Switch over and switch back – Oracle 11g Manual Data Guard Part-II“.

Thank you. 🙂

Oracle 11g Logo

How to configure oracle 11g database in archivelog mode

Connect database as sys user to configure it in archivelog mode.

[oracle@PR ~]$ sqlplus / as sysdba

Verify your database is in archivelog log mode or not, In our case obviously not.

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

OR

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           3

To configure database in archivelog mode, follow the steps below:

Step-I: Shutdown database:

SQL> shutdown immediate;

Step-II: Startup your database in mount mode:

SQL> startup mount;

Step-III: Configure database in archivelog:

SQL> alter database archivelog;

Step-IV: Open database in read write mode:

SQL> alter database open;

Database is in archive log mode now, you can verify with following SQL command, Result shows Database log mode is “Archive Mode” and “Automatic archival” is Enabled.

SQL> archive log list;
Database log mode               Archive Mode
Automatic archival              Enabled
Archive destination             /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence      1
Next log sequence to archive    3
Current log sequence            3

You can change archive log destination with following:

SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/archdir' scope=both;

Following command shows, the database is in archivelog mode:

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

 

Your comment and suggestion are highly appreciated.

Thank you. Stay Tune. 🙂

Oracle 11g Logo

ORA-25153: “Temporary Tablespace is Empty” although temporary tablespace and tempfile is available

According to oracle docs, the cause of the oracle error is to attempt was made to use space in a temporary tablespace with no files(datafile). And based on that action suggested is to add tempfile to the temporary tablespace with ADD TEMPFILE command.

In my case, Temporary tablespace already exists with sufficient free space into it. So I verified DEFAULT temporary tablespace, It was set to invalid temporary tablespace name.

The issue had resolved by setting default temporary tablespace to valid one.

Following query will help you find out default temporary tablespace:

SQL> select property_name, property_value from database_properties where

property_name='DEFAULT_TEMP_TABLESPACE';

Following query will help you to set default temporary tablespace:

SQL> alter database default temporary tablespace NEWTEMP;

Thanks, Comments are highly appreciated.
Stay Tune. 🙂

Oracle 11g Logo

ORA-01031: insufficient privileges – “SQLPLUS / as sysdba” working, but “SQLPLUS sys/sys@YOUTH as sysdba” is not working.

I have faced ORA-01031 oracle error after migrating oracle database from oracle 10g (10.2.0.4.0) to Oracle 11g (11.2.0.1.0)
Error log is as follows:

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

But, I can connect the database with SYS user as follow, but not able to connect SYS user with credentials cum connection string(as mentioned above).

[oracle@newyouth dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:15:29 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
  • Oracle error itself pretty self explanatory, according to error I have cross checked all the permissions but it doesn’t work for me.
  • Listener.ora and tnsnames.ora files also verified and found correct.
  • Oracle environment variables (like ORACLE_SID,ORACLE_HOME and PATH) also set appropriately with no mistake.
  • REMOTE_LOGIN_PASSWORDFILE parameter set to EXCLUSIVE in pfile/spfile.

Password file also created with following command.

orapwd FILE=orapwblade1 PASSWORD=sys entries=30

Finally, I got the clue from v$pwfile_users dictionary view, This view lists users who have been granted SYSDBA and SYSOPER privileges, So following query must fetch something as I have already created password file. But no luck. Oops.
SQL> select * from v$pwfile_users;
no rows selected

I have double check “$ORACLE_HOME/dbs” directory and came to know there was naming convention mistake with password file(Case sensitivity with ORACLE SID).

[oracle@newyouth dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Feb 10 04:00 hc_BLADE1.dat
-rw-r--r--. 1 oracle oinstall 1018 Feb 10 03:58 initBLADE1.ora
-rw-r-----. 1 oracle oinstall 24 Feb 8 02:24 lkBLADE1
-rw-r-----. 1 oracle oinstall 5120 Feb 10 04:16 orapwblade1
-rw-r-----. 1 oracle oinstall 3584 Feb 10 04:00 spfileBLADE1.ora

So as a part of the solution to my scenario, I have dropped an existing password file and created new one by following command.

orapwd FILE=orapwBLADE1 PASSWORD=sys entries=30

Finally, I got access and v$pwfile_user dictionary view fetched SYS user entry.

[oracle@newyouth dbs]$ sqlplus sys/sys@blade1 as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 10 04:18:50 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

Cheers!!
Stay Tune. 🙂