30 December 2012

Step by Step to Disabling and droping old RAC instance Log group and thread

Step by Step to Disabling and droping old RAC instance Log group and thread

SQL> select GROUP#,THREAD# from v$log;
    GROUP#    THREAD#
---------- ----------
         1          1
         2          1
         3          1
         4          2
         5          2
         6          2
6 rows selected.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance PROD2 (thread 2) - cannot drop
ORA-00312: online log 4 thread 2: '/u02/UAT02/data/redo04b.dbf'
ORA-00312: online log 4 thread 2: '+DGUAT02/uat02/onlinelog/group_4.385.803355387'
ORA-00312: online log 4 thread 2: '+DGUAT02/uat02/onlinelog/group_4.386.803355389'
SQL> ALTER DATABASE DISABLE THREAD 2;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL>

Step by Step Migratation from NonASM to ASM database using RMAN

Step by Step Migratation from NonASM to ASM database using RMAN

Before stoping the database, we must know datafile,tempfile,logfile location .

SQL> select name from v$datafile;

NAME

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

/u02/PROD/data/system04.dbf

/u02/PROD/data/system05.dbf

/u02/PROD/data/ctxd01.dbf

/u02/PROD/data/owad01.dbf

SQL> select name from v$controlfile;

NAME

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

/u02/PROD/data/cntrl01.dbf

/u02/PROD/data/cntrl02.dbf

/u02/PROD/data/cntrl03.dbf

SQL>select GROUP#,MEMBER from v$logfile;

    GROUP#

----------

MEMBER

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

         3

/u02/PROD/data/redo03a.dbf

         3

/u02/PROD/data/redo03b.dbf

         2

/u02/PROD/data/redo02a.dbf

SQL> select FILE#,name from v$tempfile;

     FILE#

----------

NAME

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

         1

/u02/PROD/data/temp04.dbf

         2

/u02/PROD/data/temp03.dbf

         3

/u02/PROD/data/temp02.dbf

SQL> select name from v$asm_diskgroup where name='ASMPROD';

NAME

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

ASMPROD

1 rows selected.

SQL> exit

then Shutdown your database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

Database mounted.

SQL>exit

Take Full backup of your Database using RMAN:-

[oraprod@prod02 ~]$ rman target / catalog rman/muthu@RMAN

Recovery Manager: Release 11.1.0.7.0 - Production on Sat Dec 29 23:22:21 2012

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

connected to target database: PROD (DBID=3877050111, not open)

connected to recovery catalog database

RMAN> backup as compressed backupset incremental level 0 cumulative tag LEVEL0_BACKUP filesperset 4 format '/l01/backup/PROD/level0/%d_LVL0_%T_%s' database;

Starting backup at 29-DEC-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1983 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1981 device type=DISK

input datafile file number=00396 name=/u02/PROD/data/a_summ01.dbf

channel ORA_DISK_4: starting piece 1 at 29-DEC-12

channel ORA_DISK_5: starting compressed incremental level 0 datafile backup set

channel ORA_DISK_5: specifying datafile(s) in backup set

input datafile file number=00038 name=/u02/PROD/data/a_media04.dbf

input datafile file number=00085 name=/u02/PROD/data/sysaux28.dbf

channel ORA_DISK_5: backup set complete, elapsed time: 00:04:43

channel ORA_DISK_6: finished piece 1 at 30-DEC-12

piece handle=/l01/backup/PROD/level0/PROD_LVL0_20121230_19000 tag=LEVEL0_BACKUP comment=NONE

channel ORA_DISK_6: backup set complete, elapsed time: 00:04:42

Finished backup at 30-DEC-12

RMAN>exit

