Oracle Database: Recovery

Showing posts with label Recovery. Show all posts

Wednesday, January 11, 2017

MRP0: Background Media Recovery terminated with error 1111

Today while troubleshooting a DR database sync scenario issue, I got below error in alert log file :-

MRP0: Background Media Recovery terminated with error 1111
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit1_pr00_1890.trc:
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'
ORA-01157: cannot identify/lock data file 34 - see DBWR trace file
ORA-01111: name for data file 34 is unknown - rename to correct file
ORA-01110: data file 34: '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034'

Cause :-

Above issue can occur in DR database in below scenarios :-

1. Space issue in DR database and a datafile is added in primary database.
2. Standby file management is set to manual
3. DB_create_file_dest or DB_file_name_convert parameter is not set in Standby database

Troubleshoot :-

When I crosscheck the primary database and standby database sync details , I saw Redo Apply was stopped.

I crosschecked the alert log files for standby database and got above mentioned error in alert logfile.

Checked the datafile details in DR database :-

File_id name
34 /opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

Checked the Primary alert log file and got to know that a datafile addition activity happened in primary database.

Crosschecked all the above mentioned parameters and got to know that space exhausted in BCP database due to that Oracle created an Unknown file in ORACLE_HOME location due to which Redo Apply stopped.

Solution :-

Checked the added file detail in Production database :-

File_id name
34 +DATA/amit/datafile/data.321.931222251

Login to Standby database server :-

1. As space was exhausted on Diskgroup level, added space by logging in to ASM instance
2. Login to DR database instance :-

a. Change the standby_file_management parameter to Manual

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
SQL> alter system set standby_file_management=MANUAL sid='*';

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL>
SQL> select name from v$datafile where file#=34;

NAME
--------------------------------------------------------------------------------
/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00034' as '+DATA' size 10g;

Database altered.

After creation of the datafile in DR database , check the file detail in DR database :-

File_id name
34 +DATA/amit_dr/datafile/data.278.293456881

Check the redo apply status, if not started , start it manually using dgmgrl.

I hope this article helped you.

Thanks
Amit Rath

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

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

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

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

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

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

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

Friday, December 19, 2014

How to create a Standby Database in RAC using RMAN Duplicate command

We can also use RMAN duplicate command to create a STANDBY RAC database from Production Standalone/RAC database. We can either use Backups of Production Database or we can create from Active Database also.

PFB steps to create a Standby Database in RAC using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY(RAC)

11. Reload listener after adding these entries

Lsnrctl reload

12. Add Standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;

13. Copy the passw0rd file from Primary and copy to Standby

14. Copy the init.ora file from primary and change below parameters :-

CREATE PFILE='/initTEST.ora' from spfile;

scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY1
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY1.ora

Steps to be done in STANDBY Side :-

15 . Update Pfile for below parameters for Standby server

*.audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
*.diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'

16. Start the STANDBY database with above pfile

export ORACLE_SID=STBY1
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY1.ora'
ORACLE instance started.

Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 264243040 bytes
Database Buffers 683671552 bytes
Redo Buffers 11358208 bytes

17. Connect to RMAN and use RMAN duplicate command to create STANDBY

rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL

run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
from active database
DORECOVER
spFILE
SET db_unique_name='STBY' COMMENT 'Is STANDBY'
SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
set control_files='+DATA_0220/STBY/controlfile/control01.ctl','+FRA_0220/STBY/controlfile/control02.ctl'
set db_create_file_dest='+DATA_0220/'
set db_create_online_log_dest_1='+DATA_0220/'
set diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
set audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
set db_recovery_file_dest='+FRA_0220/'
set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','+DATA_0220/STBY/'
set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','+DATA_0220/STBY/'
set FAL_SERVER='TEST'
set cluster_database='false'
set instance_number='1'
set LOG_ARCHIVE_DEST_1='location=+FRA_0220/STBY/';

}

Above command will create the STANDBY database

18. Create pfile from spfile

create pfile='/ora_backup/duplicate_test/STBY/initTEST.ora' from spfile;

19. Add the below parametErs for RAC database

