ORACLE DATABASE INCARNATION

ORACLE DATABASE INCARNATION 

The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.

Database incarnation falls into following  category:-

Current, Parent, Ancestor, Sibling

i) Current Incarnation : The database incarnation in which the database is currently generating redo.

ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.

iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.

iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.

View incarnation history :-

Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;

RMAN> LIST INCARNATION;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       LIVEDB   3038906043       PARENT  1          17-APR-19

2       2       LIVEDB   3038906043       PARENT  1920977    13-SEP-21

4       4       LIVEDB   3038906043       CURRENT 2146559    15-SEP-21

3       3       LIVEDB   3038906043       ORPHAN  2146579    15-SEP-21

 

backup full database

[oracle@vignesh ~]$ . livedb.env

[oracle@vignesh ~]$ rman target /

connected to target database: LIVEDB (DBID=3038906043)

RMAN> backup database;

Starting backup at 15-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=82 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=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf

channel ORA_DISK_1: starting piece 1 at 15-SEP-21

channel ORA_DISK_1: finished piece 1 at 15-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp tag=TAG20210915T035943 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:06

Finished backup at 15-SEP-21

Starting Control File and SPFILE Autobackup at 15-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_15/o1_mf_s_1083297650_jn28ntoh_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 15-SEP-21

RMAN> exit

After backup create a table in the database

 [oracle@vignesh ~]$ . livedb.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> create table sample as select * from all_objects;

Table created.

After  that take current scn number

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    2146558

Delete the rows in the sample table

SQL> delete from sample;

71297 rows deleted.

Get the scn number also

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    2146578

Do the log switching

SQL> alter system switch logfile;

System altered.

Check the incarnation using v$database_incarnation

SQL> select incarnation#, resetlogs_change# from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE#

------------ -----------------

               1                        1

               2              1920977

Shutdown and start the database in mount stage

SQL>  shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Database mounted.

Connect RMAN and recover the database mention the sequence number where the table record deleted

[oracle@vignesh ~]$ rman target /

connected to target database: LIVEDB (DBID=3038906043, not open)

RMAN> run{

set until scn=2146578;

restore database;

recover database;

}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 15-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=45 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp tag=TAG20210915T035943

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 15-SEP-21

Starting recover at 15-SEP-21

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 15-SEP-21

After recovery completed perform resetlogs to open the database

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit                                                                

Connect database to check incarnation also check the table can be retrieved

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL>  select incarnation#, resetlogs_change# from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE#

------------ -----------------

               1                        1

               2              1920977

               3              2146579

The table structure retrieved but record can be deleted the scn number stage

SQL>  select * from sample;

no rows selected

Shut the database and start mount stage again

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Database mounted.

SQL> exit

Connect RMAN again

[oracle@vignesh ~]$ rman target /

connected to target database: LIVEDB (DBID=3038906043, not open)

Reset the the database incarnation 2 to retrieve the table records mention the scn where table created

RMAN> reset database to incarnation 2;

using target database control file instead of recovery catalog

database reset to incarnation 2

RMAN> run{

set until scn=2146558;

restore database;

recover database;

}2> 3> 4> 5>

executing command: SET until clause

Starting restore at 15-SEP-21

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmz2qqys_.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmz2rvhs_.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmz2snrh_.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmz2sovr_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_15/o1_mf_nnndf_TAG20210915T035943_jn28lqm2_.bkp tag=TAG20210915T035943

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:55

Finished restore at 15-SEP-21

Starting recover at 15-SEP-21

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_18_jn28r8l8_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_15/o1_mf_1_18_jn28r8l8_.arc thread=1 sequence=18

media recovery complete, elapsed time: 00:00:02

Finished recover at 15-SEP-21

Perform reset logs method

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit

Check the record can be retrieved the table

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> select count(1) from sample;

  COUNT(1)

----------

     71297

DATABASE LINKS IN ORACLE

 

DATABASE LINKS IN ORACLE

A database link is a pointer that defines a one-way communication path from an Oracle Database server to another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the link, you must be connected to the local database that contains the data dictionary entry.

