ALL CHECKS

ALL CHECKS

# Database Version

SQL> desc v$version

SQL> select banner from v$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



# $cd /u01/apps/oracle/oradata/  
$ tar czf /home/oracle/ora10g.tar.gz ora10g
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1
$ cd /u01/apps/oracle/product/10.2.0/
$ tar czf /home/oracle/oraHomeBackup.tar.gz db_1
$ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1

$ cd /u01/apps/oracle/product/10.2.0/
$ rm -fr db_1
$ tar xzf /home/oracle/oraHomeBackup.tar.gz *
$ cd /u01/apps/oracle/oradata/
$ rm -fr ora10g
$ tar xzf /home/oracle/ora10g.tar.gz *



# Change password with fndcpass

FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN sysadmin


# $ echo $ORACLE_HOME
/u01/apps/oracle/product/10.2.0/db_1


#  gzip file.txt
For Expand
gunzip file.txt.gz

#  CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;


# expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log


# expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

#  expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log 


#  SELECT current_scn FROM v$database;
SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual


# SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM dual;
SELECT SCN_TO_TIMESTAMP(5474751) FROM dual;



#  adpatch options=nocompiledb


#  select home_url from apps.icx_parameters; 
SQL> select home_url from icx_parameters;


#  how to create soft links
ln -s /webroot/home/httpd/test.com/index.php /home/vivek/index.php
ls -l


#  select * from dba_datapump_jobs; 


# how to check temporary tablespace

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;


SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) 
FROM V$temp_space_header
GROUP BY tablespace_name;

# alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;

#  alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp01.dbf' resize 100M;


alter database tempfile '/R12/oracle/SHIV/db/apps_st/data/temp08.dbf' resize 1G;


# ALTER TABLESPACE lmtbsb
   ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;



# CREATE TABLESPACE TEMP3 DATAFILE '/R12/oracle/SHIV/db/apps_st/data/temp03.dbf' SIZE 250M;





# ALTER TABLESPACE tbs_03 
    ADD DATAFILE 'tbs_f04.dbf'
    SIZE 100K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;


# ALTER TABLESPACE tbs_03
    DROP DATAFILE 'tbs_f04.dbf';


# ALTER TABLESPACE undots1
  RETENTION NOGUARANTEE;

# ALTER TABLESPACE undots1
  RETENTION GUARANTEE;



# Temporary Datafiles

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:

    Tempfiles are always set to NOLOGGING mode.

    You cannot make a tempfile read-only.

    You cannot rename a tempfile.

    You cannot create a tempfile with the ALTER DATABASE command.

    Media recovery does not recognize tempfiles.

        BACKUP CONTROLFILE does not generate any information for tempfiles.

        CREATE CONTROLFILE cannot specify any information about tempfiles.

    Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE, but not in DBA_DATA_FILES or V$DATAFILE.




CREATE temporary TABLESPACE DT_TEMP
tempfile '/u02/dbs/RIDEV/temp01.dbf' SIZE 320M
extent management local
uniform size 8M





#  path for data R12
/R12/oracle/SHIV/db/apps_st/data


#  select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name


# select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name


# CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf'      SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;





# ALTER TABLESPACE lmtbsb
   ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;



#  ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
   RESIZE 100M;




#  ALTER TABLESPACE temp_demo ADD TEMPFILE 'temp05.dbf' SIZE 5 AUTOEXTEND ON;

#  ALTER TABLESPACE temp_demo DROP TEMPFILE 'temp05.dbf';



#  All Invalid objects in database.

SELECT COUNT(*) FROM all_objects WHERE Status = 'INVALID';

Free size of database

SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;


#  Total size of Database

SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;

SELECT count(*) FROM v$session;



# 7. Total no of Process

SELECT COUNT (*) FROM v$process;



# 8. Total no of concurrent Process

SELECT COUNT(*) FROM fnd_concurrent_processes;



# 9. Total no of concurrent request

SELECT COUNT(*) FROM fnd_concurrent_requests;

# Number of users 

select  user_name,count(*)
from apps.fnd_logins a, apps.fnd_user b
where a.user_id=b.user_id
and a.login_id in
(
select login_id
from apps.fnd_login_responsibilities
where end_time is null
and trunc(start_time) = trunc(sysdate)
)
group by a.user_id, b.user_name;