How to backup table in Oracle

In this Oracle tutorial, we will learn how to take backup in oracle database 21c. Steps shared in this tutorial, will work on oracle 11g, 12c, 19c & 21c. Also, we will cover these topics:

• How to backup table in oracle
• How to create backup table in oracle using rman
• How to take backup of table statistics in oracle
• How to create backup table in oracle without data
• How to create backup table in oracle sql developer
• How to backup all tables in oracle
• How to take table backup in oracle using expdp
• How to take table backup in oracle using toad

How to backup table in oracle

Backup plays an important role in saving up the company data during an emergency. When security is compromised or the server goes down due to any reason that time DBA uses backup to bring things back on track.

Backup is the duplicate file(s) of original data which can be used when original data is not accessible. Though backup files occupy space they are worth maintaining. It is highly recommended to maintain backups in a different location than of original data.

In oracle, the backup databases are classified into Physical (OS backup) and Logical (Tablespaces) databases.

In oracle, there are two ways to perform a backup:-

• User Managed Backups
• Oracle Recovery Manager (RMAN)

User-Managed Backups: Operating systems have their own backup creation system. This backup captures all the files on the system. In oracle, using SQL Plus, oracle files can be recovered from the os backup.

Use of any non-oracle tool to backup the database will also come under the user-managed backups in oracle database 21c.

Oracle Recovery Manager: RMAN is the backup and recovery utility from oracle. It is completely dedicated to oracle files and folders only. We highly recommend using RMAN for taking backups of the database.

There are two ways to perform backups using the RMAN utility tool:

• Consistent Backups
• Inconsistent Backups

Consistent Backups: Taking backup while the database is in a closed state is called consistent backup. If the business requirement permits the shutdown of a database for certain hours then this backup is performed.

This type of backup assures a 100% success rate of backup of all the files and folders and it is safe as well. But you cannot call it convenient looking at the market speed of growth in today’s time.

Inconsistent Backups: In order to keep up with the competition, the database cannot be closed even for seconds. When backups are performed while the database is running then it is termed inconsistent backups.

Most Database Administrators (DBAs) prefer this method to avoid any downtime. The database must be in archive mode before you can start the backup process.

In the next section, we will learn to create a backup table in oracle 21c using Oracle Recovery Manager (RMAN).

Also, check: Database Size in Oracle 21c

How to create backup table in oracle using rman

In the previous section, we have covered the theoretical part for creating backups in oracle database 21c. In this section, we will learn practically how to create a backup table in oracle.

Step 1: Set the oracle home environment and sid for the database you want to create a backup for. If you are new and have only one database then move to Step 2.
Tip: Recently created database is automatically set to default.

SET ORACLE_SET=<db_name>

Step 2: Start a command prompt and login to SQL Plus with SYSDBA or SYSBACKUP administrative privileges in oracle 21c.

SQLPLUS / AS SYSDBA

SQLPLUS / AS SYSBACKUP

Step 3: Check if you are in archive mode. If not then shut down the database, start the database in mount state and change it to an archive mode.

-- Check if in archive mode
ARCHIVE LOG LIST;

-- Start db in mount state
SHUTDOWN IMMEDIATE
STARTUP MOUNT

-- Set db to archive mode
ALTER DATABASE ARCHIVELOG;

Step 4: Take note of files and tablespaces that you want to backup, from the next step database won’t allow you to access data files.

SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;

SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

Step 5: Before jumping to the RMAN utility, check the destination folder for the Backup file. You can change the destination location and size value using alter command. In oracle 21c, by default recovery destination is set to C:\Oracle_21c\fast_recovery_area\ and size is 13836 MB.

-- View parameter
SHOW PARAMETER DB_RECOVER

-- Change location to Desktop
ALTER SYSTEM SET DB_RECOVERY_FILE='c:\users\oracle\Desktop\backup';

-- Change Size to 10GB
ALTER SYSTEM SET DB_RECOVERY_FILE_SIZE=10GB;

Step 6: Start the RMAN utility and connect to the database. You can either use / to connect with the recent database or provide connection details.

Step 7: Once connected you can view the list of databases available for backup using the following command. Tables in both Container Database (CDB) and Pluggable Database (PDB) are available here.

REPORT SCHEMA;

Step 8: If a tablespace has more than one datafile then all those files will be backed up when the backup is performed on that tablespace. Use datafile number or name to take backup of datafile only. Use the below command to the backup table in oracle.

BACKUP TABLESPACE [tablespace_name | datafile_name |datafile_number];

Step 9: Once the backup file is created you can view the backup using the below command in oracle 21c. The backup file is created at the location specified in step 5.

list backup;

Read: Oracle Create User

How to take backup of table statistics in oracle

Statistics or stats are the huge collections of data that describe the database and its objects in an oracle. In the below script we have shown how to take backup of table statistics in oracle database 21c.

The second step in the below script shows how to take backup of table statistics in oracle.

