Description : In this blog, we are going to see the Oracle database restore point and its types with the demo.Normal Restore Point:
A normal restore point enables you to flash the database back to a restore point within the time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
A normal restore point can be dropped explicitly.
The control files stores name of the restore point and the SCN.
Guaranteed restore point:
A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting.
Guaranteed restore point must be dropped explicitly by the user using the DROP RESTORE POINT command.
Guaranteed restore point never ages out.
Normal Restore Point Demo:Must DB in archive log mode.SQL> archive log list;
Database log mode Archive Mode
Automatic archival EnabledCheck available restore point:SQL> select name from v$restore_point;
no rows selectedCreate a normal restore point:SQL> create restore point res_test;
Restore point created.View the created restore point:SQL> select name from v$restore_point;
NAME
——————–
RES_TESTAfter the creation of the restore point creates a table.SQL> create table test as select * from dba_users;
Table created.Check the RVWR process status. In normal restore point no background process are started.
[oracle@primary ~]$ ps -ef |grep -i rvwr
oracle 16948 14963 0 09:40 pts/2 00:00:00 grep –color=auto -i rvwrFlashback the restore point:SQL> flashback database to restore point res_test;
Flashback complete.Guaranteed Restore Point Demo:Create guarantee restore point:SQL> create restore point res_test1 guarantee flashback database;
Restore point created.RVWR back ground process can be started automatically.[oracle@primary ~]$ ps -ef |grep -i rvwr
oracle 17014 1 0 09:41 ? 00:00:00 ora_rvwr_orcl
oracle 17060 14963 0 09:42 pts/2 00:00:00 grep –color=auto -i rvwr
[oracle@primary ~]$Check the flashback status:SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
RESTORE POINT ONLYAutomatically create flashback logs:[oracle@primary ORCL]$ cd flashback/
[oracle@primary flashback]$ ls
o1_mf_jtotn2c3_.flb o1_mf_jtotn8d4_.flbDrop restore point the logs will be removed.SQL> drop restore point res_test1;
Restore point dropped.
[oracle@primary ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL/flashback
[oracle@primary flashback]$ ls
[oracle@primary flashback]$Bounce database and Flashback database to restore point:SQL> flashback database to restore point res_test2;
Flashback complete.Reset the logs:SQL> alter database open resetlogs;
Database altered.SQL> select count(*) from test1;
COUNT(*)
———-
45 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:Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
Steps to Restore a TDE Database backup file of Source on Destination Server.
step 1) Create a new Master Key or Alter it using below if it already exists.
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′; — This can be from Source Server/New one.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′
step 2)Restore the Master DB Certificate of Source Server on Destination Server:
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
fROM FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
DECRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
step 3)Finally Restore the TDE Enabled DB Backup File on Destination.
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:
• Create Master Key on MASTER DB
• Backup Master Key of Master DB
• Create Certificate on MASTER DB
• Backup Certificate on MASTER DB
• Create Encryption Key on User DB.
• Finally Enable TDE on User DB
At Source Server: Step 1:Create Database Master Key on Master DB.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′;
GO
–Use this if Master key already exists and to add a new Master Key.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′;
Step-2: Backup Master Key of MASTER DB:
USE MASTER
BACKUP MASTER KEY
TO FILE = ‘D:\OracleAgent\TDE\MasterKey.key’
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
Step 3:Create a Certificate on Master DB to support TDE
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
WITH SUBJECT=’Database_Encryption’;
GO
Step-4:Backup Master DB Certificate & Private Key;
This step is not required to encrypt a database using TDE. But to make sure you can recover your encrypted data from a database backup, should your instance database become corrupted, or you want to move an encrypted database to another server, you should backup the certificate.
Run the following code:
USE master;
GO
BACKUP CERTIFICATE TDE_Cert_New
TO FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
Note: Store the PASSWORD in a safe place.
Step 5: Create Database Encryption Key on required User DB.
USE Advworks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_New;
Step 6: Enable TDE on Database
ALTER DATABASE Advworks SET ENCRYPTION ON;
In below document i mentioned how to restore in destination server.
Description :
In this blog, we are going to see the Oracle database restore point and its types with the demo.Normal Restore Point:
