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