How to check Rman backup Job

How to check Rman backup Job

 In below script we will see  RMAN backup status along with start and stop timing.

SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;  

SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
      10476 DB INCR       COMPLETED 11/21/17 13:30 11/21/17 13:57     .44
      10491 DB INCR       COMPLETED 11/21/17 21:55 11/21/17 23:07    1.20
      10496 DB INCR       COMPLETED 11/22/17 06:00 11/22/17 06:07     .12
      10504 DB INCR       COMPLETED 11/22/17 21:55 11/22/17 23:02    1.13
      10509 DB INCR       COMPLETED 11/23/17 06:00 11/23/17 06:16     .26
      10517 DB INCR       COMPLETED 11/23/17 21:56 11/23/17 22:59    1.05
      10522 DB INCR       COMPLETED 11/24/17 06:00 11/24/17 06:04     .07
      10530 DB INCR       COMPLETED 11/24/17 22:06 11/24/17 23:10    1.06
      10535 DB INCR       COMPLETED 11/25/17 06:00 11/25/17 06:07     .11
      10543 DB INCR       COMPLETED 11/26/17 06:00 11/26/17 12:14    6.23
      10551 DB INCR       COMPLETED 11/27/17 06:00 11/27/17 07:20    1.34
      10559 DB INCR       COMPLETED 11/27/17 21:57 11/28/17 04:32    6.58
      10564 DB INCR       FAILED    11/28/17 06:00 11/28/17 12:49    6.81
      10568 DB INCR       COMPLETED 11/28/17 21:55 11/29/17 01:03    3.13
      10573 DB INCR       COMPLETED 11/29/17 06:00 11/29/17 06:25     .42
      10581 DB INCR       COMPLETED 11/29/17 21:48 11/29/17 23:34    1.77
      10586 DB INCR       COMPLETED 11/30/17 06:00 11/30/17 08:49    2.82
      10594 ARCHIVELOG    RUNNING   11/30/17 11:12 11/30/17 12:28    1.27

      10597 ARCHIVELOG    RUNNING   11/30/17 11:51 11/30/17 12:28     .61


In below script we will see  SID, Total Work, Sofar & % of completion.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'

AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK; 

       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK % COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
       111      22620          1     428855    3637560      11.79
       116        891          1     465662    1846176      25.22

        47        430          1   15747871   23353264      67.43

In this Script we will  check historical backup status with the help of following script:

 set linesize 500 pagesize 2000

 col Hours format 9999.99

 col STATUS format a10

 select SESSION_KEY, INPUT_TYPE, STATUS,

 to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

 to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

 elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

 order by session_key;

SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME     HOURS

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

      10314 CONTROLFILE   COMPLETED  11-08-2017 06:05:22 11-08-2017 06:05:44      .01

      10322 DB INCR       COMPLETED  11-08-2017 21:59:32 11-08-2017 23:03:35     1.07

      10327 DB INCR       COMPLETED  11-09-2017 06:00:20 11-09-2017 06:04:58      .08

      10335 DB INCR       COMPLETED  11-09-2017 21:54:54 11-09-2017 22:58:09     1.05

      10340 DB INCR       COMPLETED  11-10-2017 06:00:21 11-10-2017 06:04:52      .08

      10348 DB INCR       COMPLETED  11-10-2017 21:56:20 11-10-2017 23:01:05     1.08

      10353 DB INCR       COMPLETED  11-11-2017 06:00:28 11-11-2017 06:06:38      .10

      10361 DB INCR       COMPLETED  11-12-2017 06:00:20 11-12-2017 06:33:49      .56

      10369 DB INCR       COMPLETED  11-13-2017 06:00:28 11-13-2017 06:06:47      .11

      10377 DB INCR       COMPLETED  11-13-2017 21:54:08 11-13-2017 23:10:10     1.27

      10382 DB INCR       COMPLETED  11-14-2017 06:00:20 11-14-2017 06:04:59      .08

      10390 DB INCR       COMPLETED  11-14-2017 22:01:09 11-14-2017 23:09:16     1.14

      10395 DB INCR       COMPLETED  11-15-2017 06:00:20 11-15-2017 06:07:13      .11

      10403 DB INCR       COMPLETED  11-15-2017 21:56:38 11-15-2017 23:00:32     1.07

      10408 DB INCR       COMPLETED  11-16-2017 06:00:20 11-16-2017 06:04:38      .07

      10416 DB INCR       COMPLETED  11-16-2017 21:55:53 11-16-2017 23:00:55     1.08

      10421 DB INCR       COMPLETED  11-17-2017 06:00:20 11-17-2017 06:05:43      .09

      10429 DB INCR       COMPLETED  11-17-2017 21:56:44 11-17-2017 23:01:21     1.08

      10434 DB INCR       COMPLETED  11-18-2017 06:00:29 11-18-2017 06:06:42      .10

      10442 DB INCR       COMPLETED  11-19-2017 06:00:20 11-19-2017 06:28:17      .47