STBY1.log_archive_format='%t_%s_%r.dbf'
STBY2.log_archive_format='%t_%s_%r.dbf'
STBY2.thread=2
STBY1.thread=1
STBY1.undo_tablespace='UNDOTBS1'
STBY2.undo_tablespace='UNDOTBS2'
STBY2.instance_number=2
STBY1.instance_number=1
STBY1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = first_node)(PORT = 9001))'
STBY2. local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = second_node)(PORT = 9001))'
*.cluster_database=TRUE

20. Start the instance with above pfile

==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 12:51:57 2014

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

Enter user-name: /as sysdba

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

SQL> startup force pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TEST READ ONLY

SQL> create spfile='+DATA_0220/STBY/spfileSTBY.ora' from pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.

SQL> show parameter cluster

NAME TYPE VALUE

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

cluster_database boolean TRUE

cluster_database_instances integer 2

cluster_interconnects string

SQL>

SQL> show parameter listenner

SQL> show parameter listener

NAME TYPE VALUE

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

listener_networks string

local_listener string (ADDRESS = (PROTOCOL = TCP)(HO

ST = first_node)(PORT = 9001))

remote_listener string scan_ip:9010

21. Login to second instance

Export ORACLE_SID=STBY2
==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 13:01:32 2014

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 679478240 bytes
Database Buffers 369098752 bytes
Redo Buffers 9748480 bytes
Database mounted.
Database opened.

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

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TEST READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,LOGINS from gv$instance;

INST_ID INSTANCE_NAME HOST_NAME VERSION LOGINS
---------- ---------------- ------------------------- ----------------- ----------
2 STBY2 wuprd02a0220 11.2.0.4.0 ALLOWED
1 STBY1 wuprd01a0220 11.2.0.4.0 ALLOWED

SQL> show parameter listener

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS = (PROTOCOL = TCP)(HO
ST = second_node)(PORT = 9001))
remote_listener string scan_ip:9010
SQL>

Check all the parameters in both instances are same or not

22. Start the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

23. Add the details of Standby database in OCR

Srvctl add database –d STBY –o $ORACLE_HOME -p

Srvctl add instance –d STBY –i STBY1-n firstnode

Srvctl add instance –d STBY –I STBY2 –n secondnode

Srvctl status database –d STBY

24. Create the broker configuration details ( for both Primary and Standby):-

SQL> alter system set dg_broker_start=FALSE;

System altered.

SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';

SQL> alter system set dg_broker_config_file2='/opt/oracle/product/database/11.2.0.4/dbs/dr2STBY.dat';

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

DGMGRL> connect /
Connected.
DGMGRL> create configuration DR as primary database is TEST connect identifier is TEST;
Configuration "dr" created with primary database "TEST"

DGMGRL> add database STBY as connect identifier is STBY maintained as physical;
Database "STBY" added
DGMGRL> show configuration

Configuration - dr

Protection Mode: MaxPerformance
Databases:
TEST - Primary database
STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.

Check if every thing is fine by checking Standby Database details

DGMGRL> show database STBY

Database - STBY

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
STBY1 (apply instance)
STBY2

Database Status:
SUCCESS

DGMGRL> exit

Standby Creation in RAC using RMAN duplicate command completed.

Standby creation for standalone using RMAN duplicate command

I hope this article helped you

Regards,
Amit Rath

Wednesday, December 17, 2014

RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

Yesterday I was doing a duplicate of a RAC database using RMAN Duplicate command and I got below error :-

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "RASTAND" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 SIZE 256 M ,
GROUP 2 SIZE 256 M ,
GROUP 10 SIZE 256 M
DATAFILE
'+DATA_0110/rastand/datafile/system.327.866524759'
CHARACTER SET AL32UTF8

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/17/2014 05:20:32
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

Problem :-

It seems while trying to create control file for a RAC database it failed by giving above error.

Reason :-

When we do a RMAN Dupliacate for a RAC Database , while creating control file for Auxiliary instance , it needs the database in Exclusive mode to create controlfile

Solution :-

In duplicate command mention Cluster_Database parameter to FALSE and it will resolve the issue. PFB :-

run

