ORA-16072: a minimum of one standby database destination is required; ORA-03113: end-of-file on communication channel

Following error while trying to open the database after RMAN restore and recover database has finished: 

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

Process ID: 3689

Session ID: 5644 Serial number: 5

On the alert.log file

Mon May 16 16:29:17 2016

alter database open

Mon May 16 16:29:18 2016

LGWR: STARTING ARCH PROCESSES

Mon May 16 16:29:18 2016

ARC0 started with pid=21, OS id=3196 

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

LGWR: Primary database is in MAXIMUM AVAILABILITY mode

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

LGWR: Minimum of 1 LGWR standby database required

Errors in file /soft/app/oracle/diag/rdbms/remitdb/remitdb/trace/remitdb_lgwr_3144.trc:

ORA-16072: a minimum of one standby database destination is required

Mon May 16 16:29:19 2016

ARC1 started with pid=22, OS id=3198 

LGWR (ospid: 3144): terminating the instance due to error 16072

Mon May 16 16:29:20 2016

System state dump requested by (instance=1, osid=3144 (LGWR)), summary=[abnormal instance termination].

System State dumped to trace file /soft/app/oracle/diag/rdbms/remitdb/remitdb/trace/remitdb_diag_3134_20160516162920.trc

Dumping diagnostic data in directory=[cdmp_20160516162920], requested by (instance=1, osid=3144 (LGWR)), summary=[abnormal instance termination].

Instance terminated by LGWR, pid = 3144

Mon May 16 16:31:18 2016

Solution

Step-1: Startup database in mount mode

SQL> startup mount;

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size                  2267792 bytes

Variable Size            5804918128 bytes

Database Buffers         1660944384 bytes

Redo Buffers               14495744 bytes

Database mounted.

SQL>  select open_mode, DATABASE_ROLE, guard_status, SWITCHOVER_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    GUARD_S SWITCHOVER_STATUS

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

MOUNTED              PRIMARY          NONE    NOT ALLOWED

Step-2: Execute following command

SQL> alter database set standby database to maximize performance;

Database altered.

SQL>

Step-3: Shutdown database

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Step-4: startup database in mount mode

SQL> startup mount;

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size                  2267792 bytes

Variable Size            5804918128 bytes

Database Buffers         1660944384 bytes

Redo Buffers               14495744 bytes

Database mounted.

Step-5: startup database in mount mode

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

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

READ WRITE

ORA-39700: database must be opened with UPGRADE option

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 3887
Session ID: 5644 Serial number: 5


Solution
After restore database another server appear this error, because on older server database version was 11.2.0.1.0 and on new server database version is 11.2.0.4.0, therefore we need:
1) open with upgrade option
2) upgrade database – it means run script $ORACLE_HOME/rdbms/admin/catupgrd.sql
3) shutdown clearly and startup
4) run script $ORACLE_HOME/rdbms/admin/utlrp.sql – for recompile all invalid PL/SQL packages

sqlplus / as sysdba
SQL> startup upgrade;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
SQL> shutdown immediate;
SQL> startup;
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql


Test Server Implement Log:
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 7482626048 bytes
Fixed Size                  2267792 bytes
Variable Size            6023021936 bytes
Database Buffers         1442840576 bytes
Redo Buffers               14495744 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/catupgrd.sql
.................
................running....
...............
DOC>   Next restart for normal operation, and then run utlrp.sql to
DOC>   recompile any invalid application objects.
DOC>
DOC>   If the source database had an older time zone version prior to
DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade
DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC>   with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 17:58:53 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 7482626048 bytes
Fixed Size                  2267792 bytes
Variable Size            6023021936 bytes
Database Buffers         1442840576 bytes
Redo Buffers               14495744 bytes
Database mounted.
Database opened.
SQL> @/soft/app/oracle/product/11.2.0.4.0/dbhome_1//rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2016-05-16 17:59:33

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
...............
................
.....................
Function dropped.