Sql loader in oracle

Sql loader in oracle

If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file. Actually, Sql loader is the utility to use for high performance data loads. It is used for moving data from external files into the Oracle database.

The Control File

The SQL Loader control file, is a flat file or text file, contains information that describes how the data will be loaded.

 It contain the following information…

§  infile – Indicates the location of the input data file

§  into table – Indicates the table name where this data should be inserted

§  fields terminated by – Indicates the delimiter that is used in the input file to separate the fields

§  ( id, name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded.

Options in SQL*Loader while loading the data.

      a) INSERT: Specifies that you are loading into an empty table.

(b) APPEND: If we want to load the data into a table which is already containing some rows.

(c) REPLACE: Specifies that, we want to replace the data in the table before loading. Will 'DELETE' all the existing records and replace them with new.

(d) TRUNCATE: This is same as 'REPLACE', but SQL*Loader will use the 'TRUNCATE' command instead of 'DELETE' command.

Example

Firstly, We create the table as shown below.

SQL> create table employee

(

  id integer,

  name varchar2(10),

  dept varchar2(15),

  salary integer)

Now We create the control file to upload data.

Define the SQL*Loader control file, called .ctl, that will allow us to load the data.

load data

 infile '/home/xyz/employee.txt'

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

 Now we execute the sqlldr command to upload these new record to the empty table .

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new.ctl

Commit point reached - logical record count 5

Now verify...

SQL> select * from employee;

        ID NAME       DEPT                SALARY 

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

       100 Ram      Sales                 5000

       200 Ravi     Technology            5500

       300 Raja     Technology            7000

       400 Mohan      Marketing             9500

       500 Shyam    Technology            6000

 Data inside the Control File using BEGINDATA

We can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table .

$ Cat sqlldr-add-new-with-data.ctl

Load data

 infile *

 into table employee

 fields terminated by ","

 ( id, name, dept, salary )

begindata

100,Ram,Sales,5000

200,Ravi,Technology,5500

300,Raj,Technology,7000

400,Shyam,Marketing,9500

 Now We Execute sqlldr to upload the data from the control file.

$ sqlldr scott/tiger control=/home/xyz/sqlldr-add-new-with-data.ctl

Loading fixed length (positional) data

The control file can also specify that records are in fixed format. A file is in fixed record format when all records in a datafile are the same length.

LOAD DATA

INFILE *

INTO TABLE positional_data

(data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

Database Shutdown

Database Shutdown

Different Modes in Database Shutdown

There are different modes to bring down the database:
1. Shutdown immediate
2. Shutdown transactional
3. Shutdown normal
4. Shutdown abort

Shutdown Immediate

1) No new connections are allowed.

2) Active transactions are terminated and uncommitted changes are rolled back.

3) Changes made by an active transaction are lost in this option.

$ sqlplus / as sysdba

SQL> shutdown immediate;

Shutdown Transactional

1) No new connections are allowed.

2) Oracle waits until all active transactions are completed.

3) Oracle then performs a checkpoint and then close the online data files.

$ sqlplus / as sysdba

SQL> shutdown transactional

Shutdown Normal

1) The database waits for all connected users to disconnect before shutting down.

2) It waits till all the current transactions end.

3) Oracle then performs a checkpoint and then close the online data files.

$ sqlplus / as sysdba

SQL> shutdown normal.

Shutdown Abort

1) Oracle closes the data files without any checkpoint.

2) This is the fastest shutdown mode.

Note-

 1) This happens immediately however the database is not shutdown cleanly.

 2) Database will have to perform instance recovery next time it is started.

3) This option should not be used in regular activities.

Flash Recovery Area

Flash Recovery Area (FRA)

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

The flash recovery area is the most powerful tool available from Oracle 10g, that plays a vital role in performing database backup & recovery operations. From Oracle 11g release2, flash recovery area is called as fast recovery area.

-- Utilisation (MB) du (Size)

select

   name,

  floor(space_limit / 1024 / 1024) "Size MB",

  ceil(space_used / 1024 / 1024) "Used MB"

from v$recovery_file_dest;

-- FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA

show parameter db_recovery_file_dest

-- Size, used, Reclaimable

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT,

  SPACE_USED ,

  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:

-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location ;

-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';