RMAN COMMANDS IN ORACLE

RMAN COMMANDS IN ORACLE 

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

rman target /

rman> show all

rman >backup database plus archivelog;

rman>shutdown immediate;

rman>startup force dba;

rman>shutdown immediate;

rman>startup mount;

rman>backup database;

rman>backup as copy database;

rman>alter database open;

RMAN> BACKUP DATABASE;

RMAN> BACKUP ARCHIVELOG ALL;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Note that the PLUS ARCHIVELOG clause performs the following:

Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command

Runs the BACKUP ARCHIVELOG ALL command. If backup optimization is enabled only backs up logs that have not already been backed up.

Backs up files specified in the BACKUP command

Runs the ALTER SYSTEM ARCHIVE LOG CURRENT command

Backs up any remaining archived logs including those generated during the backup

A FORMAT can be specified for each subclause in the BACKUP DATABASE PLUS ARCHIVELOG command. For example:

RUN

{

  ALLOCATE CHANNEL chan01 TYPE DISK MAXPIECESIZE 10G;

  BACKUP

  FORMAT '/u03/app/oracle/PROD/%d_D_%T_%u_s%s_p%p'

  DATABASE

  PLUS ARCHIVELOG

  FORMAT '/u03/app/oracle/PROD/%d_A_%T_%u_s%s_p%p';

  RELEASE CHANNEL chan01;

}

Note that FORMAT clause precedes the DATABASE clause, but follows the PLUS ARCHIVELOG clause.

The BACKUP command can be extended to backup the current control file and the SPFILE.

For example:

RUN

{

  ALLOCATE CHANNEL chan01 TYPE DISK MAXPIECESIZE 10G;

  BACKUP

  FORMAT '/u03/app/oracle/PROD/%d_D_%T_%u_s%s_p%p'

  DATABASE

  CURRENT CONTROLFILE

  FORMAT '/u03/app/oracle/PROD/%d_C_%T_%u'

  SPFILE

  FORMAT '/u03/app/oracle/PROD/%d_S_%T_%u'

  PLUS ARCHIVELOG

  FORMAT '/u03/app/oracle/PROD/%d_A_%T_%u_s%s_p%p';

  RELEASE CHANNEL chan01;

}

RMAN> BACKUP CURRENT CONTROLFILE;

RMAN> BACKUP SPFILE;

RMAN> BACKUP DATAFILE '/u01/app/oradata/PROD/users01.dbf';

RMAN> BACKUP DATAFILE 4;

Controlfile backups

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

The current controlfile can be automatically backed up by the BACKUP command by configuring the CONTROLFILE AUTOBACKUP parameters

To backup the current controlfile explicitly use:

RMAN> BACKUP CURRENT CONTROLFILE;

SPFILE backups The SPFILE can be automatically backed up with the control file during database backups by configuring the CONTROLFILE AUTOBACKUP parameters

To backup up the SPFILE explicitly use:

RMAN> BACKUP SPFILE;

Datafile backups To backup a specific data file use BACKUP DATAFILE. For example:

RMAN> BACKUP DATAFILE '/u01/app/oradata/PROD/users01.dbf';

Altermatively specify the data file number. For example:

RMAN> BACKUP DATAFILE 4;

The data file number can be obtained from V$DATAFILE. For example:

SQL> SELECT file#, name FROM v$datafile;

Tablespace Backups

To backup a tablespace use the BACKUP TABLESPACE command. For example:

RMAN> BACKUP TABLESPACE USERS;

Compressed Backups

To compress the backup use:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE;

The resulting compressed backup is around 20%-30% of the size of the uncompressed equivalent.

Format clause

The format clause allows the backup files to be directed to a specific location.

For example:

BACKUP FORMAT '/u01/app/oracle/backup/%U' DATABASE;

The above statement created the following files in /u01/app/oracle/backup:

[oracle@vm3]$ ls -l /u01/app/oracle/backup

total 1161280

-rw-r----- 1 oracle oinstall 1178050560 Jul 31 06:31 15qeibgs_1_1

-rw-r----- 1 oracle oinstall    9928704 Jul 31 06:31 16qeibld_1_1

Other formats can be specified. For example:

BACKUP FORMAT '/backup2/PROD/PROD_df_%t_s%s_p%p' DATABASE;

In the above example %t is the backup set timestamp, %s is the backup set number and %p is the piece number within the backup set.

