Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts

Saturday 8 January 2022

Consistency check before performing RMAN resetlogs.


The article covers consistency check before performing OPEN RESETLOGS after restoring a database using backup 

Saturday 4 September 2021

Open Resetlog Failure : ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

SCOPE

Restoring the database using RMAN backup.

After completion of cloning to the staging environment, OPEN RESETLOGS fails with error as below.

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

Monday 30 November 2020

RMAN Backup Validate

 Sometimes RMAN backup files might get corrupted. Sometimes disk block corruption might happen. So it is better we do validate the database backups more frequently in order to make sure it is recoverable.

Tuesday 2 October 2018

RMAN-08591: WARNING: invalid archived log deletion policy


For one of database we were getting backup failure alerts where the backup was configured on standby database.

We saw below Error messsages in backup-log :


RMAN-08591: WARNING: invalid archived log deletion policy


-

  
Cause: If archive log location is set to FRA, then there might be a chance of the deleting the archives automatically when the space pressure in FRA.
In that case at least one of the destination standby must be set as a "MANDATORY" destination.

Solution: To eliminate the RMAN warning message, at least one archive destination must be set as a mandatory destination.

Steps : 

1. Log in to the broker command line utility


[oracle@host1 log]$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration  primdb1
  Protection Mode: MaxPerformance
  Members:

  primdb1 - Primary database
    bcpdb1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 7 seconds ago)


2. For the standby site check the "Binding" property


DGMGRL> show database verbose bcpdb1

Database - bcpdb1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      11 minutes (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 625.00 KByte/s
  Active Apply Rate:  523.00 KByte/s
  Maximum Apply Rate: 18.62 MByte/s
  Real Time Query:    ON
  Instance(s):
    bcpdb1
Properties:
----------
----------
Binding = 'optional' ---------- ----------




3. Set the "binding" Property as - MANDATORY
        
//This parameter controls whether the destination is mandatory or not

DGMGRL> edit database bcpdb1 set property Binding='mandatory';

Property "binding" updated

DGMGRL> exit


Once the broker configuration has set a standby site as a mandatory destination, the RMAN configuration can be altered to set the archivelog deletion policy to applied on standby.


RMAN> configure archivelog deletion policy to applied on standby;

Once the broker configuration has set a standby site as a mandatory destination, then RMAN will not report this error again.


Reference: Data Guard Physical Standby - RMAN configure archivelog deletion policy reports RMAN-08591 (Doc ID 1984064.1)

-

Found this post interesting? Subscribe us  ðŸ˜ŠðŸ˜‰

Read more 

Configure Streaming Replication in PostgreSQL

Installation and configure Postgres 13 on RHEL

All about Physical Replication and Log shipping in Postgres 

Possible ways to recover space from deleted rows with insufficient disk space

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping


“Be like a tree. Stay grounded. Connect with your roots. Turn over a new leaf. Bend before you break. Enjoy your unique beauty. Keep growing.” -- Joanne Rapits.

Sunday 31 December 2017

Steps to Schedule any RMAN Jobs In Windows Server



This post is about the backup configuration made in Windows server and what are the backup strategies mainly used in most of production databases.

Backup strategy followed in most of  production Databases.
1) Weekly RMAN full backup
2) Daily Archive log backup
3) Daily incremental backup

In windows server environment, we use “scheduler” (such as cronjob in UNIX) to schedule any job.


The important steps to configure a scheduler are as follows :

Taking Database backup weekly(incremental level 0):
First create a cmd file (command file ) in windows: 

Let us create a file name week_backup.cmd using a text file editor(any editor) write the rman script to take the backup.

Assume, I want to take full backup (i.e, LEVEL 0 Backup)
-        Then the script for taking the weekly Level 0 backup would be like this, (here controlfile autobackup is off(default)).

Step 1.
week_backup.cmd



RMAN>run{ 
Allocate channel ch1 type disk format '\path of taking backup_\%d_data_%U'; 
Backup incremental level=0 database tag=full_backup'; 
Release channel ch1; 
Allocate channel t1 type disk format '\ backup location \%d_ctrl_%U'; 
Backup current controlfile; 
Release channel t1; 
}




Step 2.

Create the .bat file (Batch file is for batch job execution by scheduler)
Let us create a batch file week_backup.bat

The script looks like as below:


week_backup.bat 

rman target sys/db1@db1

cmdfile=\Path of cmd file\week_backup.cmd   <-------- you need to mention the file location we created at Step 1 above.
log=E:\Path of log file\Full_DB_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log
The file will be created as follows
Full_DB_Backup_<Current_Date>.log

This batch file is used executing this file through schedule.



Step 3.
Here comes the scheduling part. We have to use scheduler for scheduling job:
Goto- Start Menu>
 control panel >scheduler tasks >Add scheduler task >command prompt >Perform this task > weekly >choose day and timings when you want to run this job weekly >specify your administrator username and password >finish
 

