About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, July 27, 2015

How to restore a 10.2 Rman backup on a 11.2 Oracle Database host

Yesterday I got a requirement to restore one of my 10g database Rman backup , now all our 10g database servers were decommissed. So I have to restore it on a 11g host . PFB steps to restore and upgrade a 10.2 version database to 11.2 version :-

1. Copy the 10g Backup to 11G server

 2. Create a Pfile for to start the instance

*.audit_file_dest='/opt/oracle/admin/amit/adump'
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'    #######s set compatible for 11.2 version######
*.control_files='+DATA_DG/amit/controlfile/control01.ctl','+DATA_DG/amit/controlfile/control02.ctl'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_create_online_log_dest_1='+DATA_DG'
*.db_domain='domain.com'
*.db_name='amit'#Reset to original value by RMAN
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=7000M
*.diagnostic_dest='/opt/oracle'
*.open_cursors=300
*.pga_aggregate_target=2G
*.processes=550
*.sga_target=5G
*.undo_tablespace='UNDO'  ##### as the backup is of 10g database , we have to keep it UNDO#####

3. Start the instance with this pfile

export ORACLE_SID=amit
sqlplus "/ as sysdba"
startup nomount pfile='/ora_backup/test/amit/initamit.ora'
create spfile from pfile='/ora_backup/test/amit/initamit.ora';
shu immediate
startup nomount

 SQL> ORACLE instance started.

 Total System Global Area 5344731136 bytes
Fixed Size                  2262656 bytes
Variable Size            1040189824 bytes
Database Buffers         4278190080 bytes
Redo Buffers               24088576 bytes

4. Login to RMAN prompt and restore the controlfile

export ORACLE_SID=amit
rman log=/ora_backup/test/amit/restore.log << EOF
connect target /
restore controlfile from '/ora_backup/test/amit/ctl_c-1234324679-20140701-00';

 Starting restore at 2015-07-24 06:17:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK

 channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA_DG/amit/controlfile/control01.ctl
output file name=+DATA_DG/amit/controlfile/control02.ctl
Finished restore at 2015-07-24 06:17:25

5. Mount the database with this control file

alter database mount;

 database mounted
released channel: ORA_DISK_1

6. Catalog the 10g backup pieces

catalog start with '/ora_backup/test/amit/';
Starting implicit crosscheck backup at 2015-07-24 06:17:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=523 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=559 device type=DISK
allocated channel: ORA_DISK_4
.
.
.

 File Name: /ora_backup/test/amit/hot_amit_30309_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30310_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30311_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30312_1_20140701

 7. Restore and recover the database using sequence number+1

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 2 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 3 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 4 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 5 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 6 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 7 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 8 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 9 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 10 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 11 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 12 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 13 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 14 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 15 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 16 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 17 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 18 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 19 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 20 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 21 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 22 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 23 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 24 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 25 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 26 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 27 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 28 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 29 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 30 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 31 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 32 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 33 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 34 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 35 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 36 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 37 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 38 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 39 TO '+DATA_DG';
restore database until sequence 10123;
switch datafile all;
recover database until sequence 10123;
}

 allocated channel: c1
channel c1: SID=505 device type=DISK

 allocated channel: c2
channel c2: SID=523 device type=DISK

 allocated channel: c3
channel c3: SID=559 device type=DISK

 allocated channel: c4
channel c4: SID=577 device type=DISK

 allocated channel: c5
channel c5: SID=613 device type=DISK

 executing command: SET NEWNAME

 executing command: SET NEWNAME

 executing command: SET NEWNAME
.
.
.
Starting restore at 2015-07-24 06:18:01

 channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA_DG
channel c1: restoring datafile 00006 to +DATA_DG
channel c1: restoring datafile 00021 to +DATA_DG
channel c1: restoring datafile 00038 to +DATA_DG
channel c1: reading from backup piece /ora_backup/test/amit/hot_amit_30307_1_20140701
channel c2: starting datafile backup set restore
.
.
.Starting recover at 2015-07-24 07:08:30

 starting media recovery

 channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=10123
.
.
.
media recovery complete, elapsed time: 00:00:08
Finished recover at 2015-07-24 07:11:21
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5

8. Rename the online log files according to the target

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
alter database rename file '/u02/oracle/amit/redo.g04.m01.log' to '+DATA_DG/amit/ONLINELOG/group41.log';
alter database rename file '/u03/oracle/amit/redo.g04.m02.log' to '+DATA_DG/amit/ONLINELOG/group42.log';
alter database rename file '/u02/oracle/amit/redo.g05.m01.log' to '+DATA_DG/amit/ONLINELOG/group51.log';
alter database rename file '/u03/oracle/amit/redo.g05.m02.log' to '+DATA_DG/amit/ONLINELOG/group52.log';

9 . open the database with upgrade option

ALTER DATABASE OPEN RESETLOGS UPGRADE;