Tags:A backup tag can optionally be specified with the BACKUP command.

For example:

BACKUP DATABASE TAG = 'Full_Backup';

The tag is reported by the LIST command.

If a tag is not specified then a system-generated tag is assigned.

Incremental Backups

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

By default backups are full (level 0). Backups can also be incremental (level 1).

Incremental backups can be:

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

Differential - includes all changes since the last full or incremental backup

Cumulative - includes all changes since the last full backup

Differential backups require less space. Cumulative backups are faster to restore

Differential backups are the default.

To run a diffential incremental backup use:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

To run a cumulative incremental backup use:

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Note that in order to take a level 1 backup, a level 0 backup must already exist.

A full backup using BACKUP DATABASE is not the same as a level 0 backup. - the LV column of the LIST BACKUP output is NULL after a full backup.

In order to take a level 0 backup use

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

To create an image copy of an entire database use:

RMAN> BACKUP AS COPY DATABASE;

To create an image copy of a specific datafile use:

RMAN> BACKUP AS COPY DATAFILE <file#>

For example:

RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/u01/app/oracle/copy/users01.dbf';

Alternatively specify the source file name. For example:

RMAN> BACKUP AS COPY DATAFILE '/u01/app/oradata/PROD/users01.dbf'

FORMAT '/u01/app/oracle/copy/users01.dbf';

Recovery Area

To backup the recovery area use:

RMAN> BACKUP RECOVERY AREA TO DESTINATION '/u02/app/oracle';

Note that a destination must be specified if the recovery area is being backed up to disk.

Note : Info on Rman commands it may be differ in your environment like production,development,testing

THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME

OEM ORACLE MONITORING TOOL

 OEM ORACLE MONITORING TOOL

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

ORACLE ENTERPRISE MANAGER (OEM) ORACLE MONITORING TOOL

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

Introduction: 

 Oracle Enterprise Manager is a set of web based tools aims at managing software and hardware produced by oracle corporation as well as by non -oracle entitie  OEM is a system management tool which provides an integrated solution for managing your heterogenous environment,it combines a graphical console,agents,common services,and tools to  provide an integrated comprehensive systems management platform for managing oracle products.

           In traditional one  Oracle enterprsise manager in each databases one single enterprise manager in all the database,agent is the mediator for OEM and databases they will constantly monitor The database ,one single web URL which is used to login for entire monitoring environment all your targeted databases,they come with 12c cloud technology,12c OEM has a repository database will hold all the data for agent 

1) Monitor other vendor databases also

2)Host monitoring

3)Generate all performance report from OEM itself like AWR at consolidate at one place

4)Schedule jobs

5)Monitoring Templates like any tablespace reach 90% thresh holdvalue set to 90% usage you will get the notify (Templates),you choose what the template you want apply to the target databases

6)Groups-->Target Groups :groups in 12c oem the entire group the entire job will be done in target groups like shutting down the database running queries

7) Monitor the RAC setup

8)Monitor the weblogic 

9)Monitor the fusionware

10)Monitor the other vendors

11)we can configure thresh holds critical medium in the oem itself

12)Mount point utilization 

13)we can take backup using the own scripts 

14)checking blocking session is easy in OEM (performance tuning)

15)all the reports can be generated in OEM like (AWR,ADDM,ASH)

16)Startup and shutdown also be done in OEM

17)OEM also checks the database growth

Let us Start the process

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

Prerequisites

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

Oracle Linux installed in the system

Database should be installed

we will install the Enterprise Manager on a linux platform

we will get the software from oracle website ---->Downloads---->Enterprsie Manager---->click on oracle enterprise manager downloads----> select on linux x86 64 bit

     

we can get the three zipped files like em12103_linux_disk1.zip,em12103_linux_disk2.zip,em12103_linux_disk2.zip

before starting  the install we need to make sure that there no database control configurataion 

we need to set up that

we need to listener is to be started

after that emca -config dbcontrol db

started em configuration assistant

it will ask like  database sid-->prod

listener port number -->1521

Listener Oracle_Home /u01/app/oracle/product/12.1.0/db

Password for sysuser

Password for DBSNMP user 

Password for SYSMAN user

Email address for notifications

Outgoing mail SMTP server for notifications

You have to specified the following settings

