Create database link Oracle DBA

 

Create database link

Dba12 rajpal> create database link link1 connect

To Wilshire identified by Wilshire

Using tnsnames_dba12’;

Dba12 rajpal>    select * from tab@link1;

… the tables of Wilshire

Dba12 rajpal> select * from user_db_links;

Like1

Dba12 rajpal> select * from emp@link1;

Dba12 rajpal> insert into emp@link1 select * from emp@link1;

Dba12 rajpal> comit;                                       {only DML Txs not DDL Txs

Dba12 SYS > grant creat synonym to rajpal;

Dba12 rajpal> create synonym el1 for emp@link1

Dba12 raj> select * from el1;

Public database links:                                     ram{dba12’s another user

Dba12 SYS > grant create public database link to rajpal;

Dba12 rajpal> create public database link plink1 connect to Wilshire identified by Wilshire using tnsnames_dba12;

Dba12 rajpal> select * from user_db_links;

Link1

Plink1

Dba12 ram> select * from user_db_links;

No rows selected

Dba12 ram> select*from all_db_links;

Plink1

Dba12 ram> select * from emp@plink1;

….records of wilshire’s emp table.

On public links [only dml.drl, tcl Txs. But not DDL txs]

Dba12 SYS > select * from dba_db_links;

Dba12 SYS> select * from all_db_linnks;

SYS/rajpal> drop database link link1;

>drop public database link plink1; {only SYS [or] rajpal- [creator of the link]

SNAP HOTS (materialized views)

{cjq} co – ordinate job queue, the background process is responsible to reflect the newly inserted records on the master table

Dba12 SYS> grant create snapshot to rajpal;

Dba12 SYS> !

Dba12 ~ ] $ cd $ ORACLE _ HOME /dbs

Dba12 dbs] $ vi initrajpar.ora

Job_queue_processes+3

[at a time 3 processes]

:wq

Dba12 dbs]$exit

Dba12 SYS > startup force

Dba12 SYS> conn rajpal/rajpal

Dba12 rajpal> select * from session_prives;

Dba12 rajpal> create materialized view mv1 refresh complete with rowed start with sysdate next

sypdate   +1 /(24*60*60) as select * from emp@link1;

materialized view created

dba12 rajpal>     save mv1.sql

dba12 rajpal>     select * from tab;

mv1

dba12 rajpal> select * from mv1;

materialized view with refresh fast option

it should contain or we should assign primory key in master table

log table should be created.

Dba15 wilshire> alter table emp add primary key (empno);

Dba15 wilshire> create materialized view log on emp:

Dba15 wilshire> select * from tab;

MLOG$ _emp                    These table contains only those records which are

UPA$ _ EMP                       Modified snapshots will receive data from these tables.

DBA12 rajpal>    create masterialized view mvf 1 refresh fast with row id start with SYSdate next sys date  +1/24*60*60) as select * from emp@link1;

Dba15 wilshire> insert into emp select * from emp;

Dba15 wilshire> select * from molg$_emp;

Dba15 wilshire> commit;  

 

Views regarding {SNAP SHOTS (MV)

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

USER_MVIEWS DBA_MVIEWS

For manual refresh  - package-

     

To know the SCN # for any moment

Dbms_flashback_get_system_change_number [ a package]

Controlfile related views

V $ controlfile

V$controlfile_record_section

SYS > select type from v $ controlfile_record_section;

SYS > select name from v$controlfile;

SYS> desc v$ database_incarnation;

SYS > select incarnation#, resetlogs_id from

V$database_incarnation;  

Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore Oracle DBA Training

Multiplexing the control files

Make a copy of the control file in the new location physically.

SYS > shut immediate

Dba12 ~] $ cd / disk 3 / oradata/pal

Dba12 pal] $ cp cont. ctl / disk4/oradata/pal/cont2.ctl

Dba 12 pal ] $ cd $ ORACLE _ HOME / dbs

Dba12 dbs] $ vi initrajpar.ora

Control _ files = (i/disk3/oradata/pal/cont.ct;, ‘/disk4 / oradata/pal/cont2. Ctl’)

:wq

Dba12 dbs ] $ exit

SYS > startup

SYS> select name from v$controlfile;

Name

/disk3 /oradata/pal/cont.ctl

/disk4/oradata/pal/cont2.ctl  

Renaming the database

Dba12  `]$ sqlplus  ‘/as stsdba’

SYS > startup

SYS> alter database badkup controlfile to trace;

SYS>!

Dba12  ~] $ cd /disk4 /oradata/pal/udump

Dba12 udump ] $ ls – lrt                                 [longlisting reversely with time]

Dba12 udump] $ cp rajpar_ora_20457.trc    ~/control.trc

Dba12 udump]$ cd

Dba12 ~] $ vi control.trc

[will be in ASCII format]

In esc mode

Dgg                        removing upto beginning of the page  

Startup nomount  

Character setr US7ASCII ;  

D G  removing up to end of the page

:wq

Dba12 ~ ] $ exit

SYS > shut immediate

SYS>!

Dba12 ~]$ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar. Ora

Db_name= rajpal1

[max 8 chars}

:wq

Dba12 dbs]$ cd

Dba12 ~ ] $ vi control.trc

Startup nomount change to db-name                    [No] should be removed

Create controlfile set database “rajpal2”               RESETLOGS noarchivelogs

:wq

Dba12 ~ ] $ cd / disk3/oradata/pa;

Dba12 pal]$ cp cont.ctl                                   ~

Dba12 pal ] $ rm cont. ctl

Dba 12 pal}$ exit

SYS > @ control.trc

SYS> alter database open resetlogs;

SYS> select incarnation #, resetlogs-id from

V$database_incarnation;

SYS> select name from v$database;

Name

Rajpal2  

For an in-depth knowledge, click on below

• LMTS Notes in oracle dba
• Log files in Oracle DBA