{

allocate auxiliary channel ch1 type disk;

duplicate target database to 'RASTAND'

backup location '/ora_backup/duplicate_test/RASTAND/backup/2014_12_16'

spfile

set cluster_database='FALSE'

set instance_number='1'

set db_file_name_convert='/ora_backup/TEST1/restore_test/AMIT/DATA','+DATA_0110';

}

We can also change the Cluster_database parameter in pfile also with which we start our auxiliary instance then command to do the Duplicate will be

run

{

allocate auxiliary channel ch1 type disk;

duplicate target database to 'RASTAND'

backup location '/ora_backup/duplicate_test/RASTAND/backup/2014_12_16';

}

I hope this article helped you.

Regards,

Amit Rath

How to Create a STANDBY database using RMAN Duplicate command in Oracle

We can also use RMAN duplicate command to create a STANDBY database from Production database. We can either use Backups of Production Database or we can create from Active Database also.

PFB steps to create a standby Database using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY

Changes to be done on Primary side :-

1. Database has to be archive log mode

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

2. Change primary database to Force Logging mode :-

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3. Initialization parameters in Primary database :-

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TEST
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string TEST

4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of Standby database has to be different from primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';

System altered.

5. Set suitable remote archivelog destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database

6. Set parameters related to LOG_ARCHIVE and password file parameters :-

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-

SQL> ALTER SYSTEM SET FAL_SERVER=STBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE; -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 247465824 bytes
Database Buffers 700448768 bytes
Redo Buffers 11358208 bytes
Database mounted.
Database opened.
SQL>

9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do tnsping.

###################ORACLE_DATA_GAURD############################

TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)

STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY)
)
)

TEST_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST_DGMGRL)
)
)
STBY_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 9001))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STBY_DGMGRL)
)
)

###################ORACLE_DATA_GAURD############################

10. Add static entries to listener.ora file

PRIMARY

(SID_DESC =
(GLOBAL_DBNAME = test.db.com)
(SID_NAME = TEST)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME=TEST_DGMGRL)
(ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
(SID_NAME=test)
)

STANDBY