[oraprod@prod02 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

SQL> exit

[oraprod@prod02 ~]$

Chnage below parameter on initPROD.ora file

Old value :   *.control_files='/u02/PROD/data/cntrl01.dbf','/u02/PROD/data/cntrl02.dbf','/u02/PROD/data/cntrl03.dbf'

New Value :   *.control_files='+DBIMP02','+DBIMP02'

[oraprod@prod02 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012

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

connected to target database: PROD (not mounted)

RMAN> restore controlfile from '/u02/PROD/data/cntrl01.dbf';

Starting restore at 30-DEC-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1977 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+ASMPROD/prod/controlfile/current.256.803349501

output file name=+ASMPROD/prod/controlfile/current.257.803349503

Finished restore at 30-DEC-12

RMAN>exit

[oraprod@prod02 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012

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

Connected to an idle instance

SQL> alter database mount;

Database altered.

SQL> exit

[oraprod@prod02 ~]$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012

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

connected to target database: PROD (not mounted)

RMAN>backup as copy database format '+ASMPROD';

Starting backup at 30-DEC-12

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1977 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1975 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00030 name=/u02/PROD/data/sysaux03.dbf

channel ORA_DISK_2: starting datafile copy

input datafile file number=00034 name=/u02/PROD/data/undotbs5.dbf

output file name=+ASMPROD/prod/datafile/apps_ts_queues.364.803353979 tag=TAG20121230T004026 RECID=133 STAMP=803354005

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

output file name=+ASMPROD/prod/datafile/system.361.803353939 tag=TAG20121230T004026 RECID=132 STAMP=803354002

channel ORA_DISK_6: datafile copy complete, elapsed time: 00:01:07

Finished backup at 30-DEC-12

RMAN>switch database to copy;

datafile 1 switched to datafile copy "+ASMPROD/prod/datafile/system.351.803353795"

datafile 2 switched to datafile copy "+ASMPROD/prod/datafile/system.352.803353795"

datafile 3 switched to datafile copy "+ASMPROD/prod/datafile/system.353.803353795"

datafile 4 switched to datafile copy "+ASMPROD/prod/datafile/system.354.803353795"

datafile 405 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.287.803351559"

datafile 406 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.288.803351655"

datafile 407 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_seed.333.803353361"

RMAN> run {

set newname for tempfile 1 to '+ASMPROD';

set newname for tempfile 2 to '+ASMPROD';

set newname for tempfile 3 to '+ASMPROD';

set newname for tempfile 4 to '+ASMPROD';

set newname for tempfile 5 to '+ASMPROD';

set newname for tempfile 6 to '+ASMPROD';

set newname for tempfile 7 to '+ASMPROD';

set newname for tempfile 8 to '+ASMPROD';

set newname for tempfile 9 to '+ASMPROD';

switch tempfile all;

}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +ASMPROD in control file

renamed tempfile 8 to +ASMPROD in control file

renamed tempfile 9 to +ASMPROD in control file

RMAN>exit

[oraprod@prod02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:05:02 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> select name from v$datafile;

NAME

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

+ASMPROD/prod/datafile/system.351.803353795

+ASMPROD/prod/datafile/system.352.803353795

+ASMPROD/prod/datafile/system.353.803353795

SQL> select name from v$tempfile;

NAME

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

+ASMPROD

+ASMPROD

+ASMPROD

SQL> alter database open;

Database altered.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

GROUP# MEMBER                                   STATUS

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

         3 /u02/PROD/data/redo03a.dbf            INACTIVE

         3 /u02/PROD/data/redo03b.dbf            INACTIVE

         2 /u02/PROD/data/redo02a.dbf            INACTIVE

         2 /u02/PROD/data/redo02b.dbf            INACTIVE

         1 /u02/PROD/data/redo01a.dbf            CURRENT

         1 /u02/PROD/data/redo01b.dbf            CURRENT

         4 /u02/PROD/data/redo04a.dbf            UNUSED

         4 /u02/PROD/data/redo04b.dbf            UNUSED

         5 /u02/PROD/data/redo05a.dbf            UNUSED

         5 /u02/PROD/data/redo05b.dbf            UNUSED

         6 /u02/PROD/data/redo06a.dbf            UNUSED

    GROUP# MEMBER                                   STATUS

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

         6 /u02/PROD/data/redo06b.dbf            UNUSED

12 rows selected.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASMPROD' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/PROD/data/redo01a.dbf';

Database altered.

SQL> set line 200

SQL> col member for a40

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP# MEMBER                                   STATUS

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

         2 +ASMPROD/prod/onlinelog/group_2.387.8 CURRENT

           03355621

         1 +ASMPROD/prod/onlinelog/group_1.383.8 INACTIVE

           03355199

         1 +ASMPROD/prod/onlinelog/group_1.384.8 INACTIVE

           03355211

         2 +ASMPROD/prod/onlinelog/group_2.388.8 CURRENT

           03355625

    GROUP# MEMBER                                   STATUS

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

         3 +ASMPROD/prod/onlinelog/group_3.389.8 INACTIVE

           03355633

         3 +ASMPROD/prod/onlinelog/group_3.390.8 INACTIVE

           03355635

6 rows selected.

SQL>shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

[oraprod@prod02 ~]$ vi /u01/PROD/oracle/db/tech_st/11.1.0/db_1/dbs/initPROD.ora

Change below parameter on initPROD.ora file

Old value :   *.control_files='+DBIMP02','+DBIMP02'

New Value :   *.control_files='+ASMPROD/prod/controlfile/current.256.803349501','+ASMPROD/prod/controlfile/current.257.803349503'

[oraprod@prod02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:35:28 2012

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 6263357440 bytes

Fixed Size                  2171304 bytes

Variable Size            4244639320 bytes

Database Buffers         1996488704 bytes

Redo Buffers               20058112 bytes

Database mounted.

Database opened.

SQL>

29 December 2012

FAL[client, USER]: Error 16191 connecting to PRODNY for fetching gap sequence Error 1017 received logging on to the standby

SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1          2192           2212

SQL>

Stand by Alert log error:-

FAL[client, USER]: Error 16191 connecting to PRODNY for fetching gap sequence
Sat Dec 29 10:12:21 2012
Fetching gap sequence in thread 1, gap sequence 2192-2212
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
FAL[client, USER]: Error 16191 connecting to PRODNY for fetching gap sequence
Sat Dec 29 10:12:31 2012
Fetching gap sequence in thread 1, gap sequence 2192-2212
Error 1017 received logging on to the standby

Solution :-

We have to recover Archive log from catalog database.

[orprod@muthu]$ rman target / catalog rman/muth123@RMAN

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Dec 29 09:44:53 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=2808283451)
connected to recovery catalog database

RMAN> run {
set archivelog destination to '/oracle/PROD/db/apps_st/archive';
restore archivelog from logseq=2192  until logseq=2212  thread=1;
 }2> 3> 4>

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 29-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=512 device type=DISK

archived log for thread 1 with sequence 2211 is already on disk as file /oracle/PROD/db/apps_st/archive/1_2211_785817078.dbf
archived log for thread 1 with sequence 2212 is already on disk as file /oracle/PROD/db/apps_st/archive/1_2212_785817078.dbf
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2192
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31041
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31041 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2193
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31042
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31042 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2194
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31043
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31043 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2195
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31044
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31044 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2196
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31045
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31045 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2197
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31046
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31046 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2199
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31048
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31048 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2198
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31047
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31047 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2201
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31050
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31050 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2200
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31049
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31049 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2203
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31052
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31052 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2202
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31051
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121228_31051 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2206
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31069
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31069 tag=TAG20121229T000928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2205
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31068
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31068 tag=TAG20121229T000928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2204
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31067
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31067 tag=TAG20121229T000928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2208
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31074
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31074 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2207
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31073
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31073 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2210
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31076
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31076 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/oracle/PROD/db/apps_st/archive
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2209
channel ORA_DISK_1: reading from backup piece /backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31075
channel ORA_DISK_1: piece handle=/backup_pip/Prod_EBS/rman/PROD_ARCH_20121229_31075 tag=PROD ARCLOG BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 29-DEC-12

RMAN> exit

Login to stand by server stop and start the mrp services:-

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

Stand by alert log detail:-

Archived Log entry 350 added for thread 1 sequence 2211 rlc 785817078 ID 0xbc84a3b dest 2:
RFS[3]: Opened log for thread 1 sequence 2212 dbid -1486683845 branch 785817078
Sat Dec 29 10:23:01 2012
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2202_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2203_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2204_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2205_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2206_785817078.dbf
Media Recovery Waiting for thread 1 sequence 2207 (in transit)
Archived Log entry 351 added for thread 1 sequence 2212 rlc 785817078 ID 0xbc84a3b dest 2:
Sat Dec 29 10:23:10 2012
Archived Log entry 352 added for thread 1 sequence 2207 rlc 785817078 ID 0x0 dest 2:
Sat Dec 29 10:23:11 2012
RFS[2]: Opened log for thread 1 sequence 2215 dbid -1486683845 branch 785817078
RFS[1]: Opened log for thread 1 sequence 2216 dbid -1486683845 branch 785817078
Sat Dec 29 10:23:11 2012
RFS[3]: Opened log for thread 1 sequence 2217 dbid -1486683845 branch 785817078
Archived Log entry 353 added for thread 1 sequence 2216 rlc 785817078 ID 0xbc84a3b dest 2:
Archived Log entry 354 added for thread 1 sequence 2217 rlc 785817078 ID 0xbc84a3b dest 2:
Sat Dec 29 10:23:12 2012
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2207_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2208_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2209_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2210_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2211_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2212_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2213_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2214_785817078.dbf
Media Recovery Waiting for thread 1 sequence 2215 (in transit)
RFS[1]: Opened log for thread 1 sequence 2218 dbid -1486683845 branch 785817078
Sat Dec 29 10:23:17 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 24407
RFS[4]: No standby redo logfiles created for thread 1
RFS[4]: Opened log for thread 1 sequence 2219 dbid -1486683845 branch 785817078
Archived Log entry 355 added for thread 1 sequence 2218 rlc 785817078 ID 0xbc84a3b dest 2:
Sat Dec 29 10:23:25 2012
Archived Log entry 356 added for thread 1 sequence 2215 rlc 785817078 ID 0xbc84a3b dest 2:
Sat Dec 29 10:23:30 2012
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2215_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2216_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2217_785817078.dbf
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2218_785817078.dbf
Media Recovery Waiting for thread 1 sequence 2219 (in transit)
Sat Dec 29 10:26:04 2012
Archived Log entry 357 added for thread 1 sequence 2219 rlc 785817078 ID 0xbc84a3b dest 2:
RFS[4]: No standby redo logfiles created for thread 1
RFS[4]: Opened log for thread 1 sequence 2220 dbid -1486683845 branch 785817078
Sat Dec 29 10:26:06 2012
Media Recovery Log /oracle/PROD/db/apps_st/archive/1_2219_785817078.dbf
Media Recovery Waiting for thread 1 sequence 2220 (in transit)


verify the archive log gap:-

SQL> select * from v$archive_gap;

no rows selected

SQL>

Error 16191 PING[ARC2]: Heartbeat failed to connect to standby ORA-16191 ORA-01017 ORA-01031

Stand by Server alert log error:-


FAL[client, USER]: Error 16191 connecting to PRODNY for fetching gap sequence
Sat Dec 29 10:12:00 2012
Fetching gap sequence in thread 1, gap sequence 2192-2212
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
   
     

Primary Server Alert log error:-


PING[ARC2]: Heartbeat failed to connect to standby 'PRODVA'. Error is 16191.
Error 1017 received logging on to the standby
      returning error ORA-16191
PING[ARC2]: Heartbeat failed to connect to standby 'PRODVA'. Error is 16191.

Primary Server Trace file detail;-


OCISessionBegin failed -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'

*** 2012-12-29 08:00:10.260
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
*** 2012-12-29 08:00:10.261 4265 krsh.c
Error 1031 received logging on to the standby
*** 2012-12-29 08:00:10.261 912 krsu.c
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'PRODVA'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'PRODVA'
*** 2012-12-29 08:00:10.261 4265 krsh.c
Error 1031 for archive log file 1 to 'PRODVA'
*** 2012-12-29 08:00:10.261 2864 krsi.c
krsi_dst_fail: dest:2 err:1031 force:0 blast:1
ORA-01031: insufficient privileges

Cause:-


Password file got correpted or Password changes in Primary and standby servers or we have to check sec_case_sensitive_logon parameter on primary and standby.

SQL> show parameter sec_case_sensitive_logon;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL>

Solution:-


We have to recreate the passwd file or copy Primary server to Standby server.

Example:-


In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=muthu123 entries=10 ignorecase=y

In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.

orapwd file=$ORACLE_HOME/dbs/orapwPROD password=muthu123 entries=10

22 December 2012

RMAN-00571 RMAN-00569 RMAN-03002 RMAN-05501 RMAN-05597:Database started with server parameter file and PFILE clause used in command


Recently We have done the RMAN clone DEV from PROD that time We are faced  below Issue

 Error:-

channel ch1: CommVault Systems for Oracle: Version 9.0.0(BUILD84)
executing command: SET until clause
Starting Duplicate Db at Dec 21 2012 10:36:11
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2012 10:36:11
RMAN-05501: aborting duplication of target database
RMAN-05597: Database started with server parameter file and PFILE clause used in command
Recovery Manager completed

Cause:-

Database mounted with spfile

Solution :-

We have to stop the Database then use startup pfile=<LOcation of Pfile> mount then your problem will resolved


 
Example:-

SQL> startup pfile=/oracle/product/db/tech_st/11.2.0/dba/initMUTH.ora mount

Then Provide dublicate database command


Comments are welcome!!!!!!!!!!