RMAN BACKUP AND RESTORE
Oracle Fast Recovery Area (FRA) :
-------------------------------
Optimize Oracle Fast Recovery Area for backup and recovery operations.
Oracle FRA location is to simplify the backup and recovery management for the Oracle database. Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.
Reasons to configure FRA :
Stores RMAN backups, control file, redo logs and archive logs
Reduces and simplifies database administration tasks
Oracle takes care of managing files inside the FRA
All backups are placed in one central location
Automatically cleanup of old backups under FRA location
Manages Oracle flashback logs
Oracle creates archived logs and flashback logs in the fast recovery area. Oracle Recovery Manager (RMAN) can store its backup sets and image copies in the fast recovery area, and it uses it when restoring files during media recovery.
Oracle Database automatically manages this storage, deleting files that are no longer needed. When you issue the RMAN BACKUP command without specifying a backup destination, RMAN automatically backs up to the fast recovery area if it is configured.
Configure FRA :
Specify the size of the fast recovery area using the following command
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
Specify the location of the fast recovery area using the following command:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oracle/fra';
Note: The DB_RECOVERY_FILE_DEST_SIZE parameter must be set before the DB_RECOVERY_FILE_DEST parameter.
Backup with Backup Sets :
--------------------------
When RMAN performs a backup, it stores the backup in a backup set. Backup sets are compressed collections of backup pieces that can be stored in the file system or other storage locations.
RMAN> BACKUP AS BACKUPSET DATABASE;
Backup sets provide flexibility in terms of file storage and can be used to back up the entire database
or specific database components.
Understanding RMAN Catalog :
------------------------------
The RMAN Catalog is an optional database that stores the metadata related to the backup, restore,
and recovery operations performed by RMAN. The catalog tracks backup details and the history of
RMAN operations, making it easier to manage multiple Oracle databases.
a. Benefits of Using an RMAN Catalog :
• Centralized Repository: When you have multiple databases, the RMAN catalog can centralize the backup metadata, making it easier to manage backup operations.
• Backup History: It provides an historical record of backup operations, including the date, type of backup, and details about the backup pieces.
• Faster Recovery: The catalog can be used to restore the database metadata, improving recovery times.
• Cross-Database Backup Management: The RMAN catalog can help manage backups across multiple databases in different locations, improving consistency.
------------------------
**Instance Recovery** and **Crash Recovery** are both mechanisms used by Oracle to ensure data consistency and availability after a system failure.
1. **Instance Recovery**:(SMON,DBwn,LGwr,Reco bg process)
- **Definition**: Instance recovery occurs when an Oracle instance fails unexpectedly (e.g., due to a crash or power failure), but the database itself remains available. The recovery process focuses on the redo log files to bring the database back to a consistent state.
- **Process**:
- When the instance crashes, Oracle uses the **online redo logs** to roll forward any changes that were made to the database but not yet written to disk (i.e., **datafiles**).
- It also uses **undo segments** to roll back any uncommitted transactions.
- Instance recovery is usually automatic and happens when the instance is restarted, without requiring user intervention.
- **Steps**:
- **Redo Log Application**: Redo logs that were written before the crash are applied to the datafiles to reflect all committed changes.
- **Undo Rollback**: Oracle rolls back uncommitted transactions by using undo information stored in undo segments.
- **Checkpoint**: A checkpoint is then performed to ensure data consistency.
2. **Crash Recovery**:(SMON,DBwn,LGwr,Reco bg process)
- **Definition**: Crash recovery is essentially a subset of instance recovery and occurs when the Oracle database crashes (either due to a failure in the instance or hardware), and the database must be brought to a consistent state before it can be accessed.
- **Process**:
- Crash recovery happens automatically when the database is restarted after a crash.
- The process involves rolling forward **committed transactions** using redo logs and rolling back **uncommitted transactions** using undo information.
- The main goal is to ensure that after recovery, the database is in a consistent state with no data corruption.
- The system ensures that all **committed data** is written to disk, and no **uncommitted data** remains, achieving a consistent view of the database.
- **Steps**:
- **Rolling Forward**: The database applies any redo logs (from the online redo log files) that were generated since the last checkpoint.
- **Rolling Back**: Any uncommitted changes are undone by using undo information from undo tablespaces.
- **Recovery Completion**: After applying all the redo and undo logs, Oracle performs a checkpoint to ensure all changes are written to disk.
In both recovery processes, **redo logs** and **undo segments** play a vital role in ensuring that the database is brought back to a consistent state, with all committed changes preserved and uncommitted changes rolled back.
what is incarnation :
=====================
In Oracle databases, incarnation refers to the history of a database's unique timeline, typically associated with backup and recovery operations, particularly with RMAN (Recovery Manager). An incarnation represents a new "version" or timeline of the database after certain events, such as a reset of the SCN (System Change Number), usually following a database resetlogs operation.
When Does a Database Incarnation Occur?
A new incarnation is created in scenarios such as:
- Database Open with Resetlogs:
When a database undergoes incomplete recovery (e.g., point-in-time recovery, flashback, etc.), it requires an open resetlogs operation. This resetlogs resets the log sequence number and creates a new timeline for the database.
- New Database Creation:
A newly created database is assigned its initial incarnation.
- Database Duplication:
When duplicating a database, a new incarnation may be recorded.
- Flashback Database:
Flashing back a database may involve resetlogs, creating a new incarnation.
--------------------------------
⏺ Obsulte backup vs expired :
--------------------------------
An obsolete backup in Oracle refers to a backup that is no longer needed to meet the retention policy for recovery purposes. This determination is based on the retention policy you have defined for your backups. Obsolete backups are not immediately deleted; instead, they are marked as no longer required for recovery, allowing you to free up storage by manually removing them.
Key Concepts of Obsolete Backups:
Retention Policy:
Recovery Window: Specifies the number of days of recoverable data (e.g., CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS).
Redundancy: Specifies the number of backup copies to keep (e.g., CONFIGURE RETENTION POLICY TO REDUNDANCY 2).
Marking Obsolete Backups:
RMAN compares the backups with the retention policy. Any backup not required to satisfy the policy is marked obsolete.
These backups are candidates for deletion, but RMAN does not automatically delete them unless explicitly instructed.
Difference Between Obsolete and Expired:
Obsolete: Backups that are no longer needed per the retention policy.
Expired: Backups that cannot be found at their expected location (e.g., if the backup file was deleted outside of RMAN).
Commands Related to Obsolete Backups:
RMAN> REPORT OBSOLETE;
RMAN> DELETE OBSOLETE;
RMAN> DELETE OBSOLETE PREVIEW;
Here’s the comparison for easy copying:
| **Feature** | **Obsolete Backup** | **Expired Backup** |
|-----------------------|----------------------------------------------------|-----------------------------------------------|
| **Definition** | Backups no longer needed to satisfy the retention policy. | Backups that are missing or unavailable at the expected location. |
| **Determination** | Based on the retention policy (recovery window or redundancy). | Based on a CROSSCHECK operation. |
| **Physical File** | File exists on the storage media. | File is missing or manually deleted. |
| **Resolution** | Use DELETE OBSOLETE to remove unnecessary backups. | Use DELETE EXPIRED to remove stale metadata. |
| **Usage Scenario** | To clean up unnecessary backups. | To clean up metadata for non-existent backups. |
-----------------------------------------------
How are RMAN backups consistent if db is running?
--------------------------------------------------
RMAN ensures consistency during a hot backup by relying on Oracle's built-in mechanisms like consistent read, online redo logs, backup mode, and SCN tracking. These mechanisms allow RMAN to take a consistent snapshot of the database while it’s running, even as transactions continue.
After the backup, archived redo logs can be used to roll forward any changes and ensure that the database can be recovered to a consistent point in time if needed.
This approach ensures that an RMAN backup is consistent, even if the database is active and users are interacting with it during the backup process.
------------------
RMAN Full Backup :
-------------------
Definition: A backup of all the data blocks in the database, regardless of whether they have changed since the last backup.
Purpose: Standalone backup that is not part of an incremental backup strategy.
Behavior:
A full backup does not rely on or integrate with incremental backups.
Every block in the database is backed up during a full backup.
Usage:
Useful when you want an independent, complete backup of the database.
Typically used in environments that do not use incremental backups.
Command : BACKUP DATABASE;
--------------------
RMAN Level 0 Backup :
----------------------
Definition: A baseline backup that is part of an incremental backup strategy. It backs up all data blocks in the database.
Purpose: Serves as the starting point (base) for incremental backups.
Behavior:
Functions similarly to a full backup in that it backs up all data blocks, but it is specifically labeled as Level 0.
Subsequent Level 1 backups (differential or cumulative) depend on the Level 0 backup for recovery.
Usage:
Used in incremental backup strategies, where subsequent backups only include changes since the last Level 0 or Level 1 backup.
Command:
BACKUP INCREMENTAL LEVEL 0 DATABASE;
When to Use Which?
----------------
Full Backup:
Ideal for simple backup strategies where you want a complete database backup without incremental backups.
Suitable for smaller databases or where disk space is not a concern.
Level 0 Backup:
Essential for incremental backup strategies, especially for large databases.
Reduces backup size and time by allowing incremental backups to store only changes.
=======================================
Here's a comparison of **DB Full Backup** and **Level 0 (L0) Backup** in RMAN:
=======================================
---
| **Feature** | **DB Full Backup** | **Level 0 (L0) Backup** |
|--------------------------|-------------------------------------------------------|-------------------------------------------------------|
| **Definition** | A complete backup of the entire database, regardless of previous backups. | A complete backup of the database, which serves as the foundation for incremental backups. |
| **Type** | Can be full or incremental depending on the backup command. | Always part of the incremental backup strategy. |
| **Backup Command** | `BACKUP DATABASE` | `BACKUP INCREMENTAL LEVEL 0 DATABASE` |
| **Includes** | Backs up all datafiles, control files, and optionally archived redo logs. | Backs up all datafiles as part of an incremental backup strategy. |
| **Incremental Strategy** | Independent and not tied to incremental backups. | Forms the base for subsequent Level 1 incremental backups. |
| **Redundancy** | Does not automatically integrate with incremental backups for space optimization. | Reduces backup size for subsequent backups when combined with Level 1 backups. |
| **Performance** | May be slower due to backing up the entire database each time. | More efficient over time with incremental backups (L1). |
| **Use Case** | For standalone or one-off backups. | For strategies requiring incremental backups to save space and time. |
| **Retention Policy** | Managed independently of incremental levels. | Retention policy can depend on the combination of L0 and L1 backups. |
---
### **Key Differences**:
1. **Purpose**:
- **Full Backup**: Typically used when you need a comprehensive backup without relying on incremental strategies.
- **L0 Backup**: Forms the baseline for an incremental backup strategy (L0 + L1).
2. **Command Example**:
- **Full Backup**:
```sql
RMAN> BACKUP DATABASE;
```
- **L0 Backup**:
```sql
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
```
3. **Integration with Incrementals**:
- **Full Backup** does not integrate with incremental strategies.
- **L0 Backup** is essential for creating a base for incremental Level 1 backups.
4. **Performance and Storage**:
- **Full Backup** requires more storage as it repeatedly backs up all database files.
- **L0 Backup**, when used with Level 1 backups, saves storage and time for subsequent backups.
---
### **Choosing Between Them**:
- Use a **Full Backup** for simple backup strategies or for standalone backups.
- Use a **Level 0 Backup** when employing incremental backup strategies to save time and space for long-term backup management.
==========================Here’s the extracted content from the images:
### Image 1: RMAN Cloning via Duplicate
**Primary Database**: PRODB (Archivelog mode on)
**Clone Database**: CLONE
1. Connect PRODB via RMAN and take database backup:
`RMAN BACKUP DATABASE PLUS ARCHIVELOG;`
2. Create Parameter file for Clone database:
`SQL> create pfile='ORACLE_HOME/dbs/initclone.ora' from spfile;`
3. Edit Parameter file as per CLONE database:
```
db_file_name_convert='/u01/app/oracle/oradata/proddb','/u01/app/oracle/oradata/clone'
log_file_name_convert='/u01/app/oracle/oradata/proddb','/u01/app/oracle/oradata/clone'
```
4. Copy clone pfile to clone server and create necessary directories.
5. Create password file for Clone database:
`ORAPWD FILE=$ORACLE_HOME/dbs/orapwclone PASSWORD=sys ENTRIES=5`
6. Copy password file and backup pieces to Clone server.
7. Configure Listener & TNS for both database entries.
8. Take CLONE DB to nomount stage.
9. Connect PRIMARY DB & AUXILIARY (Clone) DB via RMAN and fire:
```
RMAN> connect target sys/sys@proddb
RMAN> connect auxiliary sys/sys@clone
RMAN> duplicate target database to clone from active database nofilenamecheck;
```
=========================================================
Here are the high-level steps to clone an Oracle database:
==========================================================
Preparation
1)Check Source Database:
Ensure the source database is healthy and consistent.
Verify archive logging is enabled if cloning requires backups.
2)Determine Cloning Method:
Decide on the cloning method:
RMAN Duplicate Database
Manual File Copy
Data Pump Export/Import (for schema-level cloning)
3)Prepare Target Environment:
Ensure the target server has sufficient storage, memory, and compute resources.
Install Oracle software if not already installed.
Using RMAN Duplicate
Prepare Auxiliary Database:
Create a password file and initialization parameter file (init.ora) for the target database.
4)Start the Auxiliary Instance:
Start the target instance in NOMOUNT mode:
STARTUP NOMOUNT;
Set Up RMAN Connection:
Connect RMAN to the source and auxiliary instances:
RMAN TARGET sys/password@source AUXILIARY sys/password@target
5)Run RMAN Duplicate:
Use one of the following commands:
Active Database Duplication:
DUPLICATE TARGET DATABASE TO target_db_name FROM ACTIVE DATABASE NOFILENAMECHECK;
6)Backup-Based Duplication:
DUPLICATE TARGET DATABASE TO target_db_name BACKUP LOCATION '/backup/location' NOFILENAMECHECK;
Manual File Copy Method
7)Backup Source Database Files:
Backup or copy datafiles, control files, and parameter files from the source.
Copy Files to Target:
Transfer the files to the target server using tools like scp.
8)Create Target Initialization File:
Modify the source init.ora or spfile for the target database.
9)Start the Target Instance:
Start the instance in NOMOUNT mode.
10)Recreate Control Files:
Use the CREATE CONTROLFILE command to recreate control files.
11)Recover and Open the Database:
Apply archive logs or redo logs as necessary, then open the database:
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
Post-Cloning Steps
Change Database Name (Optional):
Use nid to rename the cloned database if necessary:
nid TARGET=sys/password DBNAME=new_name
12)Configure Network and Listener:
Update the tnsnames.ora and listener.ora files for the cloned database.
Verify Cloned Database:
Connect to the target database and validate:
SELECT name FROM v$database;
13)Cleanup and Optimization:
Remove unnecessary temporary files or logs.
Test the database thoroughly for functionality.
==========================
Reparing Block corruptions :
===========================
HOW TO CHECK BLOCK CORRUPTIONS IN SQLPLUS
COLUMN owner FORMAT A20
COLUMN segment_name FORMAT A30
SELECT DISTINCT owner, segment_name
FROM
v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1 ORDER BY 1,2;
RECOVERING BLOCKS USING RMAN
RMAN> blockrecover datafile 3 block 121;
OTHER BLOCK REPAIR METHODS
1. Full DB recovery
2. Individual datafile recovery
3. Drop table and restore if from previous export
===============================
2.4 Taking DB Hot Backup Notepad
===============================
File Edit Format View Help
NOTE: Can be used only when DB is running in archivelog mode
PUT DB IN BEGIN BACKUP MODE
Alter database begin backup;
COPY ALL FILES TO BACKUP LOCATION
- Create a location on OS where you can copy backup files. Eg /u02/db_backup/hot_bkp_12_jan/
- Get the location of datafiles, controlfiles, redolog files
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;
Copy above files using Linx cp command to backup location
PUT DB IN END BACKUP MODE
Alter database end backup;
TAKE MANUAL CONTROLFILE BACKUP
Alter database backup controlfile to 'filename';
Alter database backup controlfile to trace as 'filename';
BACKUP ARCHIVELOGS GENERATED DUTING THE BEGIN BACKUP AND END BACKUP MODE
SELECT THREAD#, SEQUENCE#, NAME FROM VSARCHIVED_LOG;
===========================
WHAT IS FLASHBACK?
Travelling back in time
■ Oracle backup and recovery is forward technology
■ Oracle flashback is backward technology
■ For a small mistake, its easy to flashback than to restore
■ Its an additional layer of data protection
■ Flashback helps you to rewind your database
BENEFITS OF FLASHBACK
■ Flashback does not depend on RMAN
■ You can flashback single table, query, before drop and even entire database
■ Mostly people use flashback at logical level
Many logical level flashback do not require flashback to be enabled at DB level
■ For Entire Database flashback, you must enable flashback at DB level
TYPES OF LOGICAL FLASHBACK
■ No need to enable flashback at DB level
■ ORACLE FLASHBACK QUERY:
You can run a query at a time in past and see the results as they were in the past
Some data modifications have occurred, and you want to view the state of the table as it was an hour ago.
Example: Using Flashback Query with AS OF TIMESTAMP
-- Check the current data in the table
SELECT * FROM employees;
-- View the state of the table as it was 1 hour ago
SELECT *
FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
Example: Using Flashback Query with AS OF SCN
You can also use the SCN (System Change Number) for precise time-based queries if you know the SCN.
-- Get the current SCN
SELECT CURRENT_SCN FROM V$DATABASE;
-- Use a specific SCN to view historical data
SELECT *
FROM employees
AS OF SCN 1003456;
■ ORACLE FLASHBACK TRANSACTION:You can reverse a transaction
Oracle Flashback Transaction is a feature that allows you to view or recover from transactions by querying or rolling back changes made by specific transactions. This feature can be used for auditing and recovering from logical data corruption.
Steps for Flashback Transaction :
Enable Supplemental Logging (if not already enabled):
Flashback Transaction requires supplemental logging to be enabled at the database level.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Example: Querying Transaction History
Suppose a table employees was updated or deleted, and you want to analyze the changes.
-- Query transaction history
SELECT xid, operation, undo_sql, table_name, logon_user, commit_scn, commit_timestamp
FROM FLASHBACK_TRANSACTION_QUERY
WHERE table_name = 'EMPLOYEES';
Example: Undoing a Transaction
If you identify a problematic transaction by its XID, you can reverse its effects.
Step 1: Find the XID
SELECT xid, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE table_name = 'EMPLOYEES';
Step 2: Use DBMS_FLASHBACK.TRANSACTION_BACKOUT Procedure
You can back out a specific transaction, ensuring consistency.
BEGIN
DBMS_FLASHBACK.TRANSACTION_BACKOUT (
numtxns => 1,
xids => SYS.XID_ARRAY('0500090012340000'), -- Replace with the XID
options => DBMS_FLASHBACK.CASCADE
);
END;
/
■ ORACLE FLASHBACK TABLE:
Oracle Flashback Table is a powerful feature that allows you to restore a table to a previous point in time. This can be used to recover from logical errors like accidental deletions, updates, or insertions, without the need to perform a full database recovery.
Take a table back in time without taking tablespace/DB offline.
Prerequisites
UNDO Tablespace: The database must have sufficient undo data retained to support the flashback operation.
ROW MOVEMENT: The table must have row movement enabled. This allows Oracle to adjust row IDs during the flashback process.
ALTER TABLE EMP ENABLE ROW MOVEMENT;
Privileges: The user needs FLASHBACK ANY TABLE privilege (or the FLASHBACK privilege on the specific table).
Basic Syntax:
FLASHBACK TABLE table_name
TO {TIMESTAMP | SCN} {timestamp_value | scn_value};
Example: Using Flashback Table
Step 1: Enable Row Movement
ALTER TABLE employees ENABLE ROW MOVEMENT;
Step 2: Flashback the Table
Restore the employees table to how it was 1 hour ago:
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
Restore the employees table to a specific SCN:
FLASHBACK TABLE employees TO SCN 123456;
■ ORACLE FLASHBACK DROP:You can reverse the effect of drop statement :
Oracle Flashback Drop is a feature that allows you to recover a dropped table, including its dependent objects (indexes, triggers, constraints, etc.), by retrieving it from the Recycle Bin. When a table is dropped, it is not immediately deleted but instead placed in the Recycle Bin.
Prerequisites :
- The table must be in the Recycle Bin (it hasn’t been purged explicitly or automatically).
- The user must have the necessary privileges to restore the table.
Example: Recovering a Dropped Table
Step 1: Drop a Table
DROP TABLE employees;
Step 2: Check the Recycle Bin
The Recycle Bin stores dropped objects with a system-generated name. Use the following query to list objects in the Recycle Bin:
SELECT object_name, original_name, type
FROM user_recyclebin;
Example Output:
OBJECT_NAME ORIGINAL_NAME TYPE
BIN$abcd1234$0$TABLE EMPLOYEES TABLE
Step 3: Flashback the Table
Recover the table using its original name:
FLASHBACK TABLE employees TO BEFORE DROP;
Alternatively, recover the table using its Recycle Bin name:
FLASHBACK TABLE "BIN$abcd1234$0$TABLE" TO BEFORE DROP;
=============================
BASIC CONCEPTS
==============
Backup metadata is stored in target DB control file
If catalog is configured, both control file and catalog will hold backup information
■RMAN list and report command provide extensive information
If recovery catalog is configured, then RC_ views provide backup information
■ In a data guard environment, respective DB info is stored in respective recovery catalog schemas
ABOUT RMAN LIST COMMAND
■ LIST BACKUP SUMMARY;
■ LIST BACKUP BY FILE;
LIST BACKUP OF DATABASE SUMMARY;
LIST BACKUP OF CONTROLFILE SUMMARY;
■ LIST BACKUP OF SPFILE SUMMARY;
■ LIST BACKUP TAG <tag>;
LIST ARCHIVELOG ALL;
■ LIST INCARNATION;
REPORTING ON BACKUPS:
---------------------
You can use the REPORT command to answer important questions, such as:
• Which files need a backup?
Which backups are obsolete and can be deleted?
What was the physical schema of the target database or a database in the Data Guard environment at some previous time?
. Which files have not been backed up recently?
Reports enable you to confirm that your backup and recovery strategy is in fact meeting your requirements for database recoverability.
ABOUT RMAN REPORT COMMAND:
--------------------------
REPORT NEED BACKUP;
REPORT OBSOLETE;
REPORT SCHEMA;
============================
HOW IT WORKS?
■ TBPITR is different from DBPITR
■ Its not straight restore and recover
■ Once tablespace is dropped, you cannot restore it via RMAN as details of tablespace are removed from control file
■ There are three methods to recover tablespace via RMAN:
• Create DB clone with PITR before tablespace DROP clone and import on prod
• Perform entire prod PITR→ downtime + loss of data
• Automated RMAN TBPITR
Example: Tablespace PITR
1. Identify the Target Recovery Time
Determine the point in time (timestamp or SCN) to which you want to recover.
Example of identifying the desired SCN or timestamp:
SELECT current_scn FROM V$DATABASE;
-- Alternatively, determine the time of interest
SELECT * FROM V$ARCHIVED_LOG WHERE FIRST_TIME < TO_DATE('2024-12-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
2. Put the Tablespace Offline
Before starting the recovery, put the affected tablespace offline:
ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
3. Start RMAN
Launch RMAN and connect to the target database:
rman target /
4. Recover the Tablespace
Using Time:
RECOVER TABLESPACE USERS
UNTIL TIME "TO_DATE('2024-12-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
Using SCN:
rman
RECOVER TABLESPACE USERS
UNTIL SCN 1234567;
Using Sequence Number:
rman
RECOVER TABLESPACE USERS
UNTIL SEQUENCE 100;
RMAN automatically uses the archived logs and backups to perform the recovery.
5. Restore the Tablespace
Restore the datafiles for the tablespace from backup:
rman
RESTORE TABLESPACE USERS;
6. Bring the Tablespace Online
After recovery, bring the tablespace back online:
ALTER TABLESPACE USERS ONLINE;
============================
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;
SQL> shutdown immediate
================
BACKUPSET:-
==============
In Oracle RMAN (Recovery Manager), a backup set is a logical grouping of one or more physical files called backup pieces.
Each backup piece contains the backup of datafiles, control files, or archived redo logs. A backup set can include multiple backup pieces,
which allows for more efficient storage and management of backups.
===============================
Importance of Recovery Catalog :
================================
RMAN stores the backup information into the control file
If we loose control file, we loose all the backup information
Even though the backups are available, they are not usable. If you lose the control file, the backups are of no use as Oracle
or RMAN doesn’t know which backup belongs to which database. The reason behind this is the information is stored in the control file which you lost
To resolve all the above three issues, we use recovery catalog.
Recovery catalog is a separate database that stores backup information
One recovery catalog can store multiple target database backup information
You don’t have to create multiple recovery catalog for multiple target databases. You can create one recovery catalog database for all the databases or servers
Benefits of Recovery catalog
======================================
It helps in database recovery when you lose the control file
You can use recovery catalog for backups reporting also
You can write RMAN scripts in recovery catalog and save them
You can store RMAN scripts in recovery catalog
Store long term backup history
Cloning with PITR (Point in time recovery) becomes easier as the recovery catalog knows which Control file is required
Recovery catalog configuration
==============================
Create a default tablespace under rcat database which will be used for backup information storage
SQL> create tablespace rmantbs datafile '/u01/app/oracle/oradata/DEVDB/rmantbs01.dbf' size 50m;
You can create a small tablespace and keep on adding the space when You get some alerts or when the database throws some errors. We are going with the very small size of 50 MB.
Next, Create a user which will be used to connect recovery catalog and store backup information. We will create the user rman_rc to connect to the recovery catalog.
This user rman_rc will store the information into rmantbs tablespace
SQL> create user rman_rc identified by rman_rc default tablespace rmantbs temporary tablespace temp;
Grant permissions to rman_rc user
SQL> grant connect,resource,recovery_catalog_owner to rman_rc;
Next, Configure networking between target server and catalog server. Make sure to add tns entry details of catalog server on target server.
In my case, below is the tns entry of catalog server
On target server, start RMAN utility and connect only the catalog database. We will not create catalog on the catalog database
rman catalog rman_rc/rman_rc@devdb
RMAN> create catalog;
This will create all the necessary tables and views which are required to store the backup information in the user rman_rc and into the database rman_rc@rcat
Exit RMAN and now connect both target and catalog database
rman target / catalog rman_rc/rman_rc@devdb
We need to register our target database to the recovery catalog database. Use below command to register target database into catalog database:
RMAN> CREATE CATALOG;
recovery catalog created
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/r_backups/%U';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'u01/app/oracle/r_backups/%F';
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/r_backups/snapcf_prod.f';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/r_backups/c-%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
===================================
Take RMAN DB FULL Backup
===================================
Connect to the target DB and catalog. Take DB full backup
RMAN> backup database plus archivelog;
Once backup is completed, check backup tag via below command
RMAN> list backup of database summary;
Create User and Table
SQL> create user mgr2 identified by mgr2;
SQL> grant connect, resource, create session to mgr2;
SQL> conn mgr2/mgr2
SQL> create table test(serial number(2),name varchar2(5));
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');
SQL> commit;
Simulate Failure
Get the location of spfile, datafile & control file
SQL> show parameter spfile;
SQL> select name from v$controlfile;
SQL> select name from v$datafile;
Delete all the spfile, datafiles & Control files from server
rm -rf <DF locations>
rm -rf <control file locations>
rm -rf <spfile location>
Start Database Recovery
Kill the DB instance, if running. You can do shut abort or kill pmon at OS level
Connect to RMAN and issue below command
RMAN> STARTUP FORCE NOMOUNT;
RMAN> Restore spfile from autobackup;
RMAN> STARTUP FORCE NOMOUNT;
RMAN> Restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> Restore database from tag TAG20160618T204340;
RMAN> Recover database;
RMAN> sql 'alter database open RESETLOGS';
case AUTOBACKUP is OFF, then Restore SPFILE & Control File using below
RMAN> list backup of spfile summary;
RMAN> list backup tag <give-latest-tag>;
RMAN> Restore spfile from tag '<give-latest-tag>';
RMAN> list backup of controlfile summary;
RMAN> list backup tag <give-latest-tag>;
RMAN> Restore controlfile from tag '<give-latest-tag>';
==================================
RMAN Incremental Backup & Recovery
===================================
Take RMAN Incremental Backup
Connect to the target DB and catalog. Take level 0 backup
RMAN> backup incremental level 0 database plus archivelog;
Once backup is completed, check backup tag via below command
RMAN> list backup of database summary;
TAG20170115T113749 -–> L0 Backup tag
Create New User & Table
SQL> create user ogr identified by ogr;
SQL> grant connect, resource, create session to ogr;
SQL> conn ogr/ogr
SQL> create table test(serial number(2),name varchar2(5));
SQL> insert into test values(1,'one');
SQL> insert into test values(2,'Two');
SQL> insert into test values(3,'Three');
SQL> insert into test values(4,'Four');
SQL> commit;
Trigger DB L1 Backup
RMAN> backup incremental level 1 database plus archivelog;
Once backup is completed, check backup tag via below command
RMAN> list backup of database summary;
TAG20170115T114127 -–> Level 1 Backup tag
Simulate Failure
Delete all the datafiles from server
SQL> select name from v$datafile;
rm -rf <DF locations>
Start Database Recovery
Kill the DB instance, if running. You can do shut abort or kill pmon at OS level
Start the DB instance and take it to Mount stage. Connect to RMAN and issue below command
run
{
RESTORE DATABASE from tag TAG20170115T113749;
RECOVER DATABASE from tag TAG20170115T114127;
RECOVER DATABASE;
sql 'ALTER DATABASE OPEN';
}
============================================
RECOVE CONTROL WITH TRACE (Human readble format it will create )
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/tracectlfile.ora';
Manually controlfile ceation last option -
CREATE CONTROLFILE REUSE DATABASE "DEVDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ('/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_1_m8qcn7h8_.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_m8qcn7mw_.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_3_m8qcn7yd_.log') SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_m8qch7gh_.dbf',
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_m8qcklw3_.dbf',
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_m8qclp7o_.dbf',
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_m8qclqjc_.dbf'
CHARACTER SET AL32UTF8;
========================================================
SQL> SELECT DBID FROM V$DATABASE; (PROD)
DBID
----------
585924851
SELECT DBID FROM V$DATABASE;(DEVDB)
DBID
----------
1078482196
=========================================
Automate RMAN Backup using Shell Script:
=========================================
Create directory structure
mkdir -p /u01/rman
mkdir scripts logs backups
Create RMAN backup script file:
vi /u01/rman/full_backup.sh
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/19/db_1
export DATE=$(date +%y-%m-%d_%H%M%S)
rman target / log=/u01/rman/logs_${DATE}.log << EOF
run
{
allocate channel ch1 device type disk format '/u01/rman/backups/db_full_bkp_%u';
allocate channel ch2 device type disk format '/u01/rman/backups/db_full_bkp_%u';
crosscheck backup;
delete noprompt obsolete;
backup database;
backup archivelog all delete input;
release channel ch1;
release channel ch2;
}
EOF
chmod 775 /u01/rman/scripts/full_bkp.sh
crontab -e
25 10 * * * /u01/rman/scripts/full_bkp.sh
*/5 * * * * /u01/rman/scripts/arch_bkp.sh
=============================================
Oracle RMAN Cold Backup:
=============================================
It’s easy to fire RMAN backups when database is running in archive log mode. RMAN will take your database backup along with archive logs.
Put DB in mount mode
Take full backup
Open the database
You can even use RMAN to take backup of a database running in NOARCHIVELOG mode. But the condition is the database must not be open.
You can fire the RMAN full backup in MOUNT mode.
Put DB in mount mode:
SQL> startup mount;
Take full backup:
RMAN> backup database format '/u02/backup/testdb/full_bkp_%U';
Open the database
alter database open;
===============================
Tablespace Point-in-time Recovery
================================
CREATE TEST TABLESPACE & USER FOR ACTIVITY
create tablespace TBPITR datafile '/u01/app/oracle/oradata/PROD/tbpitr.dbf' size 10M;
create user tbtest identified by tbtest;
alter user tbtest default tablespace TBPITR;
grant connect, resource to tbtest;
grant select on sbz.temp to tbtest;
Now connect sqlplus with tbtest user and create a new emp table by selecting scott.emp table
SQL> conn tbtest/tbtest
SQL> create table emp as select * from sbz.temp;
Take RMAN backup for the activity. I am taking database backup in a specific location
RMAN> backup incremental level 0 database plus archivelog;
Query to check database size
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
DROP THE TABLESPACE AND START TBPIT PROCESS
Now we will drop the tablespace and then we will see that we can recover it via RMAN or not.
drop tablespace TBPITR including contents and datafiles;
If you connect to tbtest user and query emp table, you will get below error
SQL> conn tbtest/tbtest
SQL> select * from emp:
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
open alert log and check the time:
2024-07-25T02:04:06.287548-07:00
Thu Jul 25 02:04:22 PDT 2024
( Mon Jan 16 11:01:542017)
CONNECT TO RMAN AND START RECOVERY
Before you fire below command, make sure you have same or more space in auxiliary destination as your original database.
The auxiliary destination is the location where RMAN will automatically create the destination for the clone instance.
Then export the tablespace from the cloned instance. It will put it back to the original location and then RMAN will clean this auxiliary destination also.
run{
recover tablespace TBPITR until time "to_date('25-jul-24 02:04:06','dd-mon-rr hh24:mi:ss')"
auxiliary destination '/u01/bkp/';
}
ALTER SYSTEM SET db_create_file_dest='/u01/bkp' SCOPE=BOTH;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/bkp' SCOPE=BOTH;
ALTER SYSTEM SET ENCRYPTION WALLET close IDENTIFIED BY "wallet$123";
====================================
RMAN Duplicate from Active Database
====================================
Active database duplication does not require backup of the source database. It duplicates the live source database to the destination host by copying the database files over the network to the auxiliary (clone) instance. RMAN duplicate database can copy the required files as image copies or backup sets.
Prerequisites
Before you begin with this activity, make sure below items are addressed
Source database is running in Archive log mode
Check for space requirements on target server
Servers are able to ping each other
Clone database SID will be same as source database
1) Configure Listener on clone server to accept incoming connections
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_home)
(SID_NAME = prod)
)
)
Start the listener on clone server
lsnrctl start listener
Configure tns entries on the prod server
prod_clone =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)
)
Make sure to test the tnsping is working from prod to clone server
tnsping prod_clone
2)On prod server, create pfile from spfile and copy to clone server
SQL> create pfile from spfile;
scp $ORACLE_HOME/dbs/initprod.ora oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs
Also copy the password file from prod to clone server
scp $ORACLE_HOME/dbs/orapwprod oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs
If no password file exists, create one via below command and then copy
orapwd file=$ORACLE_HOME/dbs/orapwprod force=y
On the clone server, open the pfile that you copied from prod server and create directory locations
cat $ORACLE_HOME/dbs/initprod.ora
In my case I had to create below two main locations
[ Note for different SID cloning change in PFILE apart from all same .
On the clone server, open the pfile that you copied from prod server. Replace prod with clone SID
vi $ORACLE_HOME/dbs/initclone.ora
:%s/prod/clone --> replace prod with clone
Add below two parameters to change data files and log files locations while cloning database
*.db_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone'
*.log_file_name_convert='/u01/app/oracle/oradata/prod','/oradb/app/oracle/oradata/clone'
Save & exit ]
mkdir -p <control_file_locations>
mkdir -p <adump_locations>
Also create data files and redo log files locations from source on clone server
mkdir -p <soruce_db_files_loc>
mkdir -p <source_redo_files_loc>
3)Take clone server database to nomount stage
export ORACLE_SID=prod
sqlplus / as sysdba
SQL> startup nomount;
SQL> exit;
Cloning will fail if you do not exit from sqlplus
Connect to rman on prod server to both prod database and also the auxiliary (clone) database
rman target sys auxiliary sys@prod_clone
--> give same sys password for both target and auxiliary
Lets duplicate the database via RMAN duplicate from active database
RMAN> duplicate target database to 'prod' from active database nofilenamecheck;
===============================
Example of Block Corruption
Let’s simulate logical corruption in a test environment:
===============================
Check for Existing Corrupt Blocks: Use the DBMS_REPAIR.CHECK_OBJECT procedure to identify corrupt blocks.
BEGIN
DBMS_REPAIR.CHECK_OBJECT(
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'EMP',
REPAIR_TABLE => 'REPAIR_TABLE',
FLAGS => DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
);
END;
/
dd if=/dev/zero of=/u02/oradata/PROD/test_tbs01.dbf bs=1k count=1 seek=10
[oracle@db1 PROD]$ dd if=/dev/zero of=/u02/oradata/PROD/users01.dbf bs=1k count=1 seek=10
1+0 records in
1+0 records out
1024 bytes (1.0 kB, 1.0 KiB) copied, 0.000269341 s, 3.8 MB/s
[oracle@db1 PROD]$
Verify Corruption: Run a query on the affected table to see the error, typically ORA-01578.
sql
Copy code
SELECT * FROM HR.EMPLOYEES;
You might get an error like:
wasm
Copy code
ORA-01578: ORACLE data block corrupted (file # 4, block # 10)
Solution to Block Corruption
Step 1: Identify Corrupt Blocks
Query the V$DATABASE_BLOCK_CORRUPTION view to find corrupt blocks:
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
Step 2: Repair Logical Corruption
Use the DBMS_REPAIR package to skip corrupt blocks and allow continued operations:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(
SCHEMA_NAME => 'sys',
OBJECT_NAME => 'EMP'
);
END;
/
Recreate the corrupted table or export/import data if possible.
Step 3: Restore Corrupt Blocks
Use RMAN to recover corrupt blocks:
RMAN> BLOCKRECOVER CORRUPTION LIST;
Alternatively, recover specific blocks:
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 10;
Step 4: Restore from Backup
If corruption persists and RMAN recovery fails, restore and recover the datafile:
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
Preventive Measures
Enable Block Checking:
ALTER SYSTEM SET DB_BLOCK_CHECKING=TRUE;
Enable Block Corruption Detection:
ALTER SYSTEM SET DB_BLOCK_CHECKSUM=FULL;
Use ASM for Storage: ASM includes automatic corruption detection and repair features.
Regular Backups with RMAN: Schedule regular backups and validate them:
RMAN> BACKUP VALIDATE DATABASE;
Monitor Database Health: Use DBA_HIST_BLOCK_STAT and V$ views for proactive monitoring.
=================
TABLE FLASHBACK
=================
SQL> show parameter recy;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
SQL> conn c##sbz/sbz;
Connected.
SQL> show user;
USER is "C##SBZ"
SQL> CREATE TABLE emp (
emp_id NUMBER(5) PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL,
job_title VARCHAR2(30),
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(10, 2),
dept_id NUMBER(5)
); 2 3 4 5 6 7 8
Table created.
SQL> INSERT INTO emp (emp_id, emp_name, job_title, hire_date, salary, dept_id)
VALUES (101, 'John Doe', 'Manager', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 75000, 10);
INSERT INTO emp (emp_id, emp_name, job_title, hire_date, salary, dept_id)
VALUES (102, 'Jane Smith', 'Analyst', TO_DATE('2023-02-01', 'YYYY-MM-DD'), 50000, 20);
1 row created.
SQL> SQL> 2
1 row created.
SQL>
SQL>
SQL> commit;
Commit complete.
SQL> select * from emp;
EMP_ID EMP_NAME
---------- --------------------------------------------------
JOB_TITLE HIRE_DATE SALARY DEPT_ID
------------------------------ --------- ---------- ----------
101 John Doe
Manager 15-JAN-23 75000 10
102 Jane Smith
Analyst 01-FEB-23 50000 20
SQL> drop table emp;
Table dropped.
SQL> commit;
Commit complete.
SQL> select * from emp;
select * from emp
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$KfGRahJUDrrgY2UBqMD1gg==$0 TABLE 2024-12-23:06:11:08
SQL> conn sys / as sysdba
Enter password:
Connected.
SQL> show recyclebin;
We can restore with 3 types :
FLASHBACK TABLE "BIN$KfGRahJUDrrgY2UBqMD1gg==$0" TO BEFORE DROP;
FLASHBACK Table C##SBZ.EMP TO Before DROP;
FLASHBACK TABLE C##SBZ.EMP TO RENAME EMP1;
SQL> FLASHBACK TABLE "BIN$KfGRahJUDrrgY2UBqMD1gg==$0" TO BEFORE DR OP;
Flashback complete.
SQL> SELECT * FROM EMP;
EMP_ID EMP_NAME
---------- --------------------------------------------------
JOB_TITLE HIRE_DATE SALARY DEPT_ID
------------------------------ --------- ---------- ----------
101 John Doe
Manager 15-JAN-23 75000 10
102 Jane Smith
Analyst 01-FEB-23 50000 20
========================
FLASHBACK WITH SCN
=====================
SQL> select current_scn,systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
4314551 23-DEC-24 06.32.13.431506 AM -08:00
SQL> select * from emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
102 Jane Smith Analyst 01-FEB-23 50000 20
101 John Doe Manager 15-JAN-23 75000 10
SQL> delete from emp where emp_id='102';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
101 John Doe Manager 15-JAN-23 75000 10
SQL> rollback;
Rollback complete.
SQL> select * from emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
101 John Doe Manager 15-JAN-23 75000 10
ENABLE ROW MOVEMENT
conn / as sysdba
select table_name,row_movement from dba_tables where table_name='EMP';
alter table c##sbz.emp enable row movement;
flashback table emp to scn 4314551;
select * from emp;
SQL> select table_name,row_movement from dba_tables where table_name='EMP';
TABLE_NAME ROW_MOVE
-------------
EMP DISABLED
EMP ENABLED
SQL> flashback table emp to scn 4314551;
Flashback complete.
SQL> select * from emp;
EMP_ID EMP_NAME JOB_TITLE HIRE_DATE SALARY DEPT_ID
---------- -------------------------------------------------- ------------------------------ --------- ---------- ----------
102 Jane Smith Analyst 01-FEB-23 50000 20
101 John Doe Manager 15-JAN-23 75000 10
===================================
Steps to Perform Flashback Database
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------
YES
1. Identify the Target Time or SCN
Decide where you want to flash back:
Using Time:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
Replace SYSDATE with your desired time.
Using SCN (System Change Number):
SELECT CURRENT_SCN FROM V$DATABASE;
2. Shutdown the Database
Put the database in a state suitable for Flashback:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
3. Perform the Flashback
Flashback to the desired point in time or SCN:
Using Time:
FLASHBACK DATABASE TO TIMESTAMP TO_DATE('24-DEC-2024 10:00:00', 'DD-MON-YYYY HH24:MI:SS';
Using SCN:
FLASHBACK DATABASE TO SCN 123456;
Open the Database
After the Flashback, open the database with RESETLOGS:
ALTER DATABASE OPEN RESETLOGS;
===============================
Comments
Post a Comment