Tag Archives: oracle

2013-08-22
Leave a comment
Oracle, Technology

Oracle tip #2: check tablespace and usage

SELECT a.tablespace_name, round(a.bytes / 1048576) allocated_mb, round(b.free_bytes / 1048576) free_mb, ROUND(((b.free_bytes / 1048576) * 100) / (a.bytes / 1048576), 2) PERCENT_FREE FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name; 

TABLESPACE_NAME ALLOCATED_MB FREE_MB PERCENT_FREE
------------------------------ ------------ ---------- ------------
ACME_DATA 100 99 98.88
ACME_INDEX 100 99 98.94
EXAMPLE 100 21 21.25
RMAN_DATA 100 93 93.44
SYSAUX 820 56 6.78
SYSTEM 740 7 .96
UNDOTBS1 360 344 95.49
USERS 5 1 17.5
]]>

Tagged ,
2013-08-22
Leave a comment
Oracle, Technology

Oracle tip #1: make it friendly

#vi .bash_profile export SQLPATH=$HOME/scripts export PS1='[\u@\h:${ORACLE_SID}]$ ‘ [oracle@oraclemaster:orcl]$ #vi scripts/login.sql SET SQLPROMPT ‘&_USER.@&_CONNECT_IDENTIFIER.> ‘ SET PAGESIZE 100 SET LINESIZE 16000 SET LONG 90000 Example: 

# sudo su - oracle
[oracle@oraclemaster:orcl]$ sqlplus / as sysdba
SYS@orcl>
]]>

Tagged ,
2013-08-21
Leave a comment
Oracle, Technology

Learn Oracle in 1 hour: use Enterprise Manager and SQL Developer

  • Oracle Enterprise Manager (EM) is the web-based interface to manage Oracle server.
  • Oracle SQL Developer is a free Oracle database browser.
    • Use EM to understand how Oracle server works. Use SQL Developer to understand how to manage Oracle systems setting. Oracle Enterprise Manager: 
    https://[hostname]:1158/em/console/aboutApplication
    To start the dbconsole process: Navigate into your $ORACLE_HOME/bin directory Run the following statement: 
    ./emctl start dbconsole
    Additionally, you can stop the process and view its status. To stop the dbconsole process:sqlplus 
    ./emctl stop dbconsole
    To view the status of the dbconsole process: 
    ./emctl status dbconsole
    Oracle SQL Developer: To use SQL Developer simply install JavaSDK, download SQL Developer and run it using the java.exe. Create another superuser, therefore you can browse SYS without having to login to the server. Example: 
    CREATE USER supersu IDENTIFIED BY "0racle"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE temp
quota unlimited on "USERS";
GRANT CONNECT, RESOURCE TO supersu;
GRANT DBA TO supersu;
    You need to understand Oracle structures and many important default views inside SYS (browse to Other Users), such as: SYS.dba_users, SYS.dba_profiles, SYS. user_profiles, etc. Done!]]>

    Tagged ,
    2013-08-14
    Leave a comment
    Oracle, Technology

    Oracle for beginner: a simple backup and recovery

    http://selectsysdatefromdual.gnusystems.net/?p=71 Add another parameter like existing -f, -a, etc and create your own level 0 and 1 (I prefer using level 0 and 1 rather than full backup), then you could easily set into cronjob. We do a simple testing, do a full backup of Oracle running in archivelog mode: 

    #rmanbackup.sh -f
    You should see backup(s) of data files, control files and archive logs: 
    RMAN> list backup;
    If you want to delete and manage the backups, simply use this reference: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/maint003.htm Let’s start: 
    #echo $ORACLE_BASE
/home/oracle/app/oracle
SQL> shutdown abort;
$ cd /home/oracle/app/oracle/oradata/orcl
    Copy these all files and delete all of your data: 
    $ rm * -f
    Sure you can’t startup Oracle. You can fix it using 2 ways: (1) copy files back to the folder (2) using backups. Let’s use backups: 
    RMAN> startup nomount;
RMAN> restore controlfile from '/home/oracle/app/oracle/backups/controlspfile/c-1310390050-20110813-03';
    where c-1310390050-20110813-03 is your control file. If controlfile is corrupted, simply copy working control file to corrupted control file. Control file contains information about your data files, backup, etc. Once controlfile is recovered, do: 
    RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
    We do resetlogs because it’s running in archivelog mode to reset remaining archive log. Done!]]>

    Tagged , , ,