After that emca configuration assistant completed it will get the  OEM url

                                    login screen Menu screen

 

                                     Home Menu  screen 

         

                                          Performance Menu screen

                                              Availability Menu Screen 

                                                       Server Menu Screen

                                       Schema Menu Screen

                                          Data movement Screen Menu

                                          Software and Support Menu

Note : info on OEM it may be differ from your environment like production,testing,development 

THANK YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME 

ORACLE DATABASE HEALTH CHECK SCRIPTS

 ORACLE DATABASE HEALTH CHECK SCRIPTS

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

Check the Database Availability

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

sqlplus '/as sysdba'

sql>startup

sql>select instance_name,status from v$instance;

check the database read or write mode

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

sql>select name,open_mode from v$database;

check the oracle process run or not

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

# ps -ef |grep pmon

Health check of database instance &Listener

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

open terminal in linux machine

# lsnrctl status<listener-name>

if not started

# lsnrctl start <listener-name>

Check Long running process unix process

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

#  ps -ef | grep java 

Check Monitor alert log file in oracle

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

Log will be created in directory below the value of diagnostic_dest_parameter

DIAGNOSTIC_DEST parameter DIAGNOSTIC_DEST/diag/rdbms/db_name/oracle_sid/trace

alert.logfile

trace file is created by the user process and it is written in  USER_DUMP_DEST

alert file is created by server process and it is written in BACKGROUND_DUMP_DEST

You can delete alertsid.log any time while database is running when any log entry occurs it will create automatically no need to startup or shutdown,

The alertlog file also reffered to as the alert.log is chronlogical log of messages and errors written in out by oracle database typically mesages found in the file is 

database startup,shutdown,log switches,space errors etc

Check Monitoring space availability

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

my oracle work history

sql>select job_name,session_id,running_instance,elapsed_time,cpu_used from dba_scheduler_running_jobs

Check size of schema

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

sql>select sum(bytes)/1024/1024/1024 as 'size in gb' from dba_segments where owner= 'UPPER';

Check Size of database

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

sql>select round((sum(bytes)/1048576/1024),2) from v$ datafile;

Check Size of table in oracle

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

sql>select sum(bytes) from user_segments where segments_type='TABLE';

Check to get size in mb

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

sql>select segment_name,segment_type,bytes/1024/1024 mb from dba_segments where segment= type='TABLE' and segment_name='emp';

Database, Datafile and Backup

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

sql>select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;

sql>select * from v$recover_file;

sql>select distinct status,count() from v$datafile group by status;

sql>select name, RECOVER from v$datafile_header where RECOVER != 'NO';

sql>select name, ERROR from v$datafile_header where ERROR is NOT NULL;

sql>select name, ts# from v$datafile where upper(name) like '%MISSING%';

sql>select NAME, FILE# from v$datafile where status='RECOVER';

sql>select status,count() from v$backup group by status;

Check DBA Registry

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

sql>set lines 150 pages 500

column COMP_NAME format a45

column version format a15

column status format a12

sql>select comp_name,version,status from dba_registry;

Check DBA Registry History

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

sql>set lines 150 pages 500

column action format a15

column namespace format a15

column comments format a20 wrap

column ACTION_TIME format a30

col BUNDLE_SERIES format a12

column version format a15

sql>select * from dba_registry_history;

Check Invalid Object

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

sql>col object_name format a50

col owner format a10

set lines 300

set pages 1000

sql>select object_name,object_type,owner,status from dba_objects where status = 'INVALID';

Queries to check Locks

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

sql>SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;

Check Library cache locks

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

sql>select  w1.sid waiting_session,h1.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',

'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1

where

(((h.kgllkmod != 0) and (h.kgllkmod != 1)

and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))

and

(((w.kgllkmod = 0) or (w.kgllkmod= 1))

and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w1.saddr

and h.kgllkuse = h1.saddr;

hcheck.sql – Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above

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

A)$ sqlplus

sql> spool hcheck.log

sql> @hcheck

sql> spool off

For 12c with Multitenant, connect to each PDB to run the script. 

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

sql> show pdbs

CON_ID   CON_NAME     OPEN MODE      RESTRICTED

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

2        PDB$SEED     READ ONLY      NO

3        CDB1_PDB1    READ WRITE     NO

4        CDB1_PDB2    READ WRITE     NO

sql> alter session set container=CDB1_PDB1;

Session altered.

sql> spool hcheck.log

sql> @hcheck

sql> spool off

Note: info on database health check it may be differ from your environment like production,testing ,development

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