Oracle Database 19c Features

 Oracle Database 19c Features

Introduction

Oracle Database 19c is the long term release of the Oracle Database 12c and 18c family of products,it is available on all platformsWindows,Linux,Solaris,HP/UX and AIX as well as the Oracle cloud. Oracle Database 19c offers customers the best performance,scalibility,reliability, and security for all their operational and analytical workloads

Installation

Rpm based Installation install oracle 19c datbase using RPM method

Simlified image based installation of client as well

Upgrades

Auto Upgrade Utility for oracle Database

Docker Container for oracle 19c

Dryrun mode for Gridsetup in clusterware installation

General

Clear Flashback logs from time to time

Passwords removed from user accounts (default accounts)

Flush Metadata Cache for passwords

Multi-model partitioning with hybrid partioning allowing some partitions in database and some as external partitions even in HDFS

New ALTER SYSTEM  statement clause FLUSH PASSWORDFILE_METADATA_CACHE

Hybrid  Partitioned tables - to integerate internal partitions and external partitions  into a single partition table. partitions to reside in both oracle database segments and in external files and sources

Schema-only accounts -Passwords Removed from oracle database accounts  

Database Performance

SQL Quarantine - using Oracle's Resource manager tool is a great way to make sure SQL statements dont become resource hogs and slow down database performance everyone,if a system asks for more system resorces than the DBA allows ,Resources manager kills it,However in existing versions of oracle database,nothing stops users from executing problematic SQL statements again. In oracle 19C ,Resource manager can automatically quarantine the statements, user try to issue once again it wont be run at all

Automatic Indexing

This new feature puts oarcale automation capabilities to work.if oracle 19c thinks a database table would be benifit from an index,the system will automatically create the index and initially mark it as invisible so it cant be used .oracle 19c will then run SQL statements  from your application to see if the index improves query execution you can control this feature  with DBMS_AUTO_INDEX< a new PL?SQL package that's included in 19c

SQL Statement Diagnosability 

SQL Statement Diagnosability with SQL Advisor repair and SQL Test case for procedures

Automatic Database Diagnostic Monitor(ADDM)

