NETWORKING
SERVER SIDE à
CLIENTSIDE à
LISTENER.ORA
TNSNAMES.ORA
- Listener name 1.Alias name
- Protocol 2.Protocal
- Port number 3.Port number
- SID names (Same as server Port)
4. SID names
(Same as server SID)
- In .bash_profile TNS_ADMIN=$HOME
- LISTENER.ORA & TNSNAMES.ORA LOCATION…….
$ORACLE_HOME/network/admin
- Port range 1024 to 65536
LISTENER.ORA
LISTENER {Original listener File}
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = IPC)
(KEY =
ORCL))
(ADDRESS = (PROTOCAL = TCP)
(HOST =) (PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME =
ORCL)
(ORACLE_HOME =
/oraeng/app/oracle/product/10.2.0)
)
)
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
RED
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = IPC)
(KEY =
ORCL))
(ADDRESS = (PROTOCAL = TCP/IP)
(HOST = 10.0.0.5) (PORT = 6666))
)
)
)
{After Modification}
SID_LIST_RED =
(SID_LIST =
(SID_DESC =
(SID_NAME =
RAM)
(ORACLE_HOME =
/oraeng/app/oracle/product/10.2.0)
)
)
: wq
Save the file and
start the LISTENER …..
$ lsnrctl start <listener
name>
E.g.:
$ lsnrctl start RED
@@@ ###
&&&&&&&&&&&&&&&&&
&&&&&
&&&&&&
&&&&&&&
TNSNAMES.ORA
ALIAS =
(DESCRIPTION = {Original Tns
File}
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP) (HOST = ) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =
ORCL)
)
)
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
GREEN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCAL = TCP/IP) (HOST = 10.0.0.5)
(PORT = 6666))
)
(CONNECT_DATA = {After Modification}
(SERVICE_NAME = RAM)
)
)
:wq
Ping the tnsnames.ora file
$ tnsping <Alias name>
E.g.:
$ tnsping GREEN
Then u can connect to server ……..
$ sqlplus
username/password@aliasname
Username: server side username
Password: that user password
Alias name: tns name……. (GREEN)
SERVER
SIDE;
My Listener name is RED
SID: ram
{here database name is PRASAD}
Port: 6666
Protocol: TCP/IP
CLIENT
SIDE:
My Tnsname (alias name) is GREEN
SID:
ram (Same as server
side)
Port:
6666 (Same as server
side) {Here database name is REDDY}
Protocol: TCP/IP (Same as server side)
$
lsnrctl start RED
$
tnsping GREEN
$
sqlplus kk/kk@GREEN {KK is server side user}
KK> select * from tab;
KK>
Like this we can connect to only
USER (KK)
But if u want connect to SYS
(server side) then u should know the
PASSWORD FILE of server …………
·
CREATE A
PASSWORD FILE
In pfile
Remote_login_passwordfile=exclusive
dbs] $
orapwd file=orapw$ORACLE_SID password=www force=y
SYS>
startup force
SYS>
select * from v$pwfile_users;
$
sqlplus sys/www@GREEN as sysdba
SYS>
select name from v$instance;
o/p
ram (This is
server side instance name)
SYS>
select name from v$database;
o/p
PRASAD (this is server side
DB NAME)
Sys>>
select * from all_users;
Sys>>
drop user bb cascade;
Sys>>
grant connect, resource to bb identified by bb;
DATABASE LINKS
SERVER
SIDE;
My Listener name is RED
SID: ram
{Here database name is PRASAD}
Port: 6666
Protocol: TCP/IP
CLIENT
SIDE:
My Tnsname (alias name) is GREEN
SID: ram (Same as server side)
Port: 6666 (Same as server side) {Here database name is REDDY}
Protocol: TCP/IP (Same as server side)
$
lsnrctl start RED
$
tnsping GREEN
v
PRIVATE
DATABASE LINKS
Sys>
select username from dba_users;
o/p
kk
pr
Sys>
Sys>
select username from dba_users;
o/p
U1
U2
U3
U4
Sys>
grant create database link to u1;
Sys>
Sho parameter global_names
TRUE
(default)
(CLIENT SIDE)
Make it FALSE …….. in parameter file….
global_names=FALSE
If TRUE: The database link name should be PRASAD (this
is db name of server side)
U can create with different
name also but U can’t access the data of
server (Prasad)
If FALSE: You can create
with any name …. & you can access the data also……..
U1> create database link lnk1 connect to
kk identified by kk using ‘GREEN’;
U1> Desc
USER_DB_LINKS;
U1> select db_link,username,password from
user_db_links;
kk> select * from tab;
o/p
emp
dept
kk>select count (*) from emp;
14 rows’s selected
U1> select * from tab@lnk1;
o/p
emp
dept
U1>
select count (*) from emp@lnk1;
14 row’s selected
U2> select * from tab@lnk1;
ERROR U2 can’t access the data (It’s private DB link)
U1> Insert into emp@lnk1 select * from
emp@lnk1;
Commit;
U1> select count (*) from emp@lnk1;
28 row’s selected
KK>
select count (*) from emp;
28 row’s selected
v
PUBLIC DATABASE LINKS
Sys>
grant create public database link to U2;
Sys>
U2>
create public database link lnk2 connect
to pr identified by pr
Using ‘GREEN’;
U2> Desc USER_DB_LINKS;
U2> select db_link,username,password from
user_db_links;
View
: DBA_DB_LINKS
ALL_DB_LINKS
Sys>
Pr> select * from tab;
o/p
abc
xyz
Pr>select count(*) from abc;
100 row’s selected
U2>
select count(*) from tab@lnk2;
o/p
abc
xyz
U2>
select count(*) from abc@lnk2;
100 row’s selected
à
From client side using lnk2 (public
database link) any user can access
Coz
lnk2 is public database link…
U3> select count(*) from abc@lnk2;
100 row’s selected
U4> select count(*) from abc@lnk2;
100
row’s selected
Ø To
drop a Public Database link:
> drop public database link <link
name>;
MATERIALIZED VIEWS
Step 1) Configure
Network links
Step 2) Configure(Create) database links
Step 3) Create
Materilazed Views
$ lsnrctl start
RED
$ tnsping
GREEN
SYS>
kk>
select count (*) from emp;
14 row’s selected
SYS>
U1>
create db link lnk1……..
U1>
select * from emp@lnk1;
14 row’s selected
@@@
Materialized Views can we create
in 3 ways…….
Ø
REFRESH
Complete
Ø
REFRESH
Fast
Ø
REFRESH
Force
COMPLETE: Entire data is
Regenerated every time the MATERIALIZED VIEW
Refreshed.
FAST: Only the row’s that are modified are
generated every time the
MATERIALIZED VIEW is Refreshed
using the MATERIALIZED VIEW log.
Changed information is stored in
the MATERIALIZED VIEW log.
MATERIALIZED VIEW log is a table
in the master database that is associated
With the master table. Oracle uses
a MATERIALIZED VIEW log to track
the row’s that have been updated
on the master table……
FORCE: Complete + Fast
Sys>grant
create materialized view to U1;
Sys>
U1>
select * from tab;
Emp
Dept
COMPLETE:
U1>create materialized view mvn refresh complete with rowid
Start with sysdate
next sysdate +1/ (24*60*60)
As select * from
emp@lnk1;
{For
every second}
Check in the client side User(u1) tables, { One more object(mvn)
will create…)
This mvn is the READ
ONLY TABLE (ROT), we can select
only….
U1> select * from tab;
Emp
Dept
Mvn
U1>
select * from mvn;
14 row’s selected
KK>
insert into emp select * from emp;
KK>
commit;
KK>
select count (*) from emp;
28 row’s selected
U1>
select count (*) from mvn;
28 row’s selected
REFRESH FAST:
KK>
select * from tab;
Emp
Dept
KK>
select count (*) from dept;
4 row’s selected
KK>
alter table dept add primary key (deptno);
KK>
create materialized view log on dept;
KK>
select * from tab;
Emp
Dept
mlog$_dept
In
Parameter file mention
(CJQ Background process) From 10g
(COORDINATOR JOB QUEUE PROCESS)
Max processes: 1000
Job_queue_processes=5
U1>
create materialized view mvf refresh fast with primary key start with
sysdate Next sysdate +1/ (24*60*60)
As select * from dept@lnk1;
U1>
select * from tab;
U1
> select count (*) from mvf;
4 row’s selected
KK>
select count (*) from dept;
4 row’s selected
KK>
insert into dept values (66,’DBA’,’HYD’);
1 row created (Not
committed)
KK>select
* from tab;
KK>select
* from mlog$_dept;
o/p
KK>
commit;
KK>
select * from mlog$_dept; (Observe the
Output’s)
o/p
KK> /
(Some times…… then observe)
U1>
select count (*) from mvf;
5 row’s selected
NOTE: The MATERIALIZED VIEWS can be manually
refreshed using
DBMS package…………
Syntax:
>>
EXECUTE DBMS_MVIEW.RFRESH
(‘materialized viewname’,’refresh_option’)
U1> Exec dbms_mview.refresh (‘MVN’,’COMPLETE’);
(M.view name)
(Option)
VIEWS:
Ø
DBA_MVIEWS
Ø
DBA_MVIEW_COMMENTS
Ø
DBA_MVIEW_DETAILS_RELATIONS
Ø
DBA_MVIEW_LOGS
Ø
DBA_MVIEW_REFRESH_TIMES
Ø
DBA_MVIEW_LOG_FILTER_COLS
DROP Materialized View:
DROP MATERIALIZED VIEW < MV name>;
DROP Materialized View Log:
DROP MATERIALIZED VIEW LOG ON customers;
·
List
all materialized view logs
select log_owner
, log_table
from dba_mview_logs
/
·
Show
all materialized and resfresh times
set lines 100 pages 999
col last_refresh format a20
select owner
, mview_name
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/
·
Show
materialized view tables and masters
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
·
Show
refresh jobs in dba_jobs
This is useful for spotting failures
set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/
o
Fast = update changes
only
o
Complete = wipe and
repopulate the mview
o Force = fast if possible,
complete if not.
No comments:
Post a Comment