Using a password file

Using a password file

Created by Vikash Singh

Description : It’s an alternative to connect to the remote database with sysdba/sysoper privileges as a non-sys user.

Location of password file : $ORACLE_HOME/dbs

Data Dictionary ( to know the user to whom sysdba or sysoper privileges is granted ) :
v$pwfile_users

How to create a password file 

Step 1 : Create the password file :

i. Go to the password binary location :

$ su – oracle1
$ cd $ORACLE_HOME/dbs

ii. Create the password file :

$ orapwd file=<any_name>_<ORACLE_SID>.ora password=<sys password> entries= <n>
where, file : user defined name
password : sys password

enteries : n is the maximum number of users you want to remotely access the database.

ex : $ orapwd file=vks_orcl.ora password=manager entries=3;

Step 2 : Edit the parameter file (assumed database is using the spfile )

sql> alter system set remote_login_passwordfile=exclusive scope=spfile;

Step 3 : Bounce the database :

SQL> startup force ;

Step 4 : Check if the password file is connected with the data dictionary or not:

i. connect to the database

$ sqlplus /nolog

SQL> conn /as sysdba

ii. Check the password file users name :

SQL > select username from v$pwfile_users;

USERNAME
------------------------------
SYS

- if this dictionary has sys a user it’s mean you have successfully create the password file which is connect to
the dictionary “v$pwfile_users” .

iii. create a user :

SQL> create user vikash identified by kumar;

iv. Grant sysdba to a newly created user :

SQL> grant sysdba to vikash ;
SQL > select username from v$pwfile_users;

USERNAME
------------------------------
SYS
VIKASH

- now user “vikash“ can log into the database remotely .
Rights assigned by oracle to the system users :
system privilege Authorized Operations
sysdba starts up and shut down database, alter database,create and drop database, toggle archivelog mode, recover
database.
sysoper starts up and shut down database, alter database, toggle archivelog mode, recover database.

Raw device Configuration for ASM

Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.
Oracle ASM uses disk groups to store data files; an Oracle ASM disk group is a collection of disks that Oracle ASM manages as a unit. Within a disk group, Oracle ASM exposes a file system interface for Oracle database files. The content of files that are stored in a disk group is evenly distributed to eliminate hot spots and to provide uniform performance across the disks. The performance is comparable to the performance of raw devices.

The benefits of ASM are:

• Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
• Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
• Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
• Uses redundancy features available in intelligent storage arrays
• The storage system can store all types of database files
• Using disk group makes configuration easier, as files are placed into disk groups
• ASM provides stripping and mirroring (fine and coarse gain - see below)
• ASM and non-ASM oracle files can coexist
• ASM is free!!!!!!!!!!!!!

Configure ASM on VMware:

Its just like the configuration on fake devices. On VMware you can
use vitual hard disk instead of fake dives. You can do this by adding extra
hard disk on you virtual machine.

For adding hard disk to VMware Machine go to:

Edit virtual machine setting
Click on ADD
Select hard disk and click next button
And follow the instruction.

Before powering on your virtual machine attach extra hardisks.
Start your machine

Check the raw disk:

don’t format the raw disk

Linux does not use raw device by default. Every Linux raw device you want to use must be bound to the corresponding block device using the block device.

Edit the file /ect/sysconfig/rawdevices as follows:

/dev/raw/raw1 /dev/sdc
/dev/raw/raw2 /dev/sdd
/dev/raw/raw3 /dev/sde
/dev/raw/raw4 /dev/sdf

Restart the rawdevice Service:

# service rawdevices restart
Assigning devices:
           /dev/raw/raw1  -->   /dev/sdc
/dev/raw/raw1:  bound to major 8, minor 32
           /dev/raw/raw2  -->   /dev/sdd
/dev/raw/raw2:  bound to major 8, minor 48
           /dev/raw/raw3  -->   /dev/sde
/dev/raw/raw3:  bound to major 8, minor 64
           /dev/raw/raw4  -->   /dev/sdf
/dev/raw/raw4:  bound to major 8, minor 80
done
The raw device binding will be created on each reboot.

Change the ownership of all rawdevices to the "Oracle" user:

# chown -R oracle:dba /dev/raw/raw*
# chmod -R 660 /dev/raw/raw*

To chnage the raw device ownership on every reboot:

go to /etc/rc.d/rc.local
# vi /etc/rc.local
and add
chown -R oracle:dba /dev/raw*
chmod -R 777 /dev/raw*

See also:
Manual Installation of Oracle Database on (ASM)

Datapump - I

This is a first post and i want to share some datapump steps.

CONN / AS SYSDBA

Create a user:-

ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

Create directory :-

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';

GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Drop directory

sql>drop directory '/oradata/datapump';

Create directory at OS level:-

$ mkdir datapump

 Table level export

$expdp system/manager dumpfile=emp.dmp directory=datapump tables=scott.emp,scott.dept;

 Table level import

$ impdp system/manager dumpfile=emp.dmp logfile=abcd.log directory=datapump tables=scott.emp;

* 'TABLE_EXISTS_ACTION=APPEND' parameter allows data to be imported into existing tables

Schema level Import/Export:-

$expdp system/manager directory=datapump schemas=scott dumpfile=scott.dump logfile=scott.log

$impdp system/manager directory=datapump schemas=scott dumpfile=scott.dmp logfile=scottimp.log

Database Import/export:-

$expdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G

$impdp system/password full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G

Estimate_only:-

  'estimate_only=y' Estimating How Much Disk Space Will Be Consumed durin export but not take actual export

<< DataPump II >>