(SID_DESC =
(GLOBAL_DBNAME = STBY.db.com)
(SID_NAME = STBY)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)
(SID_DESC =
(GLOBAL_DBNAME = STBY_DGMGRL)
(SID_NAME = STBY)
(ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
)

11. Reload listener after adding these entries

Lsnrctl reload

12. Add Standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;

13. Copy the passw0rd file from Primary and copy to Standby

14. Copy the init.ora file from primary and change below parameters :-

CREATE PFILE='/initTEST.ora' from spfile;

scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY.ora

Steps to be done in STANDBY Side :-

15. Start the STANDBY database with above pfile

export ORACLE_SID=STBY
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY.ora'
ORACLE instance started.

Total System Global Area 960495616 bytes
Fixed Size 1222816 bytes
Variable Size 264243040 bytes
Database Buffers 683671552 bytes
Redo Buffers 11358208 bytes

16. Connect to RMAN and use RMAN duplicate command to create STANDBY

rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='STBY' COMMENT 'Is STANDBY'
SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
set control_files='/ora_backup/TEST/duplicate_test/STBY/control01.ctl','/ora_backup/TEST/duplicate_test/STBY/control02.ctl'
set db_create_file_dest='/ora_backup/TEST/duplicate_test/STBY/DATA/'
set db_create_online_log_dest_1='/ora_backup/TEST/duplicate_test/STBY/LOG/'
set diagnostic_dest='/ora_backup/TEST/duplicate_test/STBY/diag'
set audit_file_dest='/ora_backup/TEST/duplicate_test/STBY/adump'
set db_recovery_file_dest='/ora_backup/TEST/duplicate_test/STBY/FRA'
set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','/ora_backup/TEST/duplicate_test/STBY/DATA/'
set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','/ora_backup/TEST/duplicate_test/STBY/'
set log_archive_dest_1='location=/ora_backup/TEST1/duplicate_test/STBY/FRA/'
set FAL_SEVER=TEST
;

Above command will create the STANDBY database

17. Create the broker configuration details ( for both Primary and Standby):-

SQL> alter system set dg_broker_start=FALSE;

System altered.

SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

Create configuration DR as primary database is TEST connect identifier is TEST;
Add database STBY as connect identifier is STBY maintained as physical;

show configuration
enable configuration

EDIT DATABASE STBY SET STATE = 'APPLY-ON' [WITH APPLY INSTANCE = ]; -- For RAC database

Check if every thing is fine by checking Standby Database details

show database stby

DGMGRL> show database "STANDBY"

Database - STANDBY

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

Database Status:
SUCCESS

Standby Creation using RMAN duplicate command completed.

Standby creation in RAC using RMAN duplicate.

I hope this article helped you

Regards,
Amit Rath

Tuesday, December 16, 2014

MRP0: Background Media Recovery terminated with error 1274

Yesterday one of our standby database recovery stopped with below error :-

Errors in file /ora_backup/TEST/duplicate_test/AMIT/diag/diag/rdbms/standby/STANDBY/trace/STANDBY_pr00_12842.trc:
ORA-01119: error in creating database file '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
Additional information: 1
File #13 added to control file as 'UNNAMED00013'.
Originally created as:
'/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
Recovery was unable to create the file as:
'/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /ora_backup/TEST/duplicate_test/AMIT/diag/diag/rdbms/standby/STANDBY/trace/STANDBY_pr00_12842.trc:
ORA-01274: cannot add datafile '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 5662710 but controlfile could be ahead of datafiles.

Problem :-

It seems in primary a datafile has been added and it's not able to replicate the same in standby due to above errors.

Cause :-

Above error can cause due to below reasons :-

1. STANDBY_FILE_MANAGEMNET parameter is set to maual
2. If having Different file structure, db_file_name_convert is not properly set according to standby directory structure or db_create_file_dest parameter is not set in standby

Solution :-

In these kind of scenario when due to above parameters are missing in standby,
when we add a datafile in primary it failed to replicate in standby. We can see below mentioned error in alert log and recovery will stop due to this.

ORA-01274: cannot add datafile '/ora_backup/TEST1/restore_test/AMIT/DATA/AMIT_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).

Datafile detail will be present in standby controlfile but with below name :-

/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00013

So to resume recovery in these kind of scenarios we have to maualy create this datafile and start the recovery process. PFB steps :-

1. Check standby_file_management is set to manual

to add a datafile maualy in STANDBY database above parameter has to be in manual state

SQL> alter system set standby_file_management=manual;

System altered.

from DB_BROKER also we can change this :-

DGMGRL> edit database 'STANDBY' set property standbyfilemanagement=manual;

Property "standbyfilemanagement" updated

2. Create the database file with correct name in Standby database

SQL> alter database create datafile '/opt/oracle/product/database/11.2.0.4/dbs/UNNAMED00013' as '/ora_backup/TEST/duplicate_test/AMIT/DATA/STANDBY/datafile/amit03.dbf';

Database altered.

3. Check the datafile details in controlfile :-

/ora_backup/TEST/duplicate_test/AMIT/DATA/STANDBY/datafile/amit03.dbf

now its coming appropriate

4. Start the Mrp process to resume recovery.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Check the alert logfile to verify everything is fine. You can also check from Dataguard regarding sync details :-

DGMGRL> show database "STANDBY"

Database - STANDBY

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 2 seconds ago)

Apply Lag: 0 seconds (computed 2 seconds ago)

Apply Rate: 0 Byte/s

Real Time Query: OFF

Instance(s):

STANDBY

Database Status:

SUCCESS

To get rid from these kind of issues in future , I did following changes in this database:-

1. change STANDBY_FILE_MANAGEMENt to AUTO

2. Set the db_file_name_convert parameter accoding to standby database.

I hope this article helped you.

Regards,
Amit Rath

Oracle Database

About Me

Wednesday, January 11, 2017

MRP0: Background Media Recovery terminated with error 1111

Monday, July 27, 2015

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

Friday, July 24, 2015

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

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

Friday, December 19, 2014

How to create a Standby Database in RAC using RMAN Duplicate command

Wednesday, December 17, 2014

RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

How to Create a STANDBY database using RMAN Duplicate command in Oracle

Tuesday, December 16, 2014

MRP0: Background Media Recovery terminated with error 1274

Popular Posts

Followers

Labels