Steps for creating the database links

1) Connect as testuser/**** user into database abc


SQL>conn testuser/****@abc

2) Create database link with the same name as global_name of xyz database .

SQL> create database link xyz.testdomain.com connect to testdblink identified by **** using ‘xyz’;

3) Verify the database link works

SQL > select * from global_names@xyz.testdomain.com

How to create a read only user in database with specific schema access privileges .

Here are the steps :-


Step 1 :- connect system@xyz.testworld.com

Step 2 :- CREATE USER vinay_R IDENTIFIED by vinayr123 default tablespace users quota 0 on system quota unlimited on users;

Step 3 :- grant connect, create session to vinay_r;

Step 4 :- create role RL_vinay_R;

Step 5 :- grant RL_vinay_R to vinay_R;

Step 6 :-

select 'GRANT '||
DECODE(OBJECT_TYPE,'PROCEDURE','EXECUTE',
'PACKAGE','EXECUTE',
'SEQUENCE','SELECT',
'SELECT, INSERT, UPDATE, DELETE')||
' ON '||OWNER||'.'||OBJECT_NAME||' TO '||
DECODE(object_type,'VIEW',' vinay_R WITH GRANT OPTION ',' RL_vinay_R ')||';'
from dba_objects
where owner IN ('Schema1','Schema2')
and object_name not like '%$%'
--and object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
and object_type = 'VIEW'
order by owner, OBJECT_TYPE, object_name

Step 7:-

select 'CREATE SYNONYM vinay_R.'||object_name||' for '||OWNER||'.'||object_name||'; '
from dba_objects
where owner IN ('Schema1','Schema2')
and Object_Type IN ('FUNCTION', 'PROCEDURE', 'VIEW', 'TABLE', 'SEQUENCE', 'PACKAGE')

nobody user in Oracle 10g

Installation Guide for Oracle 10g mentions the need to create a user "nobody"

An unprivileged user
Verify that the unprivileged user nobody exists on the system. The nobody user
must own the external jobs (extjob) executable after the installation.

Metalink Note 357575.1 further describes what exactly is a unprivileged user:

Unprivileged user:
As the purpose of the user is essentially to allow database users to execute external jobs, you should not use the oracle user. The oracle user is a powerful user; obviously they can shut down the database, and have access to a number of resources, oracle and otherwise. You would not want to give any database user with privileges to schedule this job (actually create external job privs.) the ability to perform all oracle actions; at least not in a normal environment. For security purposes, it is best to create a user with minimum privileges required to do the job. For example, if it reads from a specific directory, give them access to that directory, but not to the oracle binaries directory.

The need for the low privilege user is simply a security measure. The term unprivileged user, in many contexts, is used to refer to a non-DBA user; it's a general term for a non super user.



How To create an Unprivileged User?
You must create a new unprivileged local user to own the extjob executable. You can use any user name for this user, but this document uses the name extjob. To create the extjob user, enter the following command:

# /usr/sbin/useradd extjob

Verifying that the User nobody Exists
Before installing the software, perform the following procedure to verify that the nobody user exists on the system:

To determine whether the user exists, enter the following command:


# id nobody


If this command displays information about the nobody user, then you do not have to create that user.
If the nobody user does not exist, then enter the following command to create it:

# /usr/sbin/useradd nobody

Script to Switch logfile

spool logswitch-trwprod1.log
conn sys/trw@prod1 as sysdba
alter system switch logfile;
exit

Database Cold Backup

We are taking the cold backup of the database.

Connect to the database.

Ex: $sqlplus ‘/as sysdba’

Find the Path of the data files, control file and redo log files.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/narniaprodb/orainfprod/oradata/narniap/pnarnia/system01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/undotbs01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/sysaux01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/users01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/dcm.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/portal.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/ptldoc.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/ptlidx.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/ptllog.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/oca.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/discopltc1.dbf

NAME
--------------------------------------------------------------------------------
/narniaprodb/orainfprod/oradata/narniap/pnarnia/discopltm1.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/oss_sys01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/wcrsys01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/uddisys01.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/b2b_dt.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/b2b_rt.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/b2b_idx.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/b2b_lob.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/bam.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/orabpel.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/attrs1_oid.dbf

NAME
--------------------------------------------------------------------------------
/narniaprodb/orainfprod/oradata/narniap/pnarnia/battrs1_oid.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/gcats1_oid.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/gdefault1_oid.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/svrmg1_oid.dbf
/narniaprodb/orainfprod/oradata/narniap/pnarnia/ias_meta01.dbf

27 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/narniaprodb/orainfprod/oradata/narniap/pnarnia/control01.ctl
/narniaprodb/orainfprod/oradata/narniap/pnarnia/control02.ctl
/narniaprodb/orainfprod/oradata/narniap/pnarnia/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/narniaprodb/orainfprod/oradata/narniap/pnarnia/redo03.log
/narniaprodb/orainfprod/oradata/narniap/pnarnia/redo02.log
/narniaprodb/orainfprod/oradata/narniap/pnarnia/redo01.log

Before shutting down the database all the services should be down (Both midtier and infrastructure)

SQL>shutdown immediate;

Goto “/narniaprodb/portalbkup” folder and create a diretory with today’s date and copy all the datafiles,controlfile and the redolog files to backup folder

$cp /narniaprodb/orainfprod/oradata/narniap/pnarnia/*

Start the database

SQL>startup