Oracle database refresh

Source db name           = ABC

Destination db name    = DEF

Considering the following:

1. OS version and db version of both the dbs is same.

Taking RMAN backup on source database

Check whether the source DB is running in archivelog mode or not. run the below command

sudo su – <dbuser>

sqlplus / as sysdba

archive log list

Incase the source DB is running in archivelog mode, then do the below steps to take the online backup of source DB using rman:

Copy the below contents into a file /tmp/archivelogdbbkp.rman

RUN {

CROSSCHECK BACKUPSET;

CROSSCHECK BACKUP;

BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';

CROSSCHECK COPY;

CROSSCHECK ARCHIVELOG ALL;

CROSSCHECK BACKUP;

DELETE NOPROMPT EXPIRED BACKUPSET;

DELETE NOPROMPT EXPIRED BACKUP;

DELETE NOPROMPT EXPIRED COPY;

DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

ALLOCATE CHANNEL ch00 TYPE DISK;

ALLOCATE CHANNEL ch01 TYPE DISK;

ALLOCATE CHANNEL ch02 TYPE DISK;

ALLOCATE CHANNEL ch03 TYPE DISK;

BACKUP AS COMPRESSED BACKUPSET FORMAT '<directory_name>/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE PLUS ARCHIVELOG;

BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';

}

Allocate more channel as per need.

Then run the below nohup command to initiate the rman backup

nohup rman target / cmdfile='/tmp/archivelogdbbkp.rman' log='/tmp/archivelogdbbkp.rman' &

Incase the source DB is running in NOARCHIVELOG mode, do the below steps to take the offline rman backup of source DB:

Take the rman backup of ABC database.

Copy the below contents into a file /tmp/noarchivelogdbbkp.rman

RUN{
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
CROSSCHECK BACKUP;
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUPSET;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
ALLOCATE CHANNEL ch00 TYPE DISK;
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP AS COMPRESSED BACKUPSET FORMAT '<directory_name>/DB_BACKUP_%d_%T_%t_%s_%p.rman' DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT '<directory_name>/CONTROLFILE_BACKUP_%d_%T_%t_%s_%p.rman';
ALTER DATABASE OPEN;
} 

Allocate more channel as per need.

Then run the below nohup command to initiate the rman backup

nohup rman target / cmdfile='/tmp/noarchivelogdbbkp.rman' log='/tmp/noarchivelogdbbkp.log' &

Monitor the rman logfile and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the backup progress.

set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) "Percent Complete",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/

Once the rman backup is complete, scp all the rman backup files onto  DEF db server. Make sure the db user of DEF db server has got the read access on each of these rman backup files.
Run report schema on ABC db and save its output. it will be used while restoring this backup on DEF db.

On DEF db:

select name,open_mode from v$database;

Take the backup of spfile and pfile 

sudo su - <dbuser>cp $ORACLE_HOME/dbs/spfileDEF.ora /tmp/ 

sqlplus / as sysdba

show parameter pfile;
create pfile='/tmp/initDEF.ora' from spfile;

Shutdown all the DEF database instances.
Change the cluster_database parameter to false in $ORACLE_HOME/dbs/initDEF.ora. (it is only needed if db has more than 1 instances).

startup and mount the database in exclusive mode
sqlplus / as sysdba
startup mount restrict exclusive pfile='$ORACLE_HOME/dbs/initDEF.ora';

--Drop the DEF database
drop database;

Now check that all space has been cleared. (datafiles, logfiles, and control files should be deleted.) Delete them manually if something left.
Make sure your datafiles filesystem or diskgroup of DEF db server has sufficient space to store data of Live database.

Change the name of the pfile as that of source db.

cp /tmp/initDEF.ora $ORACLE_HOME/dbs/initABC.ora

Change the value of db_name parameter to ABC in initABC.ora

Make sure that name of the undo tablespace is same as the name in Live

Make sure that the shared_pool_size is set like *.shared_pool_size.

change the ORACLE_SID env variable to ABC

setenv ORACLE_SID ABC

or 

export ORACLE_SID=ABC

Start up the database in nomount state

Now catalog rman backup files.

catalog start with '<directory location where rman backup files are present on DEF db server>/';"

Check the report schema output earlier taken on ABC db. Get the file_ids and their size from it.