ADDM supports for pluggable Database (PDB'S)

Realtime  Statastics For DML Operations

Oracle database 19c intoduces real time statastics which extend online support to conventional DML statements

Automatic Flashback of Standby Database

in prior versions DBA's wanted touse oracle flashback features to return  aprimary database to previous state,In oracle 19c ,a DBA can put the standby database in MOUNT mode with no managed recovery and then flashback the primary one ,the standby will aslo be reverted,thus keeping it in sync with the primary Statistics Collection on custom frequency automatically From 19c database onwards ,High frequency automatic optimizer statastics collection complements the standard statasticscollection job

DataPump

Oracle data pump test mode for transportable tablespace(TTS)

Oracle data pump allows tablespace to stay read -only during TTS import

Oracle data pump import supports more object store credentials

Oracle data pump ability to exclude ENCRYPTION clause on import-new transform  parameter OMIT_ENCRYPTION_CLAUSE

Oracle data pump support for resource usage limitations_new parameter MAX_DATAPUMP_PARALLEL_PER_JOB

Oracle data pump prevents inadvertent use of protected roles-new ENABLE_SECURE_ROLES parameter is available

Oracle data pump loads partitioned table data one operation-GROUP_PARTITION_TABLE_DATA,  new value for the import DATA_OPTIONS Command line paramaeter

Pluggable Databases

Create Duplicate of an oracle database create duplicatedb command , in DBCA silent mode

Ability to relocate a PDB to another CDB using DBCA in silent mode

Create a PDB by cloning a remote PDB using DBCA in silent mode

ADDM Analysis at PDB level

Data Guard

Replicate Restore points from primary to standby

Dynamically change  fast-start- failover (FSFO) target standby database to another standby database in the target list without disabling FSFO

Re-creation of broker configuration

Propagate restore points from primary to standby site

DML redirect to standby/ADG for read mostly applications

Simplified Dataguard broker parameter configurations

Observe only mode for data guard broker fast-satrt failover (FSFO)

Oracle dataguard multi-instance redo apply works with the in-memory column store

Finer granularity supplemental logging for logical standby databases

New Initialization Parameters in Oracle Database 19c 

"-optimizer_gather_stats_on_conventional_dml" and " _optimizer_use_stats_on_conventional_dml" which are true by default

-optimer_stats_on_conventional_dml_sample_rate(at 100%)

DATA_GUARD_MAX_IO_TIME

DATA_GUARD_MAX_LONGIO_TIME

MAX_DATAPUMP_JOBS_PER_PDB

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

 

Flashback technology Recover a Dropped User in Oracle

Flashback technology  Recover a dropped user in oracle

Introduction

Using Flashback Technology  we can restore the dropped user in oracle we will flashback the database to past when the user is available at the time of dropped before,

Then take the export dump of the schema and restore the database to same current state once database is up we can import the dump file  

Prerequisites

1. Database must be Archivelog mode

2.Flash back must be enable for the database

3.All the flashback log and  Archive log should be available from the time the user is dropped 

Let us start the process

1.Make sure flashback and archive mode is enable.

SQL> select flashback_on,log_mode from v$database;

 

FLASHBACK_ON       LOG_MODE

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

YES                                 ARCHIVELOG

2. lets drop the user and test the scenarios

04:47:15 SQL> select table_name from Chaitu_table where owner='CHAITANYA';

 

TABLE_NAME

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

ACCTTABLE1

MASTERTABLE2

 

 

04:47:33 SQL> drop user CHAITANYA cascade;

 

User dropped.

3.Flashback the database past when the user was available at that time

04:52:15 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

04:52:50 SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1.1107E+10 bytes

Fixed Size                  7644464 bytes

Variable Size            9294584528 bytes

Database Buffers         1711276032 bytes

Redo Buffers               93011968 bytes

Database mounted.

 

04:53:08 SQL> flashback database to timestamp to_date('20-AUG-2020 04:47:33','DD-MON-YYYY HH24:MI:SS');

 

Flashback complete.

4. Open the database in readonly mode

04:55:13 SQL> ALTER DATABASE OPEN READ ONLY;

 

Database altered.

 

04:55:31 SQL>  select table_name from chaitu_tables where owner='CHAITANYA';

 

TABLE_NAME

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

ACCTTABLE1

MASTERTABLE2

we can see the tables are available now

5. Take export backup of the schema CHAITANYA

# exp owner=CHAITANYA file=chaitanya.dmp

 

Export: Release 12.1.0.2.0 - Production on Tue Aug 20 05:17:45 2020

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

 

Username: / as sysdba

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

 

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user CHAITANYA

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user CHAITANYA

About to export CHAITANYA's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export CHAITANYA's tables via Conventional Path ...

. . exporting table                           ACCTTABLE1     75341 rows exported

EXP-00091: Exporting questionable statistics.

. . exporting table                          MASTERTABLE2        44 rows exported

EXP-00091: Exporting questionable statistics.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

6. Now restore the database to current state

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 1.1107E+10 bytes

Fixed Size                  7644464 bytes

Variable Size            9294584528 bytes

Database Buffers         1711276032 bytes

Redo Buffers               93011968 bytes

Database mounted.

 

SQL> recover database;

Media recovery complete.

 

SQL> alter database open;

 

Database altered.

7. create the empty user and import the dumpfile

SQL> create user chaitanya identified by chaitanya;

 

User created.

 

SQL> grant connect,resource to chaitanya;

 

Grant succeeded.

 

# imp file=chaitanya.dmp fromuser=CHAITANYA TOUSER=CHAITANYA

 

Import: Release 12.1.0.2.0 - Production on Tue Aug 20 05:23:59 2020

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

Username: / as sysdba

 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

 

Export file created by EXPORT:V12.01.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses AL32UTF8 character set (possible charset conversion)

. importing DBACLASS's objects into DBACLASS

. . importing table                        " ACCTTABLE1 "      75341 rows  imported

. . importing table                        "MASTERTABLE2"          44 rows imported

 we can restore the schema user chaitanya by using flashback technology

Note : Info on Flashback technology it may be differ in your environment like production,testing ,development and naming conventions 

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

 

Recycle Bin in Oracle Database

 Recycle Bin in Oracle Database

Introduction

In windows  have a recycle bin to all deleted files will be store like wise in oracle database also 

provided recycle bin which keeps all the dropped objects.

When we drop a table (DROP TABLE TABLE_NAME) in the database , The tables will logically be

 removed but it still exists in the same tablespace 

but with a prefix BIN$$ .And it will not release the space also

Note : The Recycle bin which will not work sys owned objects it will worked on user objects only

If we drop a table using purge command, tables willbe removed completely (even from recycle bin also)

How to check the recycle bin is on or off

1.SQL> show parameter recyclebin;

 

NAME                                 TYPE        VALUE

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

recyclebin                           string      on

 

SQL> select name,value from v$parameter where name like '%recyclebin%';

 

NAME         VALUE

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

recyclebin   on

2.Drop a table and check the table is there in the recycle bin or not  

SQL> drop table chaitanya.CHAITUTABLE;

 

Table purged.

 

SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';

 

OWNER              OBJECT_NAME                                   ORIGINAL_NAME      DROPTIME            CAN

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

CHAITANYA           BIN$fxhnqWVcPLTgVAAQ4B8y7Q==$0                CHAITUTABLE         2020-01-10:12:45:03 YES

Now the table is in recycle bin we can recover the table if required

3. For purging the table for recyclebin

  in order to remove the table from recyclebin also

SQL> purge table chaitanya.CHAITUTABLE;

 

Table purged.

 

SQL>  select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';

 

no rows selected

4. To purge complete recyclebin

SQL> select count(*) from dba_recyclebin;

 

  COUNT(*)

----------

       102

 

SQL> purge recyclebin;

 

Recyclebin purged.

 

SQL>  select count(*) from dba_recyclebin;

 

  COUNT(*)

----------

         0

5. To drop a table without keeping in recyclebin

SQL> select count(*) from CHAITANYA.chaitusample;

 

  COUNT(*)

----------

     82269

 

SQL> drop table CHAITANYA.CHAITUSAMPLE purge;

 

Table dropped.

 

SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUSAMPLE';

 

no rows selected

Note : Info on Recycle bin in oracle it may be differ in your encironment like production, testing,development and naming conventions 

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

Hot and Cold Backup in Oracle

 Hot and Cold Backup in Oracle

Introduction

In Oracle basically we use either offline or Online backups, Offline backup is called as COLD backup and online backup is called HOT backup

Backups are two types---> Physical ------>Files(Control file,Data Files,Redolog files)

                                     |---->Logical--------->Data

These Data/Files need to protect from any disaster for that we need to manage backups

Physical Backup: physical backups will have interms of Files using Hot and Cold backup we can take backups

Cold Backup:

Cold backup is a physical backup in which all the files of a  database are copied without any change means it avoids the risk of copying data and hence the image copy is 

can be easily moved to another system having the same operating system,it is the safest way of take back up of All data in the database that is controlfile(.ctl files),Datafiles(.dbf files)

and Redolog files(.logfiles) Cold back are not recommended to take backup for larger databases it will take more time and space consumption client will not accept down time

Process

While taking cold backup 

Shutdown the instances

Complete full consistent backup

Traditional backup

Steps

1. Shutdown instances

2.Copy the Physical files using unix cp command

Note: /orabackup/prod/cold

$ mkdir -p /orabackup/prod/cold

shutdown immediate

Taking db files backup

$ cp /oradata/prod/*.dbf         /orabackup/prod/cold

or

$ cp /oradata/prod

$cp * /orabackup/prod/cold

Taking Control files backup

$ cp /oradata/prod/*.ctl        /orabackup/prod/cold

or

$ cp /oradata/prod

$cp * /orabackup/prod/cold

Taking redolog files backup

$ cp /oradata/prod/*.log        /orabackup/prod/cold

or

$ cp /oradata/prod

$cp * /orabackup/prod/cold

Hot Backup

Hot backup is also physcial backup including the risk of copying data because during the hot backup the database is totally open and available for users that is online.when a full backup of database is required for the hotbackup

 database must be running in the ARCHIVELOG mode, 

Is a inconsistent backup

is incomplete backup

is  a traditional backup

while hot backup ,database must be up and running also database be in archivelog mode,while in hot backup mode ,The database must be freezed can read but cannot write into datafiles so the changes are written into archivelog files tills ends,once the backup ended ,the changes are recovered into datafiles from archivelog

Process

1.archivelog mode

2.begin backup mode

3.copy the files

4.end backup

1. SQL> archive log list

2. SQL> select files #,status from v$backup;

3. SQL> alter database begin backup;

4. $ cp /oradata/prod/*.dbf   /orabackup/prod/hot

5.SQL> alter database end backup

6 . SQL> alter database  backup controlfile to '/orabackup/prod/hot/control01.bkp' ;

Note : Info on hot and cold backup it may be differ in your environment like production,testing,development and directory structures and naming conventions

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

Oracle DBA Online Test(MCQ) Intermediate Level

 

Oracle Network Configuration Files

Oracle Network Configuration Files 

Introduction:

To communicate with other systems (server machines to client machines or client machines  to server machines)  we need three network configuration files in order to communicate with the systems  in oracle  three files need to configure that is listener.ora,tnsnames.ora,sqlnet.ora,oracle net enables a network connection between a client and database server oracle net is a software component that resides on both the client  and the database server.oracle net is layered on top of a network protocol -rules that determine how applications access the network and how data is subdivided into packets for transmission across the network . 

The example files below are relevant for an Oracle installation and instance with the following values.

• HOST : chaitanyaoracledba.blogspot.com

• ORACLE_HOME : /u01/app/oracle/product/12.1.0.2/db

• ORACLE_SID : orcl

• Service : orcl

• DOMAIN : blogspot.com

listener.ora

The "listerner.ora" file contains server side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the server. Here is an example of a basic "listener.ora" file from Linux. We can see the listener has the default name of "LISTENER" and is listening for TCP connections on port 1521. Notice the reference to the hostname "chaitanyaoracledba.blogspot.com". If this is incorrect, the listener will not function correctly.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =      

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

  )

After the "listener.ora" file is amended the listener should be restarted or reloaded to allow the new configuration to take effect.

$ # Restart

$ lsnrctl stop

$ lsnrctl start

$ # Or Reload.

$ lsnrctl reload

The listener defined above doesn't have any services defined. These are created when database instances auto-register with it. In some cases you may want to manually configure services, so they are still visible even when the database instance is down. If this is the case, you may use a "listener.ora" file like the following.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl.blogspot.com)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db)

      (SID_NAME = orcl)

    )

  )

If there are multiple database instances on the server, you can added multiple SID_DESC entries inside the SID_LIST section.

tnsnames.ora

The "tnsnames.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself. Here is an example of a "tnsnames.ora" file.

LISTENER = (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

orcl.blogspot.com =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = chaitanyaoracledba.blogspot.com)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

The alias used at the start of the entry can be whatever you want. It doesn't have to match the name of the instance or service. Notice the PROTOCOL, HOST and PORT match that of the listener. The SERVICE_NAME can be any valid service presented by the listener. You can check the available services by issuing the lsnrctl status or lsnrctl service commands on the database server. Typically there is at least one service matching the ORACLE_SID of the instance, but you can create more.

sqlnet.ora

The "sqlnet.ora" file contains client side network configuration parameters. It can be found in the "$ORACLE_HOME/network/admin" directory on the client. This file will also be present on the server if client style connections are used on the server itself, or if some additional server connection configuration is required. Here is an example of an "sqlnet.ora" file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

NAMES.DEFAULT_DOMAIN = blogspot.com

# The following entry is necessary on Windows if OS authentication is required.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

There are lots of parameters that can be added to control tracing, encryption, wallet locations etc. These are out of the scope of this article.

Testing

Once the files are present in the correct location and amended as necessary the configuration can be tested using SQL*Plus by attempting to connect to the database using the appropriate username (SCOTT), password (TIGER) and service (orcl.blogspot.com).

$ sqlplus scott/tiger@orcl.blogspot.com

Common Problems

The OS hostname command must return the correct hostname of your database server. If not, fix it so it does.

$ hostname

chaitanyaoracledba.blogspot.com

$

The server must have a correct entry in the "/etc/hosts" file matching the hostname and IP address of the server, as well as the loopback entry for localhost. For example in this case the values are as follows.

127.0.0.1      localhost localhost.localdomain localhost4 localhost4.localdomain4

192.168.0.100  chaitanyaoracledba.blogspot.com  chaitanyaoracledba

 If these are provided by DNS, that is fine also.

If you are using the ORACLE_HOSTNAME environment variable, possibly set in your "/home/oracle/.bash_profile", it must be set to the correct value.

export ORACLE_HOSTNAME=chaitanyaoracledba.blogspot.com

The HOST entry in the "listener.ora" file must point to an active network adapter, either real or loopback. If not, the listener will fail to start.

If the HOST entry in the "listener.ora" file is set to "localhost", the listener will start and accept connections from the local server, but not from other clients. You would typically expect this to be set to the hostname of the database server, although some people use the IP address instead.

For the client to make a connection via the listener, there must be a clear route through the network between the two machines. If you are struggling to connect, check network firewalls and the local firewall (iptables, firewalld, Windows Firewall) on the database server.

Tools for Network configuration

Oracle enables you to manage your network configuration with the following tools

Oracle Net Configuration Assistant

Enterprise manager

Oracle Net Manager

Oracle Net Configuration Assistant:  The oracle universal installer launches Oracle Net Configuration Assistant after the database is installed use oracle net configuration assistant to configure the listening protocol address and service information for an oracle database,during a typical database install,oracle net configuration assistant automatically configures a listener called LISTENER that has TCP/IP listening protocol address for the database.Oracle Net Configuration assistant prompts you to configure a listener name and protocol address of your choice.

use Oracle Network Configuration Assistant for initial network configuration after database installation there after you can use the Oracle Enterprise Manager and Oracle Net Manager to configure and administer your network

Oracle Net Manager:

You can access Oracle Net Manager from the command line or for windows platforms through the start menu

 command line (Unix,Linux or windows )run netmgr

on windows click the start button and select programs,oracle-oracle_home,configuration and migration tools, and then Net Manager  

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

RAC Startup Sequences

 RAC Start up Sequence

1.Brief startup sequence

2.Number of Levels while RAC startup

3.In which Level ,The clusterware services get started

4.In which Level database resources are get started in RAC

Oracle  Restart component of a clusterware monitor and manage clusterware resources in a cluster services/daemon involved to run RAC 

Two stack

Lower stack ------->Oracle high availability---->level 0

Upperstack --------->Clusterware processes

Finally we need to get started the resources are network,Scan,vip,listener,DB services

Level 0----->Init---->Ohasd

Level1------>Agents--->orarootagent and oraagent

Level2------->Clusterware services---->crsd,cssd,evmd,procd

Level3-------->Agents

Level4--------->Resources---->DB,Listener,ASM,Instances,VIP,SCAN

Cluster Ready Services(CRSD): Primary program for managing high availability operations in a cluster,operations included start,stop,monitor and failover operations

Cluster Synchronization Services(OCSSD): Manages node memebership information when node joins or leaves the cluster

Event Management (EVMD): A background process that publishes events that oracle clusterware creates

Oracle Notification Services(ONS): A publish and subscribe for communicating fast application notification(FAN) events

Oracle ProcessMonitor DAEMON(OPROCD): The OPROCD periodically wakes up and checks that the interval since it last awake is with in the expected time.If not then

OPROCD resets the processor and restarts the node,an OPROCD failure results in oracle clusterware restarting the node

# ./crsctl check crs

# ./crsctl check cssd

RAC -TOOLS

crsctl--->root user-->cluster ready service control utility

srvctl--->oracle user-->service control utility

To find the resources status

# ./crs-stat-t

or

# ./crsctl stat res -t

Note: On server boot the clusterware services and resources are started on it own

Still to start manually

First start OHASD--on all nodes manually on rac1,rac2

 ./crsctl start crs

./ crsctl check cluster -all

./crsctl check crs

on any node --can start services of every node on rac1

./crsctl start cluster -all

./crsctl check crs

stopping services on rac1

./crsctl stop cluster -all

./crsctl check cluster -all

on every node rac1,rac2

./crsctl stop crs

./crsctl check crs

How to find the health of the cluster

./crsctl check -all

How to start/stop the clusterware services 

verify the instances up and running  on rac nodes 

$ srvctl status -d database prod

/etc/inittab

In 11g r2 - only one entry will see.

/etc/init.d/init.ohasd run > /dev/null 2>&1 </dev/null

INIT process first spawns init.ohasd process which in turn startsup the Oracle High Availability services Daemon(OHASD).

In turn, OHASD spawns additional clusterware processes at each startup level.

Level 1 - OHASD spawns

cssd agent - cssd

root agent - all root owned ohasd resources.

oraroot agent - oracle owned ohasd resources.

cssdmonitor - Monitors CSSD and node health

Level 2—OHASD rootagent spawns:

        Cluster Ready Services Daemon (CRSD)—

primary daemon responsible for managing cluster resources

        Cluster Time Synchronization Services Daemon (CTSSD)

        Diskmon—provides disk monitoring services

        ASM Cluster File System (ACFS) Drivers

During the second level of startup for Clusterware, the oraagent spawns the following Clusterware processes for 11g R2:

    MDNSD: Used for DNS lookup

    GIPCD: Used for inter-process and inter-node communication

    GPNPD: Grid Plug and Play Profile Daemon

    EVMD: Event Monitor Daemon

    ASM: Resource for monitoring ASM instances

Level 3—CRSD spawns:

    orarootagent: for managing all root-owned CRSD resources

    oraagent:   for managing all Oracle-owned CRSD resources

    Level 4—CRSD rootagent spawns:

        Network resource: To monitor the public network

        SCAN VIP(s): Single Client Access Name Virtual IPs

        Node VIPs: One per node

        ACFS Registery: For mounting ASM Cluster File system

        GNS VIP (optional): VIP for GNS

During this phase for Clusterware startup with 11g R2, the oraagent spawns the following processes:

    ASM Resouce: ASM Instance(s) resource

    Diskgroup: Used for managing/monitoring ASM diskgroups

    DB Resource: Used for monitoring and managing the DB and instances

    SCAN Listener: Listener for single client access name, listening on SCAN VIP

    Listener: Node listener listening on the Node VIP

    Services: Used for monitoring and managing services

    ONS: Oracle Notification Service

    eONS: Enhanced Oracle Notification Service

    GSD: For 9i backward compatibility

    GNS (optional): It is a grid naming service that performs name resolution

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

Oracle SQL Tuning

 

SQL Tuning

SQL is running slow

Database is running slow.

Tuning - to improve performance of the database to run faster.

Goal to get tune?

Good Throughput - CPU

Best response time/elapsed time/execution time

CPU Elapsed Time

100% 15min   Worst

1% 1s Best Performance

Using Cost based Optimization , the cost is defined on

CPU and Response time.

Now we to identify the cost the sql.

What happens when SQL Statement been executed?

Three stages

Parser - does check syntax and semantic analysis

Fetch -

Execute - Will execute the plan and send result to user

Fetch : OPtimizer will choose best shortest execution plan using

cost based optimization with dictionary stats.

The plan received by Row Source generator.

then fetch the records

note: Dictionary stats

Number of rows,columns,indexes,row length.

There are many factors dependent on slowness of performance.

Backup jobs - RMAN/DP

DML Bulk Jobs -Developers - last night

Memory - AMM -

Network

Poor Coding(procedures/Packages)

Indexes - fragmentation -

Stats

storage

multiplexing - same disk

Dictionary stats

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

Dictionary stats are

number of rows,row length,columns,indexes

If there are DML changes in the last night with 1 million

records are deleted. Will optimizer knows the stats currently

got deleted ? No

These stats to be collected manually every day night. So with

the updated stats, the optimizer will generate best execution

plan

sql>EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>'SCOTT',CASCADE=>TRUE);

How we identify the stats gathered?

    from dba_tables (last_analyzed)

sql>select table_name,last_analyzed from dba_tables where owner like

'SCOTT';

If someone complained that query running slow,no changes

did on the query. Verify gather stats.

Purpose of Tuning?

What happens while sql query executed?

What are goals to tune?

What is optimizer?

How to collect/gather stats? and purpose?

How to check stats gathered?

Optimizer - can control using parameter

---------

Will choose the shortest exection plan.

using parameter optimizer_mode

can set the value to all_rows/first_rows_n

n is multiples of 10

sql>alter system set optimizer_mode='first_rows_10' scope=both;

default all_rows

sql>show parameter optimizer_mode

Manually check the execution plan using cost based optimization.

with tools. To identify the cost of plan for a query

explain plan

or

auto trace

1. Auto trace ( introduced from 10g)

sql>set autotrace traceonly

sql>select * from scott.emp;

sql>set autotrace off

2. using explain plan tool ( Being still used)

will store execution plan in plan_table

 sql>explain plan set statement_id='j1' for select * from scott.emp;

sql>@?/rdbms/admin/utlxpls.sql

How to identify the load on the server ?

What is causing slow on the server?

Using unix top command

will identify the load average and top pid consuming more CPU and mem.

$top

Load average  < 10 (best performance)

Idle - CPU idle - No activity - > 70%

Find PID based on CPU/Mem of oracle commands.

using pid from v$session,v$process can find the sid,serial#,sqlid,username

machine,terminal,program,module

Top

pid

sid,serial from v$process,v$session

using sid

find sql_text from v$sql_area

then use explain plan tool

to find the cost and factor causing slow.

If found rman / dp job... impacting db..100% kill it.

sql>alter system kill session '&sid,&serial#'immediate;

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME

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