Oracle - Hot Backup

 

 

HOT BACKUP

 

Step 1. Ensure That the Database Is in Archivelog Mode

 

SQL> archive log list;

Database log mode            Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     27

Next log sequence to archive   29

Current log sequence         29

 

Step 2. Determine Where to Copy the Backup Files

 

[oracle@oracle ~]$ cd u03/

[oracle@oracle u03]$ mkdir hotbackup

[oracle@oracle u03]$ ls

coldbackup  hotbackup

 

Step 3. Identify Which Files Need to Be Backed Up

 

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_system_jhobsyhf_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_sysaux_jhobvcxq_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_undotbs1_jhobw56w_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/tspace.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_users_jhobw6bc_.dbf

 

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oracle/oradata/ORACLE/controlfile/control2.ctl

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/ORACLE/onlinelog/redo3.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo3.log

/u01/app/oracle/oradata/ORACLE/onlinelog/redo1.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo2.log

/u01/app/oracle/oradata/ORACLE/onlinelog/redo2.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo1.log

 

6 rows selected.

Step 4. Alter the Database into Backup Mode

 

SQL> alter database begin backup;

 

Database altered.

 

Step 5. Copy the Data Files with an OS Utility

 

[oracle@oracle u03]$ cd

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_system_jhobsyhf_.dbf u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_sysaux_jhobvcxq_.dbf u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_undotbs1_jhobw56w_.dbf u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/tspace.dbf u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_users_jhobw6bc_.dbf u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/controlfile/control2.ctl u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo3.log u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo3.log u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo1.log u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo2.log u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo2.log u03/hotbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo1.log u03/hotbackup/

