Oracle DBA Online Test(MCQ) For Beginner Questions

 

Redo Log Files in Oracle Database

 Introduction

Redo Log Files(.log): Oracle maintains logs of all the transaction against the database,These transactions are recoreded in files called online redo log files(Redo logs) The main purpose of the redo log files is to hold information as recovery in the event of system failure,redo log stores a log of all changes made to the database the redolog files must perform well and be protected against hardware failures (through software or hardware fault tolerance).if redolog information is lost,one cannot recover the system when a transcation occurs in the database,it is entered in the redo log buffers,while the data blocks affected by the transactions are not immediately written to disk,in an oracle database there are at atleast three or more redolog files,oracle  writes to redolog files in a cyclical order i.e after the first log file is filled ,it writes to the second log file,untill that one is filled .when all the redo log files have been filled,it returns to the first log file and begin overwrite its content with new transaction data.note if the database is running in ARCHIVELOG mode,the database will make a copy of the online redolog files before overwriting them

How to increase the size of the redo log files?

Steps :

`       Add new groups with larger size

        drop existing inactive members.

sql>select member from v$logfile;

  eg: expected : 100m Current : 50M  (recommended 1G-2G in live for faster performance)

Adding New Groups:

sql>ALTER DATABASE

ADD LOGFILE GROUP 4 '/oradata/prod/redo4.log' size 200m;

ALTER DATABASE

ADD LOGFILE GROUP 5 '/oradata/prod/redo5.log' size 200m;

ALTER DATABASE

ADD LOGFILE GROUP 6 '/oradata/prod/redo6.log' size 200m;

sql>select group#,members,bytes/1024/1024,status from v$log;

Use manual log switch to make use of new redo groups.

sql>alter system switch logfile;

Now drop inactive groups of different in size.

sql>ALTER DATABASE DROP LOGFILE GROUP 1;

sql>ALTER DATABASE DROP LOGFILE GROUP 2;

sql>ALTER DATABASE DROP LOGFILE GROUP 3;

IF found active - use log switch to make it inactive

sql>alter system switch logfile;

sql>select group#,members,bytes/1024/1024,status from v$log;

Managing Archive log files

logbuffer(1/3rdor3 secs)-lgwr-logfiles->archiver->arc log files

The process of turning online redo logfiles into offline

redolog files is known has archiving.

The offline redo logfiles are called Archivelog files.

with .arc ext

Its mandatory in production to enable archiving and optional in Development.

The content of archives can be used for recovery of datafiles.

Enabling Archive log mode:

Physical Dest - /archives/prod - .arc

mkdir -p /archives/prod

sql>archive log list

sql>show parameter log_archive_dest_1

format

sql>show parameter log_archive_format

sql>alter system set log_archive_dest_1='LOCATION=/archives/prod' scope=both;

sql>alter system set log_archive_format='%t_%r_%s.arc' scope=spfile;

%t - thread number associated with instance number1

%r - redo sequence

%s - log sequence number

sql>shutdown immediate

sql>startup mount

sql>alter database archivelog;

sql>alter database open;

sql>archive log list

sql>alter system switch logfile;

sql>exit

cd /archives/prod

redologfiles->arch(bg process) -> archivelogfile(.arc)

Here archiver is the background process writes from online

redo log files to off archivelog files when log switch occurs.

sql>show parameter log_archive_max_processes

We can have up to 30 max processes of archiver

$ps -ef | grep arc

default - 4

Note: Info on redolog it may be differ from your environment production,testing,dev naming conventions,and directory structure etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle RAC Patching

Oracle RAC Patching

There are total three methods by which we can apply patch to RAC Cluster Environment on Unix System:

Patching RAC as a single instance (All-Node Patch)

Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

Patching RAC using a rolling strategy - No down time (Rolling Patch)

Patching RAC as a single instance (All-Node Patch)

In this mode, all instances must be down during the whole patching process. OPatch applies the patch to the local node first, then propagates the patch to all the other nodes, and finally updates the inventory.

*    Shutdown all Oracle instances on all nodes

*    Apply the patch to the RAC home on all nodes

*    Bring all instances up

Patching RAC using a minimum down-time strategy (Min. Downtime Patch)

In this mode, OPatch patches the local node, asks users for a sub-set of nodes, which will be the first subset of nodes to be patched. After the initial subset of nodes are patched, Opatch propagates the patch to the other nodes and finally updates the inventory. The downtime would happen between the shutdown of the second subset of nodes and the startup of the initial subset of nodes patched.

*    Shutdown all the Oracle instances on node 1

*    Apply the patch to the RAC home on node 1

*    Shutdown all the Oracle instances on node 2

*    Apply the patch to the RAC home on node 2

*    Shutdown all the Oracle instances on node 3

*    At this point, instances on nodes 1 and 2 can be brought up

*    Apply the patch to the RAC home on node 3

*    Startup all the Oracle instances on node 3

Patching RAC using a rolling strategy - No down time (Rolling Patch)

With this method, there is no downtime. Each node would be patched and brought up while all the other nodes are up and running, resulting in no disruption of the system.

Rolling patching strategy incur no downtime, however, some rolling patches may incur downtime due to post-installation steps, i.e. running sql scripts to patch the actual database. Please refer to patch readme to find out whether post-installation steps require downtime or not.

*    Shutdown all the Oracle instances on node 1

*    Apply the patch to the RAC home on node 1

*    Start all the Oracle instances on node 1

*    Shutdown all the Oracle instances on node 2

*    Apply the patch to the RAC home on node 2

*    Start all the Oracle instances on node 2

*    Shutdown all the Oracle instances on node 3

*    Apply the patch to the RAC home on node 3

*    Start all the Oracle instances on node 3

The algorithm used to decide which method is going to be used is the following:

       If (users specify minimize_downtime)

              patching mechanism = Min. Downtime

       else if (patch is a rolling patch)

              patching mechanism = Rolling

            else

                  patching mechanism = All-Node 

When patches are released, they have a tag as "rolling" or "not rolling" patch. While most patches can be applied in a rolling fashion, some patches cannot be applied in this fashion.

Patches that could potentially be installed on rolling fashion include:

  

*    Patches that do not affect the contents of the database.

*    Patches that are not related to the RAC internode communication infrastructure.

*    Patches that change procedural logic and do not modify common header definitions of kernel modules.

*    This includes client side patches that only affect utilities like export, import, sql*plus, sql*loader, etc.

Note: If you plan to apply a patch that is marked as "not rolling" and want to check if is possible to take advantage of the rolling patch strategy, please contact Oracle Support.

How to determine if a patch is a "rolling patch" or not?

As database user execute the following:

    - 9i or 10gR1: opatch query -is_rolling

    - 10gR2: opatch query -all [unzipped patch location] | grep rolling

    - 10gR2 on Windows: opatch query -all [unzipped patch location] | findstr rolling

    - Later 10gR2 or 11g: opatch query -is_rolling_patch [unzipped patch location]

Also, the type of OPatch can be determined by checking the <Patch Number>/etc/config/inventory file. If the variable online_rac_installable is set to true then the patch is a rolling patch.

Here we are using 3rd method of applying patch i.e. “Rolling Patch”.

Patch 25476126 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170418

Platform:  Linux 64 Bit

Released: Apr 18, 2017

Some Important Notes:

Patch can be applied to both Oracle Database Home refers to Enterprise Edition or Standard Edition Database software.

The GI PSU patch includes updates for both the Clusterware home and Database home that can be applied in a rolling fashion.

This patch is Database Vault installable.

This patch is Data Guard Standby First Installable.

Data Guard Standby-First Patch Apply for details on how to remove risk and reduce downtime when applying this patch.

GI PSUs are cumulative and include the Database PSU and associated CPU program security content.

Check whether below components are configured in your environment.

Q: Is Audit Vault enabled in your database?

Ans: NO

Q: Is Dataguard configured in your environment?

Ans: NO

Note: If Dataguard is configured in your environment then apply the patch DR first.

Then you can start to apply patch to production DC.

Get the confirmation from client for Patching Activity at least a week prior activity.

Advantages:

·       To get total number of servers to be patched.

·       To download a patch.

·       To upload the patch to target servers.

·       To perform prerequisites.

·       To confirm client if you find any conflict in prerequisites.

·       To inform client that we are ready for patching if no conflict found.

Prerequisites Check:

1.     Apply patch in UAT and Development:

First apply patch in your UAT and development servers and then Production servers.

2.     Minimum Opatch Version:

You must use the OPatch utility version 11.2.0.3.6 or later to apply this patch.

Oracle recommends that you use the latest released OPatch version for 11.2 releases, which is available for download from My Oracle Support patch 6880880 by selecting ARU link for the 11.2.0.0.0 release. Replace the same with existing opatch version by taking backup of the current one.

$opatch version => It should be 11.2.0.3.6 or later.

              

3.     Download Patch:

Download latest PSU patch for the existing Operating System and correct Oracle Version.

For Example:

We have Oracle Linux 6.3 64-bit with Oracle 11.2.0.4 Enterprise Edition

Create new directory on target server and give read permission to the ORA_INST group and this directory must be empty because the PSU patch may include multiple patches and you may notice that there are multiple directories created with distinct bug numbers.

unzip p25476126_112040_<platform>.zip

4.     Perform prerequisites:

You will get any conflict if any after executing below commands. If you will not get any conflict then you are OK to go ahead.

As a grid user:

$opatch lsinventory –detail –oh $GRID_HOME

$opatch prereq CheckConflictAgainstOHWithDetail –ph ./

As an oracle user:

$opatchlsinventory –detail –oh $ORACLE_HOME

$opatch prereq CheckConflictgainstOhWithDetail –ph ./

Also execute below commands and get the inventory details.

As a grid user:

$opatch lsinventory –detail –oh $GRID_HOME

As an oracle user:

$opatch lsinventory –detail –oh $ORACLE_HOME

5.     Client Confirmation:

Initiate mail to client at least a week prior the Patching Activity with below detail stating that “We have completed patching prerequisites and we did not find any conflict”.

Sr. No.

Server

IP Address

Database

Instance

Grid Home

Oracle Home

Operating System

Bit Mode

Current Opatch Version

Required Opatch Version

Patch Number

Conflict

  

Start Here (Plan of Action)

We are applying latest PSU patch using Rolling Patch Method.

Note: We are following 3rd method of applying patching i.e. Rolling Patch and hence do not touch 2nd node while applying patch to 1st node. Once done with 1st node, then you can proceed with 2nd node.Also, this document is for Non-Shared Cluster Environment. For Cluster shared environment, there is a different method to apply patch.

Note: Initiate mail to client prior the activity that “We are starting patching activity in next 10-15 minutes”.

Step 1: Backup

Take tar backup of Both Grid and Oracle Home

By grid User:

$tar -cvzf /backup_location/TAR_BKP_Oracle_Binaries.tar.gz $ORACLE_HOME

By Oracle User:

$tar -cvzf /backup_location/TAR_BKP_Oracle_Binaries.tar.gz $GRID_HOME

Step 2: Data Capture

Capture below details prior the patching activity.

By Grid User:

$opatch lsinventory –detail –oh $GRID_HOME

$crsctl stat res –t

$crsctl check crs

$id

By Oracle user:

$opatch lsinventory –detail –oh $ORACLE_HOME

SQL>set lines 300 pages 3000

SQL>col action_time for a34

SQL>col version for a14

SQL>col comments for a28

SQL>col bundle_series for a28

SQL> col action_name for a23

SQL> col action for a10

SQL> col namespace for a9

SQL> select action,bundle_series,comments,action_time,version from registry$history;

$srvctl status database –d DC -v -f

$srvctl config database –d DC -v -f

$ps –ef | grep pmon

$ps –ef | grep tns

$id

Step 3: Stop Agent Process

You must stop the EM agent processes running from the database home, prior to patching the Oracle RAC database or GI Home

$ps –ef | grep agent

$cd $AGENT_HOME/bin

$./emctl status agent

$./emctl stop agent

 

Step 4: Stop Oracle services  

The below command will stop all Oracle services corresponds to mentioned Oracle Home. Please do not delete status.log file which will be required later to start the service.

 

By oracle user:

 

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 5: Unmount ACFS if available

Unmount the Oracle ACFS file systems. See My Oracle Support Document 1494652.1 for unmounting ACFS file systems.

In our environment, we are not using ACFS (ASM Cluster File System).

Step 6: Run pre-root script

Go to the GI Home location and execute below command as the root user:

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

The above script will perform the following actions before patching:

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

 

·       Shutdown of Oracle High Availability Services-managed resources on 'rac1'

·       To stop 'ora.crsd' on 'rac1'

·       Shutdown of Cluster Ready Services-managed resources on 'rac1'

·       To stop 'ora.LISTENER_SCAN3.lsnr' on 'rac1'

·       To stop 'ora.LISTENER.lsnr' on 'rac1'

·       To stop 'ora.registry.acfs' on 'rac1'

·       To stop 'ora.DATA.dg' on 'rac1'

·       To stop 'ora.oc4j' on 'rac1'

·       To stop 'ora.LISTENER_SCAN2.lsnr' on 'rac1'

·       To stop 'ora.cvu' on 'rac1'

·       Stop of 'ora.cvu' on 'rac1' succeeded

·       To start 'ora.cvu' on 'rac2'

·       Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac1' succeeded

·       To stop 'ora.scan3.vip' on 'rac1'

·       Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded

·       To stop 'ora.rac1.vip' on 'rac1'

·       Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac1' succeeded

·       To stop 'ora.scan2.vip' on 'rac1'

·       Start of 'ora.cvu' on 'rac2' succeeded

·       Stop of 'ora.scan3.vip' on 'rac1' succeeded

·       To start 'ora.scan3.vip' on 'rac2'

·       Stop of 'ora.registry.acfs' on 'rac1' succeeded

·       Stop of 'ora.rac1.vip' on 'rac1' succeeded

·       Start 'ora.rac1.vip' on 'rac2'

·       Stop of 'ora.scan2.vip' on 'rac1' succeeded

·       To start 'ora.scan2.vip' on 'rac2'

·       Start of 'ora.scan3.vip' on 'rac2' succeeded

·       To start 'ora.LISTENER_SCAN3.lsnr' on 'rac2'

·       Start of 'ora.rac1.vip' on 'rac2' succeeded

·       Start of 'ora.scan2.vip' on 'rac2' succeeded

·       To start 'ora.LISTENER_SCAN2.lsnr' on 'rac2'

·       Stop of 'ora.oc4j' on 'rac1' succeeded

·       To start 'ora.oc4j' on 'rac2'

·       Start of 'ora.LISTENER_SCAN3.lsnr' on 'rac2' succeeded

·       Stop of 'ora.DATA.dg' on 'rac1' succeeded

·       To stop 'ora.asm' on 'rac1'

·       Stop of 'ora.asm' on 'rac1' succeeded

·       Start of 'ora.oc4j' on 'rac2' succeeded

·       Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac2' succeeded

·       To stop 'ora.ons' on 'rac1'

·       Stop of 'ora.ons' on 'rac1' succeeded

·       To stop 'ora.net1.network' on 'rac1'

·       Stop of 'ora.net1.network' on 'rac1' succeeded

·       Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed

·       Stop of 'ora.crsd' on 'rac1' succeeded

·       To stop 'ora.drivers.acfs' on 'rac1'

·       To stop 'ora.crf' on 'rac1'

·       To stop 'ora.ctssd' on 'rac1'

·       To stop 'ora.evmd' on 'rac1'

·       To stop 'ora.asm' on 'rac1'

·       To stop 'ora.mdnsd' on 'rac1'

·       Stop of 'ora.crf' on 'rac1' succeeded

·       Stop of 'ora.evmd' on 'rac1' succeeded

·       Stop of 'ora.mdnsd' on 'rac1' succeeded

·       Stop of 'ora.ctssd' on 'rac1' succeeded

·       Stop of 'ora.asm' on 'rac1' succeeded

·       To stop 'ora.cluster_interconnect.haip' on 'rac1'

·       Stop of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

·       To stop 'ora.cssd' on 'rac1'

·       Stop of 'ora.cssd' on 'rac1' succeeded

·       To stop 'ora.gipcd' on 'rac1'

·       Stop of 'ora.drivers.acfs' on 'rac1' succeeded

·       Stop of 'ora.gipcd' on 'rac1' succeeded

·       To stop 'ora.gpnpd' on 'rac1'

·       Stop of 'ora.gpnpd' on 'rac1' succeeded

·       Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed

·       CRS-4133: Oracle High Availability Services has been stopped.

·       Successfully unlock /u01/app/11.2.0/grid

Step 7: Apply Grid Patch

Execute below command as a grid user:

$/u01/app/11.2.0/grid/OPatch/opatch napply -oh /u01/app/11.2.0/grid -local /home/oracle/25476126

Step 8: Run pre-script for Database Home

Run the below command by Oracle User:

 

$/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

 

/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh completed successfully.

Step 9: Apply DB Patch

Execute below commands by oracle user.

$/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch napply -oh /u01/app/oracle/product/11.2.0/dbhome_1 –local /home/oracle/25476126/23054319/custom/server/23054319

 

$/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch apply -oh /u01/app/oracle/product/11.2.0/dbhome_1 -local /home/oracle/25476126/24732075

Step 10: Run Post Script for Database Home

Run below command by oracle user.

 

$/home/oracle/25476126/23054319/custom/server/23054319/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Note: The above command will read the file permissions of below utilities from params.ora file, parse it, verify it and then finally reapply it.

Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..

Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/diskmon.bin

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnodes

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/osdbagrp

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/rawutl

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/ractrans

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/getcrshome

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/gnsd

Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/crsdiag.pl

Postpatch completed successfully

Step 11: Post Script for Grid

By Root User:

 

#/u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

 

 

Step 12: Post Root Script

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

 

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Installing Trace File Analyzer

 

 

CRS-4123: Oracle High Availability Services has been started.

Note: If you are using ACFS and the message “A system reboot is recommended before using ACFS” is shown then a reboot must be issued before continuing.

Failure to do so will result in running with an unpatched ACFS\ADVM\OKS driver.

Step 13: Start Oracle services  

The below command will start all Oracle services using status.log file corresponds to mentioned Oracle Home.

By oracle user:

 

$srvctl start home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 14: Post Patch Verification

Check everything is running fine on 1st node before applying patch to 2nd node.

By Grid User:

$crscrt check crs

$crsctl stat res –t

$opatch lsinventory –detail –oh $GRID_HOME

By Oracle User:

$srvctl status database -d DC -v -f

$opatch lsinventory –detail –oh $ORACLE_HOME

Check latest patch has been applied successfully to both GRID and Oracle Homes.

Apply patch to 2nd Node

Perform all steps from Step 1 to Step 15.

Step 1: Backup

Step 2: Data Capture

Step 3: Stop Agent Process

Step 4: Stop Oracle services  

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac2

Step 5: Unmount ACFS if available.

Step 6: Run pre-root script

Step 7: Apply Grid Patch

Step 8: Run pre-script for Database Home

Step 9: Apply DB Patch

Step 10: Run Post Script for Database Home

Step 11: Post Script for Grid

Step 12: Post Root Script

Step 13: Start Oracle services  

$srvctl start home -o $ORACLE_HOME -s status.log -n rac2

Step 14: Post Patch Verification

Step 15: Registry Update

The following steps load modified SQL files into the database. For an Oracle RAC environment, perform these steps on only one node.

The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle.sql psu apply

SQL> QUIT

 

Verify below logfiles for any errors.

catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log

catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log

 

Step 16: Upgrade Catalog

If you are using the Oracle Recovery Manager, the catalog needs to be upgraded.

$ rman catalog username/password@alias

RMAN> UPGRADE CATALOG;

RMAN> UPGRADE CATALOG;

 

 

You have successfully applied latest PSU patch to both Grid and Oracle Homes.

 

Rollback Plan

 

 

Execute the following manual steps on each node of the cluster in non-shared CRS and DB home environment to roll back the patches.

Step 1: Stop Oracle services  

The below command will stop all Oracle services corresponds to mentioned Oracle Home. Please do not delete status.log file which will be required later to start the service.

 

By oracle user:

 

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Step 2: Unmount ACFS if available

Unmount the Oracle ACFS file systems. See My Oracle Support Document 1494652.1 for unmounting ACFS file systems.

Step 3: Run pre-root Script

Go to the GI Home location and execute below command as the root user:

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

Step 4: Rollback Grid Infrastructure Patch

By Grid User:

$ /u01/app/11.2.0/grid/OPatch/opatch rollback -local -id 23054319 -oh /u01/app/11.2.0/grid

 

$ /u01/app/11.2.0/grid OPatch/opatch rollback -local -id 24732075 -oh /u01/app/11.2.0/grid

Step 5: Pre-script for Database Home

By Oracle User:

$/home/grid/25476126/23054319/custom/server/23054319/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Step 6: Rollback Oracle Database Patch

By Oracle User:

$opatch rollback -local -id 23054319 -oh /u01/app/oracle/product/11.2.0/dbhome_1

 

$opatch rollback -local -id 24732075 -oh /u01/app/oracle/product/11.2.0/dbhome_1

Step 7: Post Script for Database Component

By Oracle User:

 

$/home/grid/25476126/23054319/custom/server/23054319/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

Step 8: Run post root script

#/u01/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh

 

#/u01/app/11.2.0/grid/crs/install/rootcrs.pl -patch

 

Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Installing Trace File Analyzer

 

 

CRS-4123: Oracle High Availability Services has been started.

Note: If you are using ACFS and the message “A system reboot is recommended before using ACFS” is shown then a reboot must be issued before continuing.

Failure to do so will result in running with an unpatched ACFS\ADVM\OKS driver.

Step 9: Start Oracle services  

The below command will start all Oracle services using status.log file corresponds to mentioned Oracle Home.

By oracle user:

 

$srvctl start home -o $ORACLE_HOME -s status.log -n rac1

$ps –ef | grep pmon

$ps –ef | grep tns

$srvctl status database –d DC

$ps –ef | grep ORA_

Note: Verify everything is running fine on Node1 before rolling back patch on 2nd node.

Follow the steps of “Rollback Patch” from Step1 to Step9 on Node 2.

Step 1: Stop Oracle services  

$srvctl stop home -o $ORACLE_HOME -s status.log -n rac1

Step 2: Unmount ACFS if available

Step 3: Run pre-root Script

Step 4: Rollback Grid Infrastructure Patch

Step 5: Pre-script for Database Home

Step 6: Rollback Oracle Database Patch

Step 7: Post Script for Database Component

Step 8: Run post root script

Step 9: Start Oracle services  

Step 10: Start all database instances running from the Oracle home.

Step 11: Run catbundle Script

For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> STARTUP

SQL> @catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql

SQL> QUIT

In an Oracle RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.

$ opatch lsinventory

All other instances can be started and accessed as usual while you are executing the deinstallation steps.

You have successfully rolled back the Patch.

Note: Info On RAc Patching it may be differ from your environment production,testing,development directory structure,naming conventions etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Recovery From Loss Of Datafile For Which No Backup Is Available Using RMAN Utility

 

Recovery From Loss Of Datafile For Which No Backup Is Available Using RMAN Utility

SCENARIO – 10g Database Loss of datafile which has not been backed up.

CREATE NEW TABLESPACE TESTCHAITANYA

SQL> create tablespace testchaitanya datafile ‘/u02/oradata/testdb/testchaitanya.dbf’ size 25m;

Tablespace created.

TAKE A BACKUP OF THE DATABASE AT THIS POINT 

ADD DATAFILE TO TESTCHAITANYA TABLESPACE

SQL> alter tablespace testchaitanya add datafile ‘/u02/oradata/testdb/testchaitanya01.dbf’ size 25m;

Tablespace altered.

SIMULATE FAILURE BY REMOVING DATAFILES FOR TESTCHAITANYA TABLESPACE FROM DISK

Note: The tablespace TESTCHAITANYA has two datafiles, but only one has been backed up at this point in time

testdb:/u02/oradata/testdb> rm test*

testdb:/u02/oradata/testdb> sql

SQL> alter tablespace testchaitanya offline immediate;

Tablespace altered.

RESTORE DATAFILE 5; – The datafile which was backed up.

testdb:/u02/oradata/testdb> rman target / catalog rman11p/xxx@rcatp

Recovery Manager: Release 11.1.0.6.0 – Production on Thu AUG 20 09:19:28 2020

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

connected to target database: TESTDB (DBID=2358982414)

connected to recovery catalog database

RMAN> restore datafile 5;

Starting restore at 20/AUG/20

starting full resync of recovery catalog

full resync complete

Finished restore at 20/AUG/20

RMAN> restore tablespace testchaitanya;

Starting restore at 20/AUG/20

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_DISK_1

creating datafile file number=6 name=/u02/oradata/testdb/testchaitanya01.dbf >>> In 10g, Oracle creates the missing datafile .

skipping datafile 5; already restored to file /u02/oradata/testdb/testchaitanya.dbf

Finished restore at 14/MAY/09

RECOVER TABLESPACE TEST

SQL> recover tablespace testchaitanya;

Media recovery complete.

SQL> alter tablespace testchaitanya online;

Tablespace altered.

Note: Info on Recovery From Loss Of Datafile For Which No Backup Is Available Using RMAN Utility it may differ from your environment production,testing,development and mountpoints etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

bash_profile in Oracle RAC

 

Introduction

Bash_profile : Apart from the home directory to create and store files ,users need an environment to execute some of the tools and resources ,When a user logs in to a system the users work environment is determined by the initialization files ,these initialization files are defined by the users startup shell, The .bash_profile is a personal initialization file for configuring the user environment,the file is defined in your home directory and can be used modifying  your work environment by setting custom environment variables and terminal settings,and instruction the sytem to start up the application ,in this  we have two homes like GRID_HOME,and DB_HOME  to startup the services of the cluster application and rdbms application.

Verify Cluster services - status

#cd /u01/app/11.2.0/grid/bin

#./crsctl check cluster -all

su - oracle

vi .bash_profile

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

GRID_HOME=/u01/app/11.2.0/grid; export GRID_HOME

DB_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export DB_HOME

ORACLE_HOME=$DB_HOME; export ORACLE_HOME

ORACLE_SID=prod1; export ORACLE_SID

BASE_PATH=/usr/sbin:$PATH; export BASE_PATH

PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq!

$. .bash_profile

Now... create grid_env

$vi grid_env

ORACLE_SID=+ASM2; export ORACLE_SID

ORACLE_HOME=$GRID_HOME; export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq!

$vi db_env

ORACLE_SID=prod2; export ORACLE_SID

ORACLE_HOME=$DB_HOME; export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq!

$chmod +x db_env

$chmod +x grid_env

To login to grid - for asm instance

$. grid_env

$echo $ORACLE_SID

$echo $ORACLE_HOME

sqlplus "/as sysasm"

sql>

To login to DB

. db_env

sqlplus '/as sysdba'

Two home

Grid home

db home

Note : Info on bash_profile it may differ in your environment production,testing,development,and directories etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

TNS-12542 TNS Address Already in Use

 

Problem

While start the listener is prod database getting an error like  this TNS-12542: TNS:address already in use

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-AUG-2020 06:06:57

 

Copyright (c) 1991, 2017, Oracle.  All rights reserved.

 

Starting /oracle/app/oracle/product/12.1.0.2/dbhome/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/dbaclass-host/listener_prod/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chaitanya-host)(PORT=1524)))

TNS-12542: TNS:address already in use ---- >>>

TNS-12560: TNS:protocol adapter error

  TNS-00512: Address already in use

  Linux Error: 125: Address already in use

 

Listener failed to start. See the error message(s) above...

Solution

To find the solution we need to check the Listener file listener.ora

LISTENER_PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

 

    )

  )

 

SID_LIST_LISTENER_PROD =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PRODDB)

      (ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome)

    )

  )

Inside the listener file we have found that two address entries in the same host and same port number (1524) so starting the listener it will get the conflict so unable to start the database

(ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524))

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524))

To fix the error in give different port numbers for both the address entries

The listener will look like this after change the port numbers

LISTENER_PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1524)) --- >>>

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanya-host)(PORT = 1525)) --- >>>

 

    )

  )

 

SID_LIST_LISTENER_PROD =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PRODDB)

      (ORACLE_HOME = /oracle/app/oracle/product/12.1.0.2/dbhome)

    )

  )

Now start the listener 

# lsnrctl start LISTENER_PROD

 

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-AUG-2020 06:15:09

 

Copyright (c) 1991, 2017, Oracle.  All rights reserved.

 

Starting /oracle/app/oracle/product/12.1.0.2/dbhome/bin/tnslsnr: please wait...

 

TNSLSNR for Linux: Version 12.1.0.2.0 - Production

System parameter file is /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Log messages written to /oracle/app/oracle/diag/tnslsnr/chaitanya-host/listener_prod/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1525)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=chaitanya-host)(PORT=1524)))

STATUS of the LISTENER

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

Alias                     LISTENER_PROD

Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                20-AUG-2020 06:15:09

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/oracle/product/12.1.0.2/dbhome/network/admin/listener.ora

Listener Log File         /oracle/app/oracle/diag/tnslsnr/chaitanya-host/listener_prod/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1524)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chaitanya-host)(PORT=1525)))

Services Summary...

Service "PRODDB" has 1 instance(s).

  Instance "PRODDB", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

 

Listener started successfully.

Listener started sucessfully and listener listening in on both the ports 1524 and 1525 so ports should be unique for each address in the listener

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

 

ORA-00257 Archiver Error Connect Internal Only Until Freed

 ORA-00257: Archiver Error ,Connect Internal Only Until Freed

Problem

when an application users using database unable to connect to the database aplication log shows the error

ORA-00257:archiver error, connect internal only untill freed

ORA-16014:log1 sequence# 280 not archived,no available destinations

ORA-00312:online log1 thread 1:'/oradata/prod/redo01.log'

Solution

This error comes ,when the archive destination is full and there is no space availability in the destination location archivelogs

Check archivelog destination location

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /archive/PROD

Oldest online log sequence     14

Next log sequence to archive   18

Current log sequence           18

There are different solution for this Error rectification

Option-1:  Delete old archive logs to free up space using Rman utility

rman target /

delete archivelog all completed before 'sysdate-1';

Option-2: Change the archive log location 

incase you cannot  delete the archive logs from the destination location or existing location,then we can change the archive destination to some other mount point 

SQL>show parameter log_achive_dest;

NAME                     TYPE                     VALUE

log_archive_dest      string                  LOCATION =/archive/prod

SQL>alter  system set log_archive_dest= 'LOCATION=/u03/backupdest/archive/prod'  scope=both;

SQL>show parameter log_achive_dest;

NAME                     TYPE                     VALUE

log_archive_dest      string                  LOCATION =u03/backupsest/archive/prod

switch log file

alter sytem switch logfile;

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME