Database Links

Establishing a link from one database to another database is called a database link.
Using database links we can access the data existing in a remote database from a database.
Database links internally depends on oracle networking.
In order to create a database link, users should have 'create database link' privilege.

Note : Using database links, we can access the data and we can also perform DML operations but not DDL operations.
There are 2 types of datalinks :
1.private database link
2.public database link
In order to create a public database link, user should have 'create database link' privilege.
As the volume of data increases in the source table, accessing the data thru database links degrades the performance.
To overcome this problem oracle has introduced materialized views.
In older versions of oracle, materialized views are called snapshots.
In order to create a meterialized view user should have 'create materialized view' privilege.
Materialized view is a space demanding object and it is a local copy of a remote database object.
In order to refresh the materialized view, we need to set the parameter 'job_queue_processes'.
Once we set this parameter thru a value, that many number of CJQn(CoOrdinated Job Queue Processes)gets enabled depending on the volume of data.
From 10g, the parameter is set by default to a value 10.
CJQ0,CJQ1,CJQ2......CJQ9
At the time of creating a materialized view we can specify the refresh interval as well as the refresh mode.
Refresh interval represents how frequently the materialized view has to be refreshed.
The different types of refresh modes are
1.complete
2.fast
3.force
4.none
Complete means oracle truncates the entire materialized view and refreshes the data. Complete takes more time if the volume of data is very huge.
Fast means oracle refreshes the materialized view based on primary key column.In order to create a materialized view with fast option, source object must n should have primary key column. In order to refresh with fast option , at the source side  we need to create materialized view log.
Force means, initially tries to refresh based on primary key, if primary key is not available it truncates the entire materialized view and refreshes.
None means no auto refresh.
We can also refresh the materialized view manually depending on the requirement by executing the following package :
SQL> exec dbms_mview.refresh('mview_name');
SQL> create materialized view dept_mview
refresh complete/fast
start with sysdate
next sysdate + (1/(24*60*60))
as select * from dept;
Sometimes we create materialized views to enable query rewrites within a database without depending on database links to improve the performance of SQL.

Comparision of different backup options

Oracle Networking

Oracle networking facilitates accessing of data or movement of data between the databases.
The 3 important files of oracle networking are
1.listener.ora
2.tnsnames.ora
3.sqlnet.ora
Default sample files are available in $ORACLE_HOME/network/admin/samples directory.
By default, oracle looks for this files in $ORACLE_HOME/network/admin directory.
The default location of init.ora is $ORACLE_HOME/dbs/init.ora. If we change the init.ora to another location like $ORACLE_HOME/oracle/init.ora, we need to startup the database like
SQL> startup pfile='/$ORACLE_HOME/oracle/init.ora'
We can also place the network related files in some other location but in this case we need to export the environment variable TNS_ADMIN.
oracle> export TNS_ADMIN=/home/oracle
listener.ora has to be configured at the serverside and tnsnames.ora has to be configured at the clientside by installing oracle client software.

$sh venuscript.sh --> runs in foreground
$sh venuscript.sh& --> runs in the background
$nohup venuscript.sh& --> runs in background & writes the log to nohup.dat

We can configure network related files either manually or using NETCA(Network Configuration Assistant).
We can restrict / as sysdba(Operating System Authentication) by configuring sqlnet.ora.
In sqlnet.ora we need to set a parameter sqlnet.authentication_services=none.
sqlnet.ora is mostly used in case of third party authentications.

Backup Strategy(example)

Enterprise Edition :
Everyday : RMAN incremental backup + archivelogs and delete archivelogs from archive destination.
3 days : Cumulative backup + archivelogs and delete archivelogs from archive destination.
Weekly : Complete backup + archivelogs and delete the archivelogs from archive destination.
Note : Any backup strategy that we design for the customers has to minimise the down time.

Standard Edition :
Everyday / for every 12 hours : Take the backup of archivelogs and delete the archives.
Note : If we have the licence of Standard Edition, we cannot take RMAN incremental backups.
3 days / weekly : Full backup + archivelogs and delete the archives.

Advantages of RMAN

1.RMAN backup is a block level backpup(oracle block). It will not generate excessive redo information.
2.Minimal DBA intervention during restore & recovery operations.
3.We can take incremental backup(takes the backup of only modified blocks).
4.We can take compressed backups.
5.We can enable block change tracking.
6.We can clone the production database to development or stage environments.
7.We can create a physical standby(data guard).
8.We can perform tablespace point-in-time recovery.
9.RMAN identifies currupted blocks & updates to v$backup_curruption.
10.To minimise the backup,restore and recovery time, we can configure multiple channels.
11.We can split out the backup of huge datafile into multiple sessions to minimise the backup time.
12.To segregate the job roles, in 11g oracle has introduced Virtual Private Catalog.

Using a single user account, we can view/modify the information existing in the recovery catalog which may lead to security concern. To overcome this problem and to have segregation of roles, oracle has introduced Virtual Private Catalog in 11g version of oracle.