All Checks
Database Version
SQL> desc v$version
SQL> select banner from v$version;
To check the EBS version
external hard disk mount
mount -t ntfs-3g /dev/sdb1 /prasad123
For unmount
umount -t ntfs-3g /dev/sdb1 /prasad123
create password file at dbs
orapwd file=orapwTESTC password=sys@123 ignorecase=y
Log as sysdba using created password file for test purpose
sqlplus sys/sys@123@TEST as sysdba
To check the ORACLE_HOME & ORACLE_SID
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
To chec SID in Applications
$ echo $TWO_TASK
To unlock a database user
CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
To create a directory
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
To get the applicatoin url page
select home_url from apps.icx_parameters;
select home_url from icx_parameters;
grep -i s_login $CONTEXT_FILE
Datapump related queries
select * from dba_datapump_jobs;
SYNTAX WE USE as a DBA
CREATING AN SPFILE:
create SPFILE=’</Source path>’ from PFILE=’</Destination path>’;
ALTERING THE DATABASE:
alter database<Database name> NOMOUNT;
alter database<Database name> MOUNT;
alter database<Database name> OPEN;
OPENING THE DATABASE IN READONLY MODE:
alter database<Database name> open READONLY;
OPENING DATABASE IN RESTRICTED MODE:
startup RESTRICT;
alter system enable RESTRICTED session;
MULTIPLEXING CONTROL FILES USING ‘SPFILE’:
alter system set controlfile=’</Source path>’ ‘</Destination path>’ scope=spfile;
shut down database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
startup database
MULTIPLEXING CONTROL FILES USING ‘PFILE’:
Shut the database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
Edit the pfile with new control file locations.
startup the database
FORCING A LOG SWITCH:
alter system switch logfile;
ADDING REDO LOG ‘GROUPS’:
alter database ADD logfile GROUP 1 (‘</Path of logfile to be placed>’ ‘</ another logfile to be placed>’) Size 1M;
ADDING ONLINE REDO LOG FILE ‘MEMBERS’:
alter database ADD logfile MEMBER ‘</Path of logfile>’ to GROUP 1, ‘</Path of logfile>’ to GROUP 2, ‘</Path of logfile>’ to GROUP 3;
DROPPING REDO LOG ‘GROUPS’:
alter database DROP logfile GROUP <group no.>;
DROPPING REDO LOG ‘MEMBERS’:
alter database DROP logfile MEMBER ‘</Path of log file>’;
RELOCATING (OR) RENAMING LOGFILES:
Copy redo log files to new location.
== > cp ‘</source path>’ ‘</Destination path>’
alter database clear logfile ‘</Path and new name>’; Drop old members;
CREATING TABLESPACE:
create tablespace <tablespace name> DATAFILE’</Path>’ size <no.> Autoextend on next<size.> maxsize <size>;
CREATING LOCALLY MANAGED TABLESPACES:
create tablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.> Extent management local uniform size <no.>;
CREATING UNDO TABLESPACES:
create undotablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.>;
CREATING TEMPORARY TABLESPACES:
create temporary tablespace <tablespace name> TEMPFILE ‘</Destination>’ size<no.> Extent management local uniform size<no.>;
SETTING DEFAULT TEMPORARY TABLESPACES:
alter database default temporary tablespace <tablespace name>;
SETTING TABLESPACE IN ‘READONLY’ MODE:
alter tablespace <tablespace name> READONLY;
TAKING TABLESPACE OFFLINE AND ONLINE:
alter tablespace <tablespace name> OFFLINE;
alter tablespace <tablespace name> ONLINE;
MANUALLY RESIZING DATAFILE:
alter database DATAFILE ‘</path>’ RESIZE <NO.>;
ADDING DATAFILE TO TABLESPACE:
alter tablespace <tablespace name> ADD DATAFILE ‘</path>’ size <no.>;
METHODS OF MOVING(RENAMING) DATAFILES:
Take tablespace offline.
use O.S command to copy datafile to different location.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
Bring tablespace online;
IF TABLESPACE CANNOT BE TAKEN OFFLINE FOR MOVING:
Shut down database.
use O.S command to copy datafile to different location.
Mount database.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
open database.
TABLESPACE DROPPING:
drop tablespace <tablespace name> including contents and datafiles;
AUTOMATIC SEGMENT SPACE MANAGEMENT:
create tablespace <tablespace name> DATAFILE ‘</path>’ size<no.> Extent management local uniform size <no.> Segment space management AUTO;
SWITCHING UNDO TABLESPACE:
alter system set UNDO_TABLESPACE=<undo name>;
DROPPING UNDO TABLESPACE:
drop tablespace <undo tablespace name>;
MANUALLY ALLOCATING EXTENTS:
alter table <tablespace name> Allocate extent (size<no.> DATAFILE ‘</path>’);
MOVING ONE TABLE TO OTHER TABLESPACE:
alter table <table name> Move tablespace<tablespace name>;
TRUNCATING TABLE:
truncate table <table name>;
DROPPING TABLE:
drop table <table name> cascade constraints;
DROPPING COLUMNS:
alter table<table name> drop column comments Cascade constraints checkpoint <1000>;
CREATE BITMAP INDEX:
create BITMAP index < tablename_column_name_idx > On <tablename>(column name) Pctfree 30 Storage(initial <no.> next <no.> Pct increase 0 maxextents <no.>) Tablespace <tablespace name>;
REBUILDING INDEX:
alter index <index name> REBUILD Tablespace<tablesapce name>;
REBUILDING INDEXES ONLINE:
alter index <index name> REBUILD ONLINE;
DROPPING INDEX
drop index <index name>;
Tablespace related queries:
To check temporary tablespace
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;
To resize temporary tablespace
alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;
To add a datafile to temporay tablespace
ALTER TABLESPACE temp1 ADD TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' SIZE 4096M;
Resize the datafile of the particular tablespace.
ALTER DATABASE DATAFILE '/d01/oracle/VIS/db/apps_st/data/data01.dbf' RESIZE 2048M;
Add a datafile to tablespace
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/d01/oracle/VIS/db/apps_st/data/data02.dbf' SIZE 1024M;
Set Default Temporary Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;
To resize temporary tablespace:
ALTER DATABASE TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' RESIZE 4096M;
Create a New User and Grant
Login to SYS user.
CREATE USER XXCUST IDENTIFIED BY ORACLE
DEFAULT TABLESPACE XXCUST_TABLESPACE TEMPORARY TABLESPACE TEMP;
GRANT RESOURCE, CONNECT TO XXCUST;
To view current users in Oracle database and their current status:
select username, account_status from dba_users;
Rename a table in Oracle
alter table <Current table name> rename to <New table name>;
Get the objects consumed by SYSAUX tablespace
SELECT * FROM V$SYSAUX_OCCUPANTS
How to check the last resetlogs
SQL> select name,to_char(RESETLOGS_TIME,'DD-MON-YYYY HH:MI::SS') from v$database;
How to find a table size on Oracle Database
SELECT segment_name, tablespace_name, ROUND((BYTEs/1073741824*100)/100) sizeGB, ROUND((BYTEs/1048576*100)/100) sizeMB, ROUND((BYTES/1024*100)/100) sizeKB FROM USER_SEGMENTS order by sizeKB desc;
To check the hidden parameters in a database
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
To check SGA and PGA sizes
SELECT * FROM V_$SGA_TARGET_ADVICE;
SELECT * FROM V_$PGA_TARGET_ADVICE;
or
select * from v$memory_target_advice;
Database Version
SQL> desc v$version
SQL> select banner from v$version;
To check the EBS version
external hard disk mount
mount -t ntfs-3g /dev/sdb1 /prasad123
For unmount
umount -t ntfs-3g /dev/sdb1 /prasad123
create password file at dbs
orapwd file=orapwTESTC password=sys@123 ignorecase=y
Log as sysdba using created password file for test purpose
sqlplus sys/sys@123@TEST as sysdba
To check the ORACLE_HOME & ORACLE_SID
$ echo $ORACLE_HOME
$ echo $ORACLE_SID
To chec SID in Applications
$ echo $TWO_TASK
To unlock a database user
CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
To create a directory
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
To get the applicatoin url page
select home_url from apps.icx_parameters;
select home_url from icx_parameters;
grep -i s_login $CONTEXT_FILE
Datapump related queries
select * from dba_datapump_jobs;
SYNTAX WE USE as a DBA
CREATING AN SPFILE:
create SPFILE=’</Source path>’ from PFILE=’</Destination path>’;
ALTERING THE DATABASE:
alter database<Database name> NOMOUNT;
alter database<Database name> MOUNT;
alter database<Database name> OPEN;
OPENING THE DATABASE IN READONLY MODE:
alter database<Database name> open READONLY;
OPENING DATABASE IN RESTRICTED MODE:
startup RESTRICT;
alter system enable RESTRICTED session;
MULTIPLEXING CONTROL FILES USING ‘SPFILE’:
alter system set controlfile=’</Source path>’ ‘</Destination path>’ scope=spfile;
shut down database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
startup database
MULTIPLEXING CONTROL FILES USING ‘PFILE’:
Shut the database;
copy controlfiles using O.S command.
==>cp ‘</source path>’ ‘</destination path>’
Edit the pfile with new control file locations.
startup the database
FORCING A LOG SWITCH:
alter system switch logfile;
ADDING REDO LOG ‘GROUPS’:
alter database ADD logfile GROUP 1 (‘</Path of logfile to be placed>’ ‘</ another logfile to be placed>’) Size 1M;
ADDING ONLINE REDO LOG FILE ‘MEMBERS’:
alter database ADD logfile MEMBER ‘</Path of logfile>’ to GROUP 1, ‘</Path of logfile>’ to GROUP 2, ‘</Path of logfile>’ to GROUP 3;
DROPPING REDO LOG ‘GROUPS’:
alter database DROP logfile GROUP <group no.>;
DROPPING REDO LOG ‘MEMBERS’:
alter database DROP logfile MEMBER ‘</Path of log file>’;
RELOCATING (OR) RENAMING LOGFILES:
Copy redo log files to new location.
== > cp ‘</source path>’ ‘</Destination path>’
alter database clear logfile ‘</Path and new name>’; Drop old members;
CREATING TABLESPACE:
create tablespace <tablespace name> DATAFILE’</Path>’ size <no.> Autoextend on next<size.> maxsize <size>;
CREATING LOCALLY MANAGED TABLESPACES:
create tablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.> Extent management local uniform size <no.>;
CREATING UNDO TABLESPACES:
create undotablespace <tablespace name> DATAFILE ‘</Destination of datafile>’ size<no.>;
CREATING TEMPORARY TABLESPACES:
create temporary tablespace <tablespace name> TEMPFILE ‘</Destination>’ size<no.> Extent management local uniform size<no.>;
SETTING DEFAULT TEMPORARY TABLESPACES:
alter database default temporary tablespace <tablespace name>;
SETTING TABLESPACE IN ‘READONLY’ MODE:
alter tablespace <tablespace name> READONLY;
TAKING TABLESPACE OFFLINE AND ONLINE:
alter tablespace <tablespace name> OFFLINE;
alter tablespace <tablespace name> ONLINE;
MANUALLY RESIZING DATAFILE:
alter database DATAFILE ‘</path>’ RESIZE <NO.>;
ADDING DATAFILE TO TABLESPACE:
alter tablespace <tablespace name> ADD DATAFILE ‘</path>’ size <no.>;
METHODS OF MOVING(RENAMING) DATAFILES:
Take tablespace offline.
use O.S command to copy datafile to different location.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
Bring tablespace online;
IF TABLESPACE CANNOT BE TAKEN OFFLINE FOR MOVING:
Shut down database.
use O.S command to copy datafile to different location.
Mount database.
alter database ‘</path>’ RENAME Datafile ‘</Source path>’ to ‘</Destination path>’;
open database.
TABLESPACE DROPPING:
drop tablespace <tablespace name> including contents and datafiles;
AUTOMATIC SEGMENT SPACE MANAGEMENT:
create tablespace <tablespace name> DATAFILE ‘</path>’ size<no.> Extent management local uniform size <no.> Segment space management AUTO;
SWITCHING UNDO TABLESPACE:
alter system set UNDO_TABLESPACE=<undo name>;
DROPPING UNDO TABLESPACE:
drop tablespace <undo tablespace name>;
MANUALLY ALLOCATING EXTENTS:
alter table <tablespace name> Allocate extent (size<no.> DATAFILE ‘</path>’);
MOVING ONE TABLE TO OTHER TABLESPACE:
alter table <table name> Move tablespace<tablespace name>;
TRUNCATING TABLE:
truncate table <table name>;
DROPPING TABLE:
drop table <table name> cascade constraints;
DROPPING COLUMNS:
alter table<table name> drop column comments Cascade constraints checkpoint <1000>;
CREATE BITMAP INDEX:
create BITMAP index < tablename_column_name_idx > On <tablename>(column name) Pctfree 30 Storage(initial <no.> next <no.> Pct increase 0 maxextents <no.>) Tablespace <tablespace name>;
REBUILDING INDEX:
alter index <index name> REBUILD Tablespace<tablesapce name>;
REBUILDING INDEXES ONLINE:
alter index <index name> REBUILD ONLINE;
DROPPING INDEX
drop index <index name>;
Tablespace related queries:
To check temporary tablespace
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;
To resize temporary tablespace
alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;
To add a datafile to temporay tablespace
ALTER TABLESPACE temp1 ADD TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' SIZE 4096M;
Resize the datafile of the particular tablespace.
ALTER DATABASE DATAFILE '/d01/oracle/VIS/db/apps_st/data/data01.dbf' RESIZE 2048M;
Add a datafile to tablespace
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/d01/oracle/VIS/db/apps_st/data/data02.dbf' SIZE 1024M;
Set Default Temporary Tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;
To resize temporary tablespace:
ALTER DATABASE TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' RESIZE 4096M;
Create a New User and Grant
Login to SYS user.
CREATE USER XXCUST IDENTIFIED BY ORACLE
DEFAULT TABLESPACE XXCUST_TABLESPACE TEMPORARY TABLESPACE TEMP;
GRANT RESOURCE, CONNECT TO XXCUST;
To view current users in Oracle database and their current status:
select username, account_status from dba_users;
Rename a table in Oracle
alter table <Current table name> rename to <New table name>;
Get the objects consumed by SYSAUX tablespace
SELECT * FROM V$SYSAUX_OCCUPANTS
How to check the last resetlogs
SQL> select name,to_char(RESETLOGS_TIME,'DD-MON-YYYY HH:MI::SS') from v$database;
How to find a table size on Oracle Database
SELECT segment_name, tablespace_name, ROUND((BYTEs/1073741824*100)/100) sizeGB, ROUND((BYTEs/1048576*100)/100) sizeMB, ROUND((BYTES/1024*100)/100) sizeKB FROM USER_SEGMENTS order by sizeKB desc;
To check the hidden parameters in a database
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
To check SGA and PGA sizes
SELECT * FROM V_$SGA_TARGET_ADVICE;
SELECT * FROM V_$PGA_TARGET_ADVICE;
or
select * from v$memory_target_advice;
No comments:
Post a Comment