10. Create Undo tablespace for 11g database and change the default to the new one

create undo tablespace UNDOTBS1 datafile '+DATA_DG' size 1g;
alter system set undo_tablespace=UNDOTBS1;

11. Create New temporary tablespace and change the default one

create temporary tablespace TEMP1 tempfile '+DATA_DG' size 1g;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;

12. Now run the Catupgrd.sql script, it will error out as the preupgrade tasks were not applied on 10g backup

shu immediate
startup upgrade

 SQL> @?/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint () violated
DOC>              possibly in conjunction with
DOC>   ORA-06512: at "", line NN
DOC>
DOC>   These errors will automatically be suppressed by the Database Upgrade
DOC>   Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the user running this script is not SYS.  Disconnect
DOC>    and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

 no rows selected

 DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the database server version is not correct for this script.
DOC>    Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC>    a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#

 no rows selected

 DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

 no rows selected

 DOC>#######################################################################
DOC>#######################################################################
DOC>     The following statement will cause an "ORA-01722: invalid number"
DOC>     error if the Oracle Database Vault option is TRUE.  Upgrades cannot
DOC>     be run with the Oracle Database Vault option set to TRUE since
DOC>     AS SYSDBA connections are restricted.
DOC>
DOC>     Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC>     the server without the Database Vault option, and restart the server
DOC>     using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

 no rows selected

 DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if Database Vault is installed in the database but the Oracle