[oracle@oracle ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 9 23:10:22 2021

Version 19.3.0.0.0

 

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

Connected to:

 

 

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

 

Step 6. Alter the Database out of Backup Mode

 

SQL> alter database end backup;

 

Database altered.

 

SQL> select name from v$database;

 

NAME

---------

ORACLE

 

 

Oracle Cold Backup

 

Cold backup.

Step 1—Generating File List

Data files

Control files

Init.ora 

 

V$datafile Lists all the data files used in the database

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_system_jhobsyhf_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_sysaux_jhobvcxq_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_undotbs1_jhobw56w_.dbf

/u01/app/oracle/oradata/ORACLE/datafile/tspace.dbf

/u01/app/oracle/oradata/ORACLE/datafile/o1_mf_users_jhobw6bc_.dbf

Backup the control file and perform a trace of the control file using

SQL>alter database backup controlfile to '/u03/colbkp/control.ctl';

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oracle/oradata/ORACLE/controlfile/control2.ctl

 

SQL> select member from v$logfile;

 

MEMBER

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

/u01/app/oracle/oradata/ORACLE/onlinelog/redo3.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo3.log

/u01/app/oracle/oradata/ORACLE/onlinelog/redo1.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo2.log

/u01/app/oracle/oradata/ORACLE/onlinelog/redo2.log

/u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo1.log

 

Step 2—Shut down the database

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

Step 3- create a new folder using mkdir to save the backup files in this location

 

[oracle@oracle ~]$ mkdir u03

[oracle@oracle ~]$ cd u03/

[oracle@oracle u03]$ mkdir coldbackup

 

 

Step 4—Perform a backup

In the first step, you generated a list of files to be backed up. To back up the files, you can use the Unix copy command (cp) to copy it to a backup location, as shown in the following code. You have to copy all files that you generated in Step 1.

[oracle@oracle u03]$ cd coldbackup/

[oracle@oracle coldbackup]$ cd

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_system_jhobsyhf_.dbf u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_sysaux_jhobvcxq_.dbf u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_undotbs1_jhobw56w_.dbf u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/tspace.dbf u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/datafile/o1_mf_users_jhobw6bc_.dbf u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/controlfile/control2.ctl u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo3.log u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo3.log u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo1.log u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo2.log u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/oradata/ORACLE/onlinelog/redo2.log u03/coldbackup/

[oracle@oracle ~]$ cp /u01/app/oracle/fast_recovery_area/ORACLE/onlinelog/redo1.log u03/coldbackup/

 

Step 5—Start the database

After the backup is complete, you can start the database as follows:

[oracle@oracle ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 9 23:00:00 2021

Version 19.3.0.0.0

 

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

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1543500144 bytes

Fixed Size                       8896880 bytes

Variable Size                 989855744 bytes

Database Buffers          536870912 bytes

Redo Buffers                  7876608 bytes

Database mounted.

Database opened.

SQL> alter database backup controlfile to trace;

 

Database altered.

 

SQL> select name from v$database;

 

NAME

---------

ORACLE

 

 

 

Difference between exp/imp and expdp/impdp

 

Difference between exp/imp and expdp/impdp

Datapump	Tradionational exp/imp
Datapump operates on a group of files called dump file sets.	Normal export operates on a single file.
Datapump access files in the server (using ORACLE directories)	Traditional export can access files in client and server both (not using ORACLE directories).
Datapump represent database metadata information as XML document format.	Exports (exp/imp) represent database metadata information as DDLs in the dump file
Datapump has parallel execution.	exp/imp single stream execution.
Datapump does not support sequential media like tapes	traditional export supports equential media like tapes
In Data Pump, we can stop and restart the jobs.	That facility don’t support in traditional exp/imp
Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.
Data Pump will recreate the user, whereas the old imp utility required the DBA to create the user ID before importing.

.

 

Data Pump is faster than conventional export/import. 

·         Data Pump is block mode, Traditional exp is byte mode.

·         Data Pump will do parallel execution.

·         Data Pump uses direct path API.

·         Datapump gives 15 – 50% performance improvement than exp/imp.

 

 

 

 

Parameter name changes in datapump:

 

EXP/IMP Parameter	EXPDP/IMPDP Parameter
owner	schemas
file	dumpfile
log	logfile/nologfile
IMP: fromuser, touser	IMPDP: remap_schema

 

Additional Features in datapump:

1.      Job Estimation can be done in datapump

2.      SAMPLE parameter is used for taking the sample % data.

3.      Failed export/import Jobs can be restarted

4.      EXCLUDE/INCLUDE parameter allows the fine-grained object selection.

5.      Data remapping can be done using REMAPDATA parameter.

6.      You don’t need to specify the BUFFER size in datapump

7.      Job estimated completion time can be monitored from v$session_longops view.

8.      Dump file can be compressed with COMPRESSION parameter.

9.      Data encryption can be done in datapump.

10.  DATAPUMP has interactive options like ADD_FILE, START_JOB, KILL_JOB, STOP_JOB.

11.  REUSE_DUMPFILES parameter asks the confirmation/rewrite the existing dumpfile.

Oracle – Pinning table data in the Buffer Cache

Oracle – Pinning table data in the Buffer Cache

 

Database buffer cache:

            The largest component of the SGA is usually the database buffer cache, which is the part of the SGA that holds copies of blocks of data read from the Oracle datafiles on disk. The size of the database buffer cache is controlled by the INIT.ORA parameter DB_BLOCK_BUFFERS, which specifies the number of database blocks that will be contained in the database buffer cache.

Pinning table data in buffer cache:

Step 1: connect and start the database.

SQL> startup

ORACLE instance started.

Total System Global Area 2147481656 bytes

Fixed Size                       8898616 bytes

Variable Size               1677721600 bytes

Database Buffers          452984832 bytes

Redo Buffers                  7876608 bytes

Database mounted.

Database opened.

Step 2: first make sure that there’s nothing in the cache by running and clear the cache file using following command

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

Step 3: Now, with the help of autotrace, we can have a look at the difference between retrieving cached and uncached data.

Auto trace
          The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement. The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics.

Timing

            Sometimes when working on SQL command optimizations, all that is desired is a rough timing estimate; namely, the SQL*Plus client elapsed execution time, or simple clock time. Often that simple metric is sufficient for some very basic tuning needs. SQL*Plus has a built-in capability to do exactly this - it is the SET TIMING  command. 

SQL> set autotrace on

SQL> set timing on

Step 4:run the sql select statement to check the result

SQL> select * from rman.rman;

The first time we execute this query, the timing and statistics output will be something like

            ID NAME

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

             7 viki

            10 vignesh

            10 vijay

             3 ajay

             4 pradeep

             5 chokku

             6 ram

             8 si

8 rows selected.

Elapsed: 00:00:00.16 (run this query on 00.16)

Execution Plan

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

Plan hash value: 557262021

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time           |

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

|   0 | SELECT STATEMENT  |          |     6 |    54 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| RMAN |     6 |    54 |     3   (0)| 00:00:01 |

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

Statistics

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

             52  recursive calls

              0  db block gets

             56  consistent gets

             22  physical reads(and this count as 22)

              0  redo size

            774  bytes sent via SQL*Net to client

            385  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              5  sorts (memory)

              0  sorts (disk)

              8  rows processed

run the same query again, we can see that things have changed a bit

SQL> select * from rman.rman;

            ID NAME

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

             7 viki

            10 vignesh

            10 vijay

             3 ajay

             4 pradeep

             5 chokku

             6 ram

             8 si

8 rows selected.

Elapsed: 00:00:00.01(time has been changed)

Execution Plan

Plan hash value: 557262021

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time           |

|   0 | SELECT STATEMENT  |          |     6 |    54 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| RMAN |     6 |    54 |     3   (0)| 00:00:01 |

Statistics

              0  recursive calls

              0  db block gets

              6  consistent gets

              0  physical reads(its also changed)

              0  redo size

            774  bytes sent via SQL*Net to client

            385  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              0  sorts (memory)

              0  sorts (disk)

              8  rows processed

The second run was a fair bit faster. This is mainly because the data required to resolve the query was cached after the first run.
           Therefore, the second execution required no Physical I/O to retrieve the result set.

current size of the Buffer Cache:

SQL> select component, current_size from v$memory_dynamic_components where component = 'DEFAULT buffer cache';    

COMPONENT                                                                                   CURRENT_SIZE

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

DEFAULT buffer cache                                                                         419430400

 

Elapsed: 00:00:00.00

Execution Plan

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

Plan hash value: 2402317937

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

| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT |               |         1 |      31 |       0   (0)| 00:00:01 |

|*  1 |  FIXED TABLE FULL| X$KMGSCT |            1 |      31 |       0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("ST"."COMPONENT"='DEFAULT buffer cache' AND

                  "ST"."INST_ID"=USERENV('INSTANCE'))

Statistics

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

              2  recursive calls

              0  db block gets

              1  consistent gets

              0  physical reads

              0  redo size

            657  bytes sent via SQL*Net to client

            466  bytes received via SQL*Net from client

              2  SQL*Net roundtrips to/from client

              0  sorts (memory)

              0  sorts (disk)

              1  rows processed