Create automatic user

CREATE USER &USERNAME IDENTIFIED BY &PASSWORD
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
PROFILE DEFAULT
ACCOUNT UNLOCK;

Extract a schema

set pages 500
set lines 200
set long 100000
select dbms_metadata.get_ddl( 'USER', 'AIRTEL' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'AIRTEL' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'AIRTEL' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'AIRTEL' ) from dual;

maximum of 148 enabled roles exceeded

Try importing using another user.

nslookup

this command is used to get the server name if we know the ip address.

$ nslookup ###.##.##.##

drop a database

This is in 11G
------------------------------

shutdown abort;
startup mount exclusive restrict;
drop database;
exit

Database export/import using datapump (schema and tablespace names are same)

Check whether

listener file location

By default, the listener file will be located at $ORACLE_HOME/network/admin. If it is not there, then issue the command, lsnrctl status. This will give you the exact location of the listener file.

pfile/spfile contents

Move the existing init file present in $ORACLE_HOME/dbs folder to *.old.

SQL> show parameter spfile;

If it shows the output, then do a "create pfile from spfile" and the pfile will be created from the spfile in the location $ORACLE_HOME/dbs folder. Extract the content of the pfile.

If the output shows pfile, then directly go to $ORACLE_HOME/dbs folder and get the content of the file.

Create a database

Pre-requisite : Make sure Exceed is running in the background.

export ORACLE_SID=
export ORACLE_HOME=
export DISPLAY=ip address(get it from the command prompt using the command ipconfig):0.0
Check whether xterm is working or not..
In AIX, go to location /usr/bin/X11 and type xterm.. you should be able to see a small window pop up
In Linux, /usr/X11R6/bin.
In Sun Solaris, /usr/openwin/bin.
In any location, type dbca





Click "next"





Click "Create a database"






Click "General Purpose or Transaction Processing" and click "next"






Enter the global database name and SID and click "next"







Click "next"


Enter the passwords and click "next"

Select storage type as "ASM" or "Filesystem"

Select "use Oracle Managed Files" and select the available disk group.

click "next"

It will ask for the ASMSNMP password specific to ASM; give the password.
click "ok"


Choose the recovery options for the database: skip and click "next"


click on "sample schemas" and click "next"

Initialization parameters:just click on "typical"
tick "automatic memory management"

click "next"

Database storage: skip and click "next"

Select the database creation options
Select "Create a database"

Click "finish"

It will ask for confirmation. Click "ok"


You can see the progress bar simultaneously while the database is getting created.

You can skip "Password Management" and click "exit"


now you will be able to see the database added. do a ps -ef | grep pmon


I guess by default , the database size will be 2 GB.

If you want to increase the database size, then increase the size of the datafiles or make the datafiles autoextend on.

schema export/import using datapump (schema names and tablespace names are same)

check the schema size

check whether you have enough space in the filesystem in which you are going to perform the export

export parameters
------------------------
schemas, directory, logfile, dumpfile

transfer the dump file using scp from source to the destination database
make sure the files have the 700 privilege

drop and create the schema in the destination database

make sure whether you have sufficient space in the database and in the filesystem

import parameters
-------------------------
schemas, directory, logfile, dumpfile

Disk I/O

In AWR, check for the columns Tablespace I/O stats and File I/O stats.

In Tablespace I/O, see the avg(ms). If it is > 20, we may have to take some initiative.

In File I/O stats, if the Reads column shows more value, then we can try to change the mount point.

create a new service

exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'*******_APP', NETWORK_NAME=>'********_APP')
exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'*********_DBA', NETWORK_NAME=>'********_DBA')
exec dbms_service.START_SERVICE('**********_APP')
exec dbms_service.START_SERVICE('**********_DBA')