DOC>   Label Security option is FALSE.  To successfully upgrade Oracle
DOC>   Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC>   Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC>   relink the server with the OLS option (but without the Oracle Database
DOC>   Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

 no rows selected

 DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if bootstrap migration is in progress and logminer clients
DOC>   require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC>   Run utlmmig.sql
DOC>   then (if needed)
DOC>   restart the database using UPGRADE and
DOC>   rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

 no rows selected

 DOC>#######################################################################
DOC>#######################################################################
DOC>   The following error is generated if the pre-upgrade tool has not been
DOC>   run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC>   SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC>                       *
DOC>      ERROR at line 1:
DOC>      ORA-01722: invalid number
DOC>
DOC>     o Action:
DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC>       Revert to the original oracle home and start the database.
DOC>       Run pre-upgrade tool against the database.
DOC>       Review and take appropriate actions based on the pre-upgrade
DOC>       output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

 Session altered.
Table created.
Table altered.

 SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

To remove this error , Please see below link
http://amit7oracledba.blogspot.com/2015/06/ora-01722-during-upgrade-102-to-112_30.html

we have to disable the data vault option and update the timezone detail of current Database server to this Restored10g database.

13. Once both Errors removed , we will again run the Catupgrd.sql to upgrade the database , PFB output :-

...
.
.

 200        -- Output final actions time
201        IF display_xml THEN
202           DBMS_OUTPUT.PUT_LINE (
203                           '" upgradeTime="' || substr(time_result,5,8) ||
204                           '">');
205        ELSE
206           DBMS_OUTPUT.PUT_LINE('.' ||
207                                LPAD(' ',46) || ' ' ||
208                                LPAD(' ',15) ||
209                                LPAD(substr(time_result,5,8),10));
210        END IF;
211     END IF; -- actions_end_time is not null
212
213     IF up_end_time IS NOT NULL THEN
214        elapsed_time := up_end_time - up_start_time;
215        time_result := to_char(elapsed_time);
216        IF display_xml THEN
217           DBMS_OUTPUT.PUT_LINE('');
219        ELSE
220           DBMS_OUTPUT.PUT_LINE('Total Upgrade Time: ' ||
221                    substr(time_result, 5,8));
222        END IF;
223     ELSE
224        IF display_xml THEN
225              DBMS_OUTPUT.PUT_LINE('');
226        ELSE
227           DBMS_OUTPUT.PUT_LINE('Upgrade Incomplete');
228        END IF;
229     END IF;
230        IF display_xml THEN
231         DBMS_OUTPUT.PUT_LINE('
');232           DBMS_OUTPUT.PUT_LINE('
');233        END IF;
234  END;
235  /
.
Oracle Database 11.2 Post-Upgrade Status Tool           07-27-2015 02:26:47
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:24:14
Final Actions
.                                                                00:00:28
Total Upgrade Time: 00:24:47

 PL/SQL procedure successfully completed.

 SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;

 Commit complete.

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

We can see upgrade completed successfully in approx 24 mins and version is now 11.2.0.4.0

14. After Upgrade completed successfully, Oracle Instance is down, Start up the instance and check for any invalid objects :-

SQL> ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2262656 bytes
Variable Size            1040189824 bytes
Database Buffers         4278190080 bytes
Redo Buffers               24088576 bytes
Database mounted.
Database opened.
SQL>
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-07-27 02:27:12

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.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-07-27 02:27:56

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
AMIT      READ WRITE

Restore of a 10G backup to a 11g host completed successfully.

I hope this article helped you.

Thanks
Amit Rath

Posted by at No comments:
Labels: , , ,

Friday, July 24, 2015

Flashback Technology : Flashback to a restore point having standby database also enabled

Yesterday we were in a scenario where we have to do some changes in our production database which if needed can be rollbacked.

 We planned to go with a restore point option so that if change not needed by Development team, we can move back to before change time.

 This was a big database and it has standby also enabled with it. So if we do a flashback on primary then incarnation of primary will differ from standby and recovery will be stopped.

 Below are the steps which we used to handle both primary and standby in case of a Flashback database in Primary :-

 1. Backup Primary database

 2. Note the Current SCN of primary :-

SQL> select current_scn from v$database;

 CURRENT_SCN
-----------
   15883324

3. Create a restore point 

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

4. Do your changes in database 

Now you want to do the flashback in your primary Database as the changes which you did, development team does not require those. PFB steps :-

SQL> startup force mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size            1124074376 bytes
Database Buffers         3137339392 bytes
Redo Buffers               12107776 bytes
Database mounted.
SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> alter database open RESETLOGS;

Database altered.

Now when you check your standby database , MRP is stopped and it's showing that incarnation is different from primary database.

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Move your standby database to that SCN which was before doing a flashback.

 Standby Database :-

SQL> flashback database to scn 15883324;

 Flashback complete.

Now start the MRP and check the alert log, it will show MRP started successfully

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (dr3fia)
Fri Jul 24 06:10:35 2015
MRP0 started with pid=49, OS id=14805
MRP0: Background Managed Standby Recovery process started (dr3fia)
 started logmerger process
Fri Jul 24 06:10:40 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 12 slaves
Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +Amit_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

I hope this article helped you.

Thanks
Amit Rath

Posted by at No comments:
Labels: , ,

ORA-16700: the standby database has diverged from the primary database

Yesterday I faced below error in my standby database :-

 Problem :-

DGMGRL> show database amit_dr

 Database - amit_dr

   Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      148.00 KByte/s
  Real Time Query: OFF
  Instance(s):
   amit_dr

   Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

 Database Status:
ERROR

Cause :- 

This happened because when due to an incomplete recovery in primary database, it has to be opened using resetlogs option and incarnation of primary and standby becomes different due to resetlogs option.

we will get below error in our standby database alert logfile

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Fri Jul 24 05:58:48 2015
Archived Log entry 113 added for thread 1 sequence 1 ID 0xf0d327b6 dest 1:
Recovered data files to a consistent state at change 15883769
Fri Jul 24 05:58:48 2015
 started logmerger process
Fri Jul 24 05:58:48 2015
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 15883769) is orphaned on incarnation#=5
MRP0: Detected orphaned datafiles!

Solution :-

 We do not have to rebuild our standby, just move standby database using flashback to a previous SCN value when the incarnation of primary and standby was same. Start the MRP manually or bounce the configuration in DG broker.

SQL> flashback database to scn 15883324;

Flashback complete.

Details from alert log on standby :-

flashback database to scn 15883324
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fri Jul 24 06:06:06 2015
Setting recovery target incarnation to 5
 started logmerger process
Parallel Media Recovery started with 12 slaves
Fri Jul 24 06:06:06 2015
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Incomplete Recovery applied until change 15883325 time 07/24/2015 05:52:43
Flashback Media Recovery Complete
Setting recovery target incarnation to 6
Completed: flashback database to scn 15883324

Attempt to start background Managed Standby Recovery process (dr3fia)
Fri Jul 24 06:10:35 2015
MRP0 started with pid=49, OS id=14805
MRP0: Background Managed Standby Recovery process started (dr3fia)
 started logmerger process
Fri Jul 24 06:10:40 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 12 slaves
Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

DGMGRL> show configuration

Configuration - amit_dr

  Protection Mode: MaxPerformance
  Databases:
    amit  - Primary database
    amit_dr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this article helped you.

Thanks
Amit Rath

Posted by at 2 comments:
Labels: , ,

Saturday, July 11, 2015

ORA-25191: cannot reference overflow table of an index-organized table

Yesterday I faced below issue when I was trying to grant some privileges to a table :-

grant select,insert,update,delete on AMIT.SYS_IOT_OVER_14456 TO TEST;
                                             *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Issue :-

This issue comes when we deal with Index Organized tables(IOT).

Solution :-

Issue this statement against the Parent Index Organized table containing the specified overflow table.

SQL> select IOT_NAME from dba_tables where owner='AMIT' and table_name='SYS_IOT_OVER_14456';

IOT_NAME
------------------------------
AMIT_TABLE

SQL> grant select,insert,update,delete on AMIT.AMIT_TABLE to TEST;

Grant succeeded.

Any operation related to Index Oraganized table has to be done by above method else it will give ORA-25191.

I hope this article helped you.

Thanks
Amit Rath
Posted by at 3 comments:
Labels: , ,
Subscribe to: Posts (Atom)