IF DEF db resides on file system based server to store the datafiles

we would need to allocate the datafiles into different FS on the destination server according the size of each FS. e.g. sapdata1 FS has a free space of 100GB and datafile 1, datafile 2 and datafile3 and datafile 4 are of in total size 95GB, then datafile 1 to 4 will be allocated in sapdata1 FS. similary the subsequent datafiles are allocated in the remaining FSs. once this calculation is done, the below rman script should be prepared for reference.

RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
SET NEWNAME FOR DATAFILE 1 TO '/oracleDEF/DEF/sapdata1/datafiles/system.270.831670025';
SET NEWNAME FOR DATAFILE 2 TO '/oracleDEF/DEF/sapdata1/datafiles/sysaux.271.831670051';
SET NEWNAME FOR DATAFILE 3 TO '/oracleDEF/DEF/sapdata1/datafiles/psapundo.267.831669899';
SET NEWNAME FOR DATAFILE 4 TO '/oracleDEF/DEF/sapdata1/datafiles/psapsr3.260.831669393';
SET NEWNAME FOR DATAFILE 5 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3.272.831670075';
SET NEWNAME FOR DATAFILE 6 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.259.831669277';
SET NEWNAME FOR DATAFILE 7 TO '/oracleDEF/DEF/sapdata2/datafiles/psapsr3702.261.831669489';

...

..

.
SET NEWNAME FOR DATAFILE 55 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.312.944651039';
SET NEWNAME FOR DATAFILE 56 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.313.946635317';
SET NEWNAME FOR DATAFILE 57 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.314.947955021';
SET NEWNAME FOR DATAFILE 58 TO '/oracleDEF/DEF/sapdata5/datafiles/psapsr3.315.950772363';
restore database;
switch datafile all;

recover database;

}

allocate more channels as per need.

Now start the restore from the backup. Copy the above contents into a file /tmp/restore.rman
Then run the below nohup command to initiate the rman restore on DEF db server.

nohup rman target / cmdfile='/tmp/restore.rman' log='/tmp/restore.log' &"

Monitor the logfile /tmp/restore.log and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the restore progress.

set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/

Once it is completed, check the /tmp/restore.log file and make sure that there is no ORA error came in it.

also, make sure that the all archivelogs present in the rman backup have been successfully applied. otherwise explicitily restore the archivelogs from the backupand recover them manually.

IF DEF db resides on diskgroup based server to store the datafiles, 

normally there is only 1 diskgroup is present to store all the datafiles, so make sure that diskgroup has sufficient space to store all the datafiles of ABC database. 

Say the diskgroup name is +DATA, the below rman script should be prepared for restore:

RUN
{
Allocate channel ch00 type disk;
Allocate channel ch01 type disk;
Allocate channel ch02 type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA';
SET NEWNAME FOR DATAFILE 2 TO '+DATA';
SET NEWNAME FOR DATAFILE 3 TO '+DATA';
SET NEWNAME FOR DATAFILE 4 TO '+DATA';
SET NEWNAME FOR DATAFILE 5 TO '+DATA';

...
..
.
SET NEWNAME FOR DATAFILE 55 TO '+DATA';
SET NEWNAME FOR DATAFILE 56 TO '+DATA';
SET NEWNAME FOR DATAFILE 57 TO '+DATA';
SET NEWNAME FOR DATAFILE 58 TO '+DATA';
restore database;
switch datafile all;
recover database;
}

allocate more channels as per need.

Now copy the above contents into a file /tmp/restore.rman
Then run the below nohup command to initiate the rman restore on DEF db server.

nohup rman target / cmdfile='/tmp/restore.rman' log='/tmp/restore.log' &"

Monitor the logfile /tmp/restore.log and make sure that there is no ORA or RMAN error occurs in it.
Also, run the below sql query to check the restore progress.

set pages 333 lines 222
select inst_id,sid,SOFAR,TOTALWORK,round(SOFAR*100/TOTALWORK) ""Percent Complete"",
TIME_REMAINING from gv$session_longops
where sid in
(select sid from gv$session where username is not null /*and status='ACTIVE'*/)
and TIME_REMAINING <> 0
order by 4 desc
/

Once it is completed, check the /tmp/restore.log file and make sure that there is no ORA error came in it.