Configuring  Database backup Daily(incremental LEVEL 1)

For this activity, all the above steps remains same, but the scripts and scheduler task option changes here:

1. backup_incr_1.cmd  

run{
allocate channel ch1 type disk format '\path of backup location_\%d_data_%U';
backup incremental level=1 database tag=Level_1_Backup';
release channel ch1;
}

2. backup_incr_1.bat 


rman target sys/db1@DBNAME 
cmdfile=\Path of cmd file created in Step1 above\ backup_incr_1.cmd 
log=E:\Logfile location\Level_1_Backup_%date:~4,2%_%date:~7,2%_%date:~10%.log

Now, Configure the scheduler as we did above in Step 3 and  simply  change the Scheduler option > as ‘daily’ and timings whichever  you want.


 -


For information on  UNIX System Commands you can check an article here – UNIX for DBA's


Click here for RMAN recovery scenarios





Everything comes to us that belongs to us, if we create the capacity to receive it

-Rabindranath Tagore

Wednesday 12 April 2017

ORA-01194: file 1 needs more recovery to be consistent


ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/dump01/files/data/system01'


If you try to give “alter database open resetlogs” command after cloning the database  using cold backup you probably see this error.


To troubleshoot this, follow these steps


SQL>  recover database;
ORA-00283: recovery session cancelled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 



So, you must recover using the backed up controlfile . Why ? Because the controlfile has information of archives that need to be applied during recovery.

Lets do this

SQL>  recover database until cancel using BACKUP CONTROLFILE;
ORA-00283: recovery session cancelled due to errors

Viola…!!

Now shutdown the database


SQL>shut immediate


Go to $ORACLE_HOME/dbs and locate the pfile of respective database.

And  add below parameter in the pfile
 _allow_resetlogs_corruption=true




SQL> startup mount pfile= $ORACLE_HOME/dbs/initDBNAME.ora;

ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size                  2188408 bytes
Variable Size             436214664 bytes
Database Buffers          616562688 bytes
Redo Buffers               14286848 bytes
Database mounted.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE


Done . . !!



Saturday 27 August 2016

How to make a cold backup for database running on ASM ( using RMAN )





Many a times, you will come in a situation… Or they may ask you in Interview that how to take a cold backup using RMAN.
You heard Right, Cold backup using RMAN…
Using RMAN Utility, we can perform the backup in MOUNT mode.

Lets see…
Follow the below steps to take cold back of database running on ASM.

1.Start the instances in all the nodes in mount stage.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7504E+10 bytes
Fixed Size                  2235448 bytes
Variable Size            4429185992 bytes
Database Buffers         1.3019E+10 bytes
Redo Buffers               53477376 bytes
Database mounted.


2.Connect RMAN from any node and take the backup as below.

[oracle@backupServer ]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 2 05:53:43 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SPSHIDMP (DBID=465850406, not open)

RMAN> backup device type disk format '/backupServer/test/rman_backup_43/backup/%U' database plus archivelog;


Starting backup at 02-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4566 instance=spshidmp1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=55 RECID=107 STAMP=783110838
input archived log thread=1 sequence=58 RECID=106 STAMP=783107220
input archived log thread=1 sequence=59 RECID=108 STAMP=783139625
input archived log thread=2 sequence=56 RECID=109 STAMP=783155353
input archived log thread=1 sequence=60 RECID=112 STAMP=783164816

3.Once done, start the instances on all the nodes.

SQL> alter database open;
Database altered


We can take a database Backup using RMAN in Mount, this will be said as COLD-BACKUP

Wednesday 3 August 2016

Error faced while Database Cloning from 64 bit to 32 bit version



Few days back, I faced this issue when I was performing a task of cloning from Higher Version to Lower Version.

I was cloning a production database which runs on 64 bit platform to a 32 bit platform. Cloning completed successfully without any error. But i found 2 issues

1. First , When I tired logging into the cloned database as non sys user Credentials , I faced some error.

2. When i tried to do ALTER TABLESPACE TEMP ADD TEMPFILE i encountered the below error

ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]


Solution:

Whenever you change the platform between 32 bit and 64 bit you should run the script ‘utlirp.sql’ which is located in (Oracle_Home/rdbms/admin  directory)

For this, you need to open your database in ‘Upgrade’ Mode .

Follow Below steps.

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

SQL> startup upgrade;                                                  //open in Upgrade Mode

ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 155190164 bytes
Database Buffers 373293056 bytes
Redo Buffers 5844992 bytes
Database mounted.
Database opened.

SQL> @/$ORACLE_HOME/RDBMS/ADMIN/utlirp.sql


SQL> shutdown immediate;
SQL> startup;

Database was back again, to normal operations :)