Oracle 18c Cloud Database Cloning by Instance Snapshots

                       

Description:-

  • Instance snapshots provide an easy way to create a customized machine image using an existing instance as a template.
  • You can then use this customized machine image to create multiple instances with identical configurations.
  • To clone an instance using an instance snapshot, first create an instance using an appropriate machine image.
  • Development or testing purposes no need to go for Rman  active cloning in the cloud service.
  • It is very simple to create a snapshot of your production database and clone it in a few minutes.

Let’s Start the Demo:-

Step1:-Creating a new 18c database and open it.

Source Database = orcl18c
Target Database=dev18c

Create a new 18c cloud database using Cloud account.

Step2:-Create the table for testing in orcl18c source database

We will check this table after cloning is done

Check there is no snapshots taken before

Enter the snapshot name “snap18cdatabase”

Step3:-Here we go for the create snapshot.

Automatically the database is placed into the backup mode and the session is hang couple of mints

Step4:- Snapshot takes few mints to complete

After the snapshot is completed the table created successfully

Step5:- Snapshot is ready and clone the database

Step6:-Clone the dev18c database using snapshot “snap18cdatabase”

provide the service name and the database name Dev18c for the clone database and create it

Step7:-After few mints the Dev18c database ready for use

Step8:-Finally the table is shows in cloned database dev18c

Reference:-

https://docs.oracle.com/en/cloud/iaas/compute-iaas-cloud/stcsg/cloning-instance-using-instance-snapshots.html#GUID-3232455D-5B55-4D71-B73B-E2E86A52937F

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

 

Data Recovery Advisor in Oracle Database 18c

Description:-

  • This feature is available form Oracle 11g called the Data Recovery Advisor.
  • Data Recovery Advisor tool will automatically diagnose data failures, such as corruption or loss of persistent datafile on disk.
  • Prior to Oracle Database 11g,only block corruptions that were detected by RMAN were recorded in V$DATABASE_BLOCK_CORRUPTION.
  • Starting with Oracle Database 11g,several database components and utilities, including RMAN and Automatic Diagnostic
    Repository (ADR), can detect a corrupt block and record it in that view.
  • Oracle Database automatically updates this view when block corruptions are detected or repaired.
  • Data Recovery Advisor tool is used for recovery process to complete the  Mean Time To Recover (MTTR).

Detection and Diagnosis Using Data Recovery Advisor.

Data Recovery Advisor commands are

  1. List Failure
  2. Advise Failure
  3. Repair Failure
  4. Change Failure
  5. Validate
  • The Data Recovery Advisor automatically diagnoses data failures, determines and presents
    appropriate repair options, and performs repair operations at the user’s request.
  • Data Recovery Advisor improves the manageability and reliability of an Oracle database.
  • You can use Data Recovery Advisor to troubleshoot primary databases,logical standby databases, and snapshot
    standby databases

Let’s Start the Demo:-

  • This article i am going to corrupt the block and recover using ADR Method. The demo is done on 18c database the same way for 12c also.
  • I have corrupted blocks on my demo 18c database affecting the oracledba table of the tablespace hari

Step1:-Create tablepsace(Hari) and table(oracledba) for Demo.

New Tablespace

SQL> create tablespace hari datafile ‘/u02/app/oracle/oradata/orcl18c/hari.dbf’ size 5m;
Tablespace created.

Create table and store in the new tablespace

SQL> create table oracledba tablespace hari as select * from all_objects;
Table created.

SQL> select count(*) from oracledba;
COUNT(*)
———-
72884

Step2:-Check the Block Header of the Table

SQL> select segment_name , header_file , header_block from dba_segments where segment_name = ‘ORACLEDBA’;

SEGMENT_NAME     HEADER_FILE                HEADER_BLOCK
———–          ————                         ————–

ORACLEDBA                          17                                         130

Step3:-Before going to corrupt the block take rman backup

[oracle@orcl18c ~]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Mon May 21 20:25:09 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL18C (DBID=518253543)
RMAN> backup tablespace hari;
Starting backup at 21-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u02/app/oracle/oradata/orcl18c/hari.dbf
channel ORA_DISK_1: starting piece 1 at 21-MAY-18
channel ORA_DISK_1: finished piece 1 at 21-MAY-18
piece handle=/u03/app/oracle/fast_recovery_area/ORCL18C/backupset/2018_05_21/o1_mf_nnndf_TAG20180521T202524_fj6bsob8_.bkp tag=TAG20180521T202524 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-MAY-18
Starting Control File and SPFILE Autobackup at 21-MAY-18
piece handle=/u03/app/oracle/fast_recovery_area/ORCL18C/autobackup/2018_05_21/o1_mf_s_976739128_fj6bsrrc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-MAY-18

Step4:-Corrupt the block using OS command (DD).                                                                     (Do Not Try This in Client Database)

cd /u02/app/oracle/oradata/orcl18c/

Step5:-After the Block Corruption to check using DBV Utility

dbv file=hari.dbf

Here we are unable to get the count of the table (oracledba)

[oracle@orcl18c trace]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 18.0.0.0.0 Production on Mon May 21 21:00:18 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.1.0.0.0
SQL> select count(*) from oracledba;
select count(*) from oracledba
*
ERROR at line 1:
ORA-28304: Oracle encrypted block is corrupt (file # 17, block # 130)
ORA-01110: data file 17: ‘/u02/app/oracle/oradata/orcl18c/hari.dbf’

Step6:-Recover the Corrupt block Using Data Recovery Advisor Concept

VALIDATE CHECK LOGICAL TABLESPACE hari;List Failure:-

Advise failure all:-

Repair failure:-

[oracle@orcl18c trace]$ sqlplus ‘/as sysdba’
SQL*Plus: Release 18.0.0.0.0 Production on Mon May 21 21:27:42 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production
Version 18.1.0.0.0

SQL> select count(*) from oracledba;
COUNT(*)
———-
72884

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/18/bradv/diagnosing-repairing-failures-dra.html#GUID-8C219B50-1F7F-4F7A-95EE-5F029AE7EB2A

Catch Me On:- Hariprasath Rajaram

LinkedIn:    https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
FB Group:   https://www.facebook.com/groups/894402327369506/
FB Page:      https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba