We are going to have look on the backup of Oracle, Which is common in DBA’s life. HOT Backup is the most common way to have a backup.
Let’s have look on the steps of Oracle’s HOT Backup.
Check the archive status
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9
Create a test table
SQL> create table online_back_1(name varchar(100)) tablespace users;
Table created.
SQL> insert into online_back_1 values('*******BEFORE BACKUP *******');
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT owner,segment_name,segment_type FROM DBA_SEGMENTS WHERE TABLESPACE_NAME ='USERS';
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ -----------------------------------------------------------------
SYS ONLINE_BACK1 TABLE
1 rows selected.
Check the datafile name and begin the backup mode
SQL> select file_id,tablespace_name,file_name from dba_data_files; FILE_ID TABLESPACE_NAME FILE_NAME ------------------------------------------------------------------------------------------ 4 USERS D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\USERS01.DBF 3 SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSAUX01.DBF 2 UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\UNDOTBS01.DBF 1 SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSTEM01.DBF 5 TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\TEST01.DBF SQL> select current_scn from v$database; CURRENT_SCN ----------- 868641 SQL> alter tablespace users begin backup; Tablespace altered. SQL> select * from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 ACTIVE 868643 18-JUN-10 5 NOT ACTIVE 0
Copy the datafile into a folder
D:\>mkdir Hot_Backup_1 D:\>copy D:\oracle\product\10.2.0\oradata\DBA01\USERS01.DBF D:\Hot_Backup 1 file(s) copied.
Stop the backup mode
SQL> alter tablespace users end backup;
Tablespace altered.
SQL> insert into online_back_1 values('*******AFTER BACKUP *******');
1 row created.
SQL> commit;
Commit complete.
Stop the database and remove the datafile then try to start the database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> HOST Del USERS01.DBF SQL> startup; ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1247876 bytes Variable Size 75498876 bytes Database Buffers 83886080 bytes Redo Buffers 7139328 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\USERS01.DBF'
Make datafile offline and start recovery
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\USERS01.DBF' offline; Database altered. SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\USERS01.DBF'; Media recovery complete. SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\USERS01.DBF' online; Database altered. SQL> alter database open; Database altered. SQL> select * from online_back_1; NAME --------------------------------------------- *******BEFORE BACKUP ******* *******AFTER BACKUP *******
Today’s thought
“Great achievers are driven, not so much by the pursuit of success but by the fear of failure.” Larry Ellison
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