A database link connection is one-way in the sense that a client connected to local database A can use a link stored in database A to access information in remote database B, but users connected to database B cannot use the same link to access data in database A. If local users on database B want to access data on database A,then they must define a link that is stored in the data dictionary of database B

Types

Private database link           

This link is more secure than a public or global link, because only the owner of the private link, or subprograms within the same schema, can use the link to access the remote database.

Public database link 

When many users require an access path to a remote Oracle Database, you can create a single public database link for all users in a database.

 

Rcdb is an database for target server

Clone as an database for datalink server

Step 1: create a user in the target server and grant the privilages

[oracle@vignesh ~]$ . rcdb.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> create user vignesh identified by vbt;

User created.

SQL> grant connect,resource to vignesh;

Grant succeeded.

SQL> alter user vignesh default tablespace users quota unlimited on users;

Step 2:connect  the user create table and insert the sample values

SQL> conn vignesh/vbt

Connected.

SQL> create table sample(id int,name varchar2(20));

Table created.

SQL>  insert into sample values(1,'vignesh');

1 row created.

SQL>  insert into sample values(2,'vbt');

1 row created.

SQL> commit  ;

Commit complete.

SQL> select * from sample;

        ID NAME

---------- --------------------

         1 vignesh

         2 vbt

Step 3: check listener and tns names entry in the network files and ping the two database target and database link database.

[oracle@vignesh ~]$ cd $ORACLE_HOME/network/admin

[oracle@vignesh admin]$ ls

listener.ora  samples  shrept.lst  sqlnet.ora  tnsnames.ora

 [oracle@vignesh admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:23

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Linux Error: 111: Connection refused

[oracle@vignesh admin]$ lsnrctl start listener

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:40

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production

System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/vignesh/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vignesh.localdomain)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias                     listener

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                13-SEP-2021 23:17:40

Uptime                    0 days 0 hr. 0 min. 1 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/vignesh/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vignesh.localdomain)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "livedb" has 1 instance(s).

  Instance "livedb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@vignesh admin]$ tnsping rcdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:47

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.164.128

OK (10 msec)

[oracle@vignesh admin]$ tnsping clone

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:51

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.164.128)

OK (0 msec)

[oracle@vignesh admin]$ tnsping rcdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-SEP-2021 23:17:51

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.164.128)

OK (0 msec)

Step 4: create private database link to connect the target database user table:

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL> create database link v_link connect to vignesh identified by vbt using 'rcdb';

Database link created.

After create the link we can view the target database table using the db_link

SQL> desc sample@v_link;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)

 NAME                                               VARCHAR2(20)

SQL> select * from sample@v_link;

        ID NAME

---------- --------------------

         1 vignesh

         2 vbt

Note: private link only accessible for sys users only.

Create other user to connect the db link its not working

SQL> create user vbt identified by vbt;

User created.

SQL> grant connect,resource to vbt;

Grant succeeded.

SQL> conn vbt/vbt

Connected.

SQL>  desc sample@v_link;

ERROR:

ORA-02019: connection description for remote database not found

Step 5:create a public link to access all user in the db link database to target database table.

SQL> create public database link b_link  connect to vignesh identified by vbt using 'rcdb';

Database link created.

SQL> desc sample@b_link;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)

 NAME                                               VARCHAR2(20)

SQL> conn vbt/vbt

Connected.

SQL> desc sample@b_link

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)

 NAME                                               VARCHAR2(20)

SQL> select * from sample@b_link;

        ID NAME

---------- --------------------

         1 vignesh

         2 vbt

Note: the public link access the target database all the users

Step 6: insert the values in the target database using database link

SQL> insert into sample@b_link values(3,'tharan');

1 row created.

SQL> commit;

Commit complete.

SQL>  select * from sample@b_link;

        ID NAME

---------- --------------------

         3 tharan

         1 vignesh

         2 vbt

Step 7:check the target database can be updated the new record

[oracle@vignesh ~]$ . rcdb.env

[oracle@vignesh ~]$ sqlplus / as sysdba

SQL>  conn vignesh/vbt

Connected.

SQL>

SQL> select * from sample;

        ID NAME

---------- --------------------

         3 tharan

         1 vignesh

         2 vbt