also, make sure that the all archivelogs present in the rman backup have been successfully applied. otherwise explicitily restore the archivelogs from the backupand recover them manually.

now, check the status of redo log file using

select * from v$log;

if any of the redo log group is in CLEARING status, manually clear that redo log using below command:

alter database clear logfile group < group id of redo log group>;

Also, check the file location of all redo log files using below command:

select * from v$logfile;

Rename all the logfiles as per the FS structure or disk group of DEF database server using below command for each of those logfile:

alter database rename logfile 

Then open the database with resetlogs option.

alter database open resetlogs;

Ideal procedure to take and apply incremental backup to resolve archive logs gap

1. Check the scn from which the incremental backup needs to be taken on standy databas :

Run below sql query on standby database:


select min(to_char(checkpoint_change#)) from v$datafile_header;

e.g:

SQL> select min(to_char(checkpoint_change#)) from v$datafile_header;

MIN(TO_CHAR(CHECKPOINT_CHANGE#))

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

77982638190

SQL>

In general, current_scn from v$database on standby database is taken which is wrong practice. if we use current_scn for taking incremental backup and scn of any datafile on standby database is lower than the current_scn, then even after applying the incremental backup on standby database, the MRP on standby database will still be stuck on old archive logs(which it was asking before applying incremental backup). so always use the min scn from v$datafile_header.

2. Defer the sync from primary onto standby database

alter system set log_archive_dest_state_2=defer scope=both;

3. Take the incremental backup and controlfile for standby backup on primary:

Put below rman script in a rcv file say <target_dir>/incr.rcv

run {

allocate channel dsk_1 device type disk;

allocate channel dsk_2 device type disk;

backup as compressed backupset incremental from scn 115203415483 database maxpiecesize 2G format '<target_dir>/dbname_incr_%d_%t_%t_%s_%p.rman';

backup current controlfile for standby format '<target_dir>/ctrl_stndby_dbname.ctl';

release channel dsk_1;

release channel dsk_2;

}

#77982638190 = scn taken from standby database above in step 1.

# MAXPIECESIZE = it defines the output backup filesize. it is optional. you can remove it or increase/decrease the size as per your need.

4. Once the incremental backup is completed, transfer or make available the backup files on to the standby database.

5. Stop MRP on the standby database/

alter database recover managed standby database cancel;


6. Shutdown all the instances of the standby database.

7. Startup only one instance in nomount state.

startup nomount

8. Restore the standby control file from the standby controlfile backup taken in step 3 above.

rman target /

restore standby controlfile from '<standby controlfile backup directory location>';

9. Mount the database

sql ‘alter database mount standby database’;


10. Switch the datafiles.

Since the controlfile is taken from the primary database, so we will need to update the controlfile on standby with the actual datafiles location of standby database and switch each of the datafile.

catalog start with '<datafiles location>';

Run report schema command to get the file_id of all the datafiles and note the file_id of all the datafiles.

switch each of the datafiles using below command:

switch datafile 1 to copy;
switch datafile 2 to copy;
switch datafile 3 to copy;
switch datafile 4 to copy;
switch datafile 5 to copy;
...
..
.
switch datafile n to copy;

where n = file_id of last datafile as observed in report schema.


11. Delete expired backupset information on the standby database:

delete expired backupset;

12. Catalog the incremental backup:

rman target /
catalog start with '<directory location of incremental backup on standby database>’;

13. Check incarnation of primary and standby database.

list incarnation;

If the incarnation is different on standby, then Reset incarnation of standby db to that of incarnation value of primary db.

e.g. if incarnation value on standby database is 3 whereas the incarnation value on primary database is 2. so, you should run below rman command on standby database:

reset database to incarnation 2.

14. Perform recover database on standby database:

recover database;

this step is important to apply the incremental backup. if you skip this step and directly start the MRP, the MRP will still be stuck on old archive logs. so to forward the MRP sequence value, this step is important  to run.

15. Start managed recovery process:

alter database recover managed standby database using current logfile disconnect from session;

how to permanently change environment variables in oracle database

The below are the steps:

1. Shutdown the database instance for which the env variable has to be changed.
2. Backup $HOME/.cshrc file.
3. Change the value of the required env variable to the desired value in $HOME/.cshrc file.
4. Logout and login again from the OS user which is the corresponding SYS user in database. in my case, it is oracle user.
5. Startup the database instance.

In my case, the value of ORACLE_BASE parameter which is showing in pfile(after creating it from spfile) was incorrect. i had to change it using below above procedure.

Regards

Vineet Arneja

Difference between oracle 9i, 10g, 11R1 and 11R2.

Below table brief out the difference in brief between oracle database versions 9i, 10g, 11gR1 and 11gR2.

9i	10g	11gR1 or 11.1	11gR2 or 11.2
PGA_AGGREGATE_TARGET parameter was introduced to automate the pga management.	SGA_TARGET was introduced to automate the SGA management.	MEMORY_TARGET was introduced to automate the management of both SGA and PGA
High availability using cluster.	Grid computing. VIP		SCAN, CTSS Storage of OCR and voting disk in ASM. GPNP service which eases the removal and addition of RAC nodes. Oracle Restart service to provide HA for single instance database.
Introduced DataGuard	DataGuard Realtime apply and log compressions Max no of standby dbs are 10	Snapshot standby	Active DataGuard Also, automatic block repair in active dataguard. New rman policy: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY. Max no of standby dbs are 30
Traditional exp/imp	Data Pump
	SYSAUX tablespace
	ADDM and AWR
patching takes minutes.	patching takes few seconds	Online patching
	ASM	SYSASM privilege Asmcmd Also, new disk group compatibility attributes that are compatible.rdbms and compatible.asm which enables hetrogenous environments i.e. disk groups from both 10g and 11g.	cp command in asm
	Automatic UNDO tablespace management.
	Backup compression in Rman
Default temporary tablespace	Rename temporary tablespace		Shrink temporary tablespace
background_dump_destination		diagnostic_destination

Regards

Vineet Arneja

MRP process stuck at old sequence even after restoring incremental backup from primary database.

The below article applies to oracle database 11gR2 release and further releases.

Issue:
MRP process on physical standby stuck at old sequence of archive log even after recovering the standby database using the incremental backup from the primary database.


Diagnosis:
The incarnation of database at the standby database was different (or say newer) from the primary database. Please see the below logs:


Standby database:
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       MWQ      2446434119       ORPHAN  9515432557014 07-AUG-17
2       2       MWQ      2446434119       ORPHAN  9515432560577 07-AUG-17
3       3       MWQ      2446434119       CURRENT 9515667276518 22-AUG-17

RMAN>


Primary database:
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       MWQ      2446434119       PARENT  9515432557014 07-AUG-17
2       2       MWQ      2446434119       CURRENT 9515432560577 07-AUG-17

RMAN> 


Resolution:

Before executing 'recover database noredo' to recover the incremental backup data on standby database, reset the incarnation of standby database as that of current incarnation value of primary database using below rman command:

RMAN> reset database to incarnation 2;

using target database control file instead of recovery catalog
database reset to incarnation 2
RMAN> 

Oracle 11gR2 RAC startup sequence

The below article applies to Oracle RAC 11GR2 and further releases.

Below are the steps in brief:

1.     Startup of ohasd.bin process. it further startup the ohasd.bin process.

2.     ohasd access OLR file and get GPNP Profile location and spawns cssd process.

3.     CSSD daemon access GPNP Profile, get voting disk location, access it and join the cluster.

4.     Ohasd process access the ASM spfile and startup the ASM instance.

5.     Ohasd’s orarootagent process starts up the crsd process. The crsd process reads OCR file and completes the cluster initialization.

6.     The crsd process spawns the oraagent and orarootagent process and these two further spawns the subsequent crs resources processes.

The below are the steps with detailed information.

 1.     Startup of ohasd.bin process.

The init.ohasd process entries are present in /etc/inittab file, so it get started automatically by the OS after server boots up. This entry is added by root.sh script after grid installation. The entry is like below:

 

h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null

 

35 means run level i.e. 

3 is for Multiuser mode with Networking 

5 is for Multiuser mode with networking and GUI

 

It means the OS must have the run level equals to either 3 or 5 to get this process started. We can check the current run level of the OS using “who –r” command.

 

[oracle@edwdev02 ~]$ who -r

         run-level 3  2021-06-24 12:35

[oracle@edwdev02 ~]$

 

respawn means if the process was not started, then start it.

 

The process init has pid=1,

It’s absolute location is /sbin/init on Linux, Solaris and hp-ux OS platforms

Whereas its location is /usr/sbin/init on AIX OS Platforms.

 

[root@node1 ~]# ps -ef|grep init

root         1     0  0 Jun24 ?        00:00:44 /sbin/init

oracle   31134 31026  0 19:29 pts/1    00:00:00 grep init

[root@node1 ~]#

 

This init process starts “init.ohasd”process. This init.ohasd process will further startup ohasd.bin process which is also called ohasd service.

 

2.     ohasd access OLR file and get GPNP Profile location and spawns cssd process.

The OLR file location is present in /etc/oracle/olr.loc. PFB the reference output:

[root@node1 ~]# cat /etc/oracle/olr.loc

olrconfig_loc=/u01/app/11.2.0/grid/cdata/node1.olr

crs_home=/u01/app/11.2.0/grid

 

[root@node1 ~]# ls -ltr /u01/app/11.2.0/grid/cdata/node1.olr

-rw-------. 1 root oinstall 272756736 Jan  8 21:40 /u01/app/11.2.0/grid/cdata/node1.olr

[root@node1 ~]#

 

The OLR file is present in $GRID_HOME/cdata/ directory. It is named as ‘<hostname>.olr’

The ohasd process get the location of OLR file from /etc/oracle/olr.loc and access the main OLR file and gets the below info which is stored in it:

          I.        GPNP file details. It is present in $GRID_HOME/gpnp/<hostname>/profiles/peer/

         II.        OCR latest backup time and location.

        III.        CRS_HOME

       IV.        Active crs version.

        V.        Local host name and crs version

       VI.        Status of the resources to know which are to be started and which are to be kept OFFLINE.

      VII.        Start and stop dependencies of the resouces i.e.

Hard Dependency means it must full-fill

Weak Dependency means it should full-fill.

GPNP Profile stands for Grid Plug and Play Profile. The file is located in CRS_HOME/gpnp/<node_name>/profile/peer/profile.xml. And this profile consists of cluster name, hostname, network profiles with IP addresses, OCR. If we do any modifications for voting disk, profile will be updated.

ohasd.bin process then further spawns:
   1. its orarootagent process.
   2. its oraagent process.
   3. cssdagent process.
   4. cssdmonitor process.

The ohasd’s orarootagent process further spawns the gpnpd process and cssdagent process further spawns the cssd daemon process.

 

3.     CSSD daemon access GPNP Profile, get voting disk location, access it and join the cluster.

 

The cssd daemon then access the local GPNP profile of the node at /u01/app/12.2.0.2/grid/gpnp/node2/profiles/peer/profile.xml. It contains the below info:

 

   i. Name of the ASM Diskgroup containing the Voting Files
   ii. ASM SPFILE location (Diskgroup name)
   iii. ASM Diskgroup Discovery String

 

Fyi below info:
Oracle ASM reserves several physical blocks at a fixed location for every Oracle ASM disk to store the the voting disk file i.e. the physical location of voting disk file is fixed on each asm disk. As a result , Oracle Clusterware can access the voting disks present in ASM even if the ASM instance is down.

The cssd process mainly access the voting disk file’s diskgroup location from it. It then physically scan the headers of each disk present in that diskgroup and find out the disks which contains the voting disk file. Now, it has got the voting disk files, so it will now join the cluster.

 

4.     Ohasd process access the ASM spfile and startup the ASM instance.

The same way ASM spfile is read to startup the asm instance.

5.     Ohasd’s orarootagent process starts up the crsd process. the crsd process reads OCR file and completes the cluster initialization.

6.     The crsd process spawns the oraagent and orarootagent process.

 

The CRSD orarootagent then further spawns the below resources:
   1. Network resource : for the public network
   2. SCAN VIPs
   3. Node VIPs : VIPs for each node
   4. ACFS Registry
   5. GNS VIP : VIP for GNS if you use the GNS option

and the CRSD oraagent spawns the below resources:

   1. ASM Resources
   2. Diskgroups
   3. DB Resources
   4. SCAN Listener : Listener for SCAN listening on SCAN VIP
   5. Listener : Node Listener listening on the Node VIP
   6. Services : Database Services
   7. ONS
   8. GSD
   9. GNS