Skip to main content

SHAHID HUSSAIN ABRO

Search This Blog

SHAHID HUSSAIN ABRO: ORACLE DBA DAILY JOB ACTIVITIES

November 08, 2016

Get link
Facebook
X
Pinterest
Email
Other Apps

SHAHID HUSSAIN ABRO: ORACLE DBA DAILY JOB ACTIVITIES

Get link
Facebook
X
Pinterest
Email
Other Apps

Comments

Post a Comment

Shahid Hussain Abro

ORACLE DBA DAILY JOB ACTIVITIES

- Oracle Database instance is running or not SQL> select status from v$instance; - To shows the time that the current instance was started. SQL>SELECT TO_CHAR(STARTUP_TIME,'MON-DD-RR HH24:MI:SS') AS "Inst Start Time" FROM V$INSTANCE; - Check Datafiles SQL> SELECT NAME FROM V$DATAFILE; -Look for any new alert log entries SQL> select value from v$parameter where name = 'background_dump_dest'; SQL> show parameter background_dump_dest; VALUE ---------------------------------------------------------------------------------------------------- C:\ORACLE\ORACLE\PRODUCT\10.2.0\ADMIN\SRBDB\BDUMP -Track Tablespace Used/Free Space: SQL>SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round...

Shahid Hussain Abro

FINAL DATA GUARD ON ORACLE 11G ON LINUX SYSTEM

PRIMARY DATABASE 1. SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; 2. SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE; 3. SHU IMMEDIATE; 4. STARTUP 5. Create Folder with name "backup" and create pfile in backup folder SQL> Create pfile='/home/oracle/backup/pfile.ora' from spfile 6. Open Pfile and past following code. *.db_unique_name='PRIMARY' *.FAL_Client='primary' *.FAL_Server='standby' *.Log_archive_config='DG_CONFIG=(primary, standby)' *.Log_archive_dest_1='Location=/home/oracle/backup VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primary' *.Log_archive_dest_2='Service=standby lgwr async VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby' *.Log_archive_dest_state_1=ENABLE *.Log_archive_dest_state_2=ENABLE *.Service_names='srbdb' *.Standby_File_Management='AU...

USER ACCESS TO DATABASE WITHIN LIMITS OF TIME

SQL> create user shahid identified by shahid; User created. SQL> grant connect, resource to shahid; Grant succeeded. SQL> conn shahid/shahid Connected. SQL> disc Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr oduction With the Partitioning, OLAP and Data Mining options SQL> conn sys / as sysdba Password: xxxxxxxx Connected. SQL> CREATE OR REPLACE TRIGGER limit_connection AFTER LOGON ON DATABASE BEGIN IF USER = 'SHAHID' THEN IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22 THEN RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22'); END IF; END IF; END limit_connection; / Trigger created. SQL> select to_char(sysdate,'hh24') from dual; TO -- 23 SQL> conn sh...

HOW TO MIGRATE NON-ASM FILES TO ASM IN WINDOWS

21. Disk Groups are created now perform following steps. 22. SQL> select file_name from dba_data_files; 23. SQL> select MEMBER from v$logfile; 24. SQL> select name from v$controlfile; 25. SQL> SHUTDOWN IMMEDIATE 26. RMAN> STARTUP NOMOUNT 27. RMAN>restore controlfile to '+DG1' from 'C:\oracle\product\10.2.0\oradata\srbdb\CONTROL01.CTL'; 28. C:\oracle\product\10.2.0\srbdb\BIN>SET ORACLE_HOME=C:\ORACLE\PRODUCT\10.2.0\SRBDB 29. C:\oracle\product\10.2.0\srbdb\BIN>SET ORACLE_SID=+asm 30. C:\oracle\product\10.2.0\srbdb\BIN>asmcmd 31. ASMCMD> find -t CONTROLFILE +DG1 * Result: +DG1/SRBDB/CONTROLFILE/backup.256.808492297 32. SQL> alter system set control_files='+DG1/SRBDB/CONTROLFILE/backup.256.808402379' SCOPE=SPFILE; 33. SQL> shutdown 34. SQL> startup mount. 35. SQL> select name from v$controlfile; ...

How to block access to database by IP address in Linux and Windows

IN LINUX: Solution: 1. [oracle@localhost ~]$ cd $ORACLE_HOME/network/admin 2. [oracle@localhost admin]$ 3. Edit the sqlnet.ora file similar way: 4. [oracle@localhost admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. 5. Now Paste Following lines in sqlnet.ora NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) tcp.validnode_checking = yes tcp.invited_nodes = (localhost, 192.168.11.22) tcp.excluded_nodes = (192.168.33.44) 6. save it [oracle@localhost admin]$ 6. Restart the listener. Now connection to your database from one of the IPs excluded will be prevented. Example: Trying to connect mysid Oracle db from ip 192.168.33.44: C:\>sqlplus myuser/...@mysid SQL*Plus: Release 11.2.0.1.0 Production on Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-12537: TNS:connection closed Enter us...

CALCULATING THE FREE SPACE IN A DATAFILE AND TABLESPACES

select substr(A.tablespace_name,1,16) "Tablespace", MAX(A.contents) "Type", MAX(A.status) "Status", MAX(A.initial_extent)/1024 "Initial extent(Kb)", MAX(A.next_extent)/1024 "Next extent(Kb)", MAX(A.max_extents) "Max extents", MAX(A.pct_increase) "Pct_increase", (SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/ 1024/1024)-(ROUND(SUM(C.BYTES) /1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)", (SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-( SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS", TO_CHAR(100-(SUM(C.BLOCKS)* 100*COUNT(B.FILE_ID)/(SUM(B. BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE", ROUND(SUM(C.BYTES)/1024/1024/ COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)", SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS", TO_CHAR((SUM(C.BLOCKS)*100* COUNT(B.FILE_ID)/(SUM(B. BLOC...

Create Flashback data archive tablespace

SQL> create tablespace FB_Storage datafile '/u01/oracle/oradata/orcl/fdstore.dbf' size 100m autoextend on segment space management auto; Step 2:- Create Flashback Archive SQL> create flashback archive default FB_Arch1 tablespace FB_Storage quota 1g retention 1 year Flashback archive created. (i) Grant Flashback archive privilege SQL> grant flashback archive on fb_arch1 to scott; Grant succeeded. SQL> connect scott/tiger (ii) Enable flashback archive for table SQL> alter table emp flashback archive

Powered by Blogger

Shahid Hussain Abro