Warning: XDB now invalid, could not find xdbconfig
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110:
data file 201: '/u01/app/oracle/oradata/remitdb/temp01.dbf'
ORA-01403: no data
found

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exit
[oracle@bkuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 16 18:00:09 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL>
SQL>

ORA-00392: log 3 of thread 1 is being cleared, operation not allowed

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 3 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03a.log'
ORA-00312: online log 3 thread 1: '/data/bkuptest/redo03b.log'


Solution
SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CLEARING
         2 CLEARING
         5 CLEARING
         4 CLEARING
         3 CLEARING_CURRENT

SQL>
SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> alter database clear logfile group 4;

Database altered.

SQL> alter database clear logfile group 5;

Database altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 UNUSED
         5 UNUSED
         4 UNUSED
         3 CURRENT

SQL>
SQL> alter database open resetlogs;
Database altered.

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/bkuptest/system01.dbf'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 13:57:21
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 961622 and starting SCN of 649440238 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961621 and starting SCN of 649432402 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961620 and starting SCN of 649423457 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961619 and starting SCN of 649413447 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 961618 and starting SCN of 649405456 found to restore

RMAN>


Solution

Copy following  specified Archivelog (sequence 961618,961619,961620,961621,961622) to following Specified directory and recover database.

Archivelog directory for my case : /fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/

After copying archivelog to specified directory, run below recover database command from RMAN.

RMAN> RECOVER DATABASE;
Starting recover at 16-MAY-16
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

starting media recovery

archived log for thread 1 with sequence 961619 is already on disk as file 

/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc
archived log for thread 1 with sequence 961620 is already on disk as file 

/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc
archived log for thread 1 with sequence 961621 is already on disk as file 

/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc
archived log for thread 1 with sequence 961622 is already on disk as file 

/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961619_cmj84crz_.arc thread=1 

sequence=961619
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961620_cmj8kj68_.arc thread=1 

sequence=961620
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961621_cmj8xllx_.arc thread=1 

sequence=961621
archived log file name=/fra/fast_recovery_area/REMITDB/archivelog/2016_05_15/o1_mf_1_961622_cmj93x7z_.arc thread=1 

sequence=961622
unable to find archived log
archived log thread=1 sequence=961623
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/16/2016 15:32:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 961623 and starting SCN of 649445097

Note: this error can be ignore as RMAN has applied all the available archivelog files and is now trying to apply a non-existent archivelog file.

Renaming or Moving Datafiles of Oracle 11gR2 (While database in online)

This document will detail the steps to Rename OR Moving datafile using Oracle 11g R2.

The datafiles for the CREDITTESTDATA tablespace are following. We will rename following datafiles name.

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

FILE_NAME
--------------------------------------------------------------------------------
/area_db/monthenddata01.dbf
/area_db/monthenddata02.dbf
/area_db/monthenddata03.dbf
/area_db/monthenddata04.dbf
/area_db/monthenddata05.dbf
/area_db/monthenddata06.dbf
/areadb2/monthenddata07.dbf
/area_db/monthenddata08.dbf
/area_db/monthenddata09.dbf
/areadb2/monthenddata10.dbf

10 rows selected.


Step-1: First take the tablespace offline in which the datafiles to rename or move.  

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue May 10 11:54:43 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> alter tablespace credittestdata offline;

Tablespace altered.

Step-2: Rename/move the file using operating system commands.

$ mv /area_db/monthenddata01.dbf /area_db/credittestdata01.dbf
$ mv /area_db/monthenddata02.dbf /area_db/credittestdata02.dbf
$ mv /area_db/monthenddata03.dbf /area_db/credittestdata03.dbf
$ mv /area_db/monthenddata04.dbf /area_db/credittestdata04.dbf
$ mv /area_db/monthenddata05.dbf /area_db/credittestdata05.dbf
$ mv /area_db/monthenddata06.dbf /area_db/credittestdata06.dbf
$ mv /areadb2/monthenddata07.dbf /areadb2/credittestdata07.dbf
$ mv /area_db/monthenddata08.dbf /area_db/credittestdata08.dbf
$ mv /area_db/monthenddata09.dbf /area_db/credittestdata09.dbf
$ mv /areadb2/monthenddata10.dbf /areadb2/credittestdata10.dbf

Step-3: Rename datafiles using command "ALTER DATABASE RENAME FILE".

SQL> alter database rename file '/area_db/monthenddata01.dbf' to '/area_db/credittestdata01.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata02.dbf' to '/area_db/credittestdata02.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata03.dbf' to '/area_db/credittestdata03.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata04.dbf' to '/area_db/credittestdata04.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata05.dbf' to '/area_db/credittestdata05.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata06.dbf' to '/area_db/credittestdata06.dbf';

Database altered.

SQL> alter database rename file '/areadb2/monthenddata07.dbf' to '/areadb2/credittestdata07.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata08.dbf' to '/area_db/credittestdata08.dbf';

Database altered.

SQL> alter database rename file '/area_db/monthenddata09.dbf' to '/area_db/credittestdata09.dbf';

Database altered.

SQL> alter database rename file '/areadb2/monthenddata10.dbf' to '/areadb2/credittestdata10.dbf';

Database altered.

SQL>


Step-4: Bring the tablespace online.

SQL> alter tablespace credittestdata online;

Tablespace altered.


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

FILE_NAME
--------------------------------------------------------------------------------
/area_db/credittestdata01.dbf
/area_db/credittestdata02.dbf
/area_db/credittestdata03.dbf
/area_db/credittestdata04.dbf
/area_db/credittestdata05.dbf
/area_db/credittestdata06.dbf
/areadb2/credittestdata07.dbf
/area_db/credittestdata08.dbf
/area_db/credittestdata09.dbf
/areadb2/credittestdata10.dbf

10 rows selected.