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 ohould 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;
Multiplexing the control files
Make the copy of the control file in 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
0 Responses on Create database link Oracle DBA"