-- Create table to store the stats
execute dbms_stats.create_stat_table(ownname= 'sqlserverguides', stattab= 'backup_stats');

-- export the statistics  [taking backup]
exec dbms_stats.export_table_stats(ownname=>'sqlserverguides', tabname=>'testserver', statown=>'sqlserverguides', stattab=>'backup_stats', cascade=>true);

-- importing the statistics 
exec dbms_stats.import_table_stats(ownname=>'sqlserverguides', tabname=>'home', statown=>'sqlserverguides', stattab=>'backup_stats', cascade=>true);

- delete the statstics table
execute dbms_stats.drop_stat_table(ownname= 'sqlserverguides', stattab= 'backup_stats')

Read: Oracle Drop Database

How to create backup table in oracle without data

In oracle, using As Select statement backup can be created without data. Add the where statement with 1=0. Below is the example, here we have created a backup of the sales table with the name sales_backup.

CREATE TABLE SALES_BACKUP AS 
  SELECT * FROM SALES
  WHERE 1=0;

Alternatively, you can create a backup table in the Oracle SQL developer tool as well. Follow these steps:

• Connect with the database as sysdba.
• click on the Tools -> Database copy -> provide the source and destination connection
• deselect copy data checkbox to create backup table without data.
• Click Next and then finish on the next page.

A backup of the table will be created. The process could take some time depending upon the size of the data and once done you can access the data in the destination connection.

Read More – Oracle how to copy a table

How to create backup table in oracle sql developer

Oracle offers a SQL developer tool using which huge SQL queries can be executed efficiently. It provides both command line and GUI support to execute oracle operations.

Create backup has two meanings here, either you can copy the table data or you can export it. In our blog – Oracle how to copy a table we have shown how to copy a table using the SQL developer tool.

Here we are going to show you how to export or create a backup table in the Oracle SQL developer tool:

Step 1: Click on the tool -> Database Export you will see an interface as shown in the below image. Select the database (login will be required) from the dropdown and browse to the destination of the export file.

Step 2: Click on the Next button 4 times and then finish. The export of data will take time depending upon the size of the database. You click on the Run in Background button.

Once the process is completed, you have a SQL file that can be imported in case of data loss.

Read: Oracle check database status

How to backup all tables in oracle

In oracle, one tablespace can hold more than one data file. These data files have information about the tables. To backup all tables in oracle, we need to backup the tablespace that has tables.

In the second section of this blog – How to create a backup table in oracle using rman we have shown how to take backup in oracle. In step 8 provide the name of the tablespace to take backup of all the tables inside it.

Read: Oracle Stored Procedure

How to take table backup in oracle using expdp

Oracle expdp is another method to take backup of the table. The word ‘expdp’ is made of two words:- export (exp) and dump(dp). We can export multiple tables using expdb in the oracle database.

Using the below syntax on the command prompt or terminal, you can take table backup in oracle using expdb:

expdp <user>/<pwd>@<host>/<sid> DUMPFILE=<filename.dmp> DIRECTORY=<Path_to_save> TABLENAME=<first_table>, <sec_table>, <third_table> .... <n_table>; 

Here is the implementation of the above syntax:

expdp prod/toor@localhost/orcl DUMPFILE=export.dmp DIRECTORY=C:\Users\Users\Desktop TABLENAME=events, manager;

• prod:- user to connect with
• toor:- password of the user
• loclahost:- provide localhost or ip address
• orcl:- container name
• export.dmp:- name provided to exported file
• events and manager are the table names that we want to export.

How to take table backup in oracle using toad

Toad for Oracle is a third-party tool that is commercially available to run oracle queries. It is a modern tool that claims easy management of oracle operations with business agility.

Step 1: From the menu, click on the Database option and select Export.

Step 2: There are multiple options for exporting the data, choose Export Utility Wizard.

Step 3: Since we want to export tables so select the Export tables radio button and click on the Next button.

Step 4: Select the table(s) you want to export. To export, multiple tables click on the checkbox in front of the table name.

Step 5: Select the object(s) you want to export. You can choose multiple objects by clicking on the checkbox.

Step 6: Choose the destination to export the file in the Output file name section also you can provide a name to the file.

Step 7: The last step is to select if you want to export the file now or later. If now then check the checkboxes for compress export files and watch the progress. This is optional to do but looks better that way. Click on the finish button to start the backup.

Once completed you can visit the path where you have stored the file also import it as and when required.

You may also like to read the following Oracle tutorials.

• Oracle get database name
• Connect to oracle database
• How to create table in Oracle
• Number Datatype in Oracle 
• Oracle stored procedure create table

In this tutorial, we have learned how to take backup in oracle database 21c. Also, have covered these topics:

• How to backup table in oracle
• How to create backup table in oracle using rman
• How to take backup of table statistics in oracle
• How to create backup table in oracle without data
• How to create backup table in oracle sql developer
• How to backup all tables in oracle
• How to take table backup in oracle using expdp
• How to take table backup in oracle using toad