How to see database instance information?

Please execute the commands as "sys";

SQL> SELECT * FROM v$instance;

To see db startup time:
SQL>
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM v$instance;

How to see version of database?

Please execute the commands as "sys";

SQL> select version from v$instance;

How to see resource limits?

Please execute the commands as "sys";

SQL> SELECT * FROM v$resource_limit;

How to see all tablespace quotas?

Please execute the commands as "sys";

SQL> SELECT * FROM DBA_TS_QUOTAS;

How to see all profiles and assigned limits?

Please execute the commands as "sys";

SQL> SELECT * FROM DBA_PROFILES ORDER BY PROFILE;

How to check database link?

SQL> SELECT 1 FROM dual@mydblink

How to configure EM (Enterprose Manager) if has any problem?

On linux terminal;
set ORACLE_SID=<YOURSID>
emca -deconfig dbcontrol db -repos drop;
then;
emca -config dbcontrol db -repos create;
or
emca -config dbcontrol db -repos recreate;

How to delete archive log files until specific time in RMAN?

RMAN> delete archivelog until time ‘SYSDATE-10’;
i: it will delete them until 10 days ago

Also it has a no prompt option;
RMAN> delete noprompt archivelog until time ‘SYSDATE-10′;

How to check/open archive log in sqlplus?

Check in archive log open:
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
or
SQL> archive log list;

Open achieve log mode:
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Enterprise Manager and OEM Agent commands in Oracle

Please login to database server with ssh as oracle user and run that commands.

Go to /bin path
su - grid12
cd agent/agent_inst/bin

How to start/stop Enterprise Manager
./emctl start dbconsole;
./emctl stop dbconsole;

for 12c grid EM
/home/grid12/oms/bin/emctl start oms
/home/grid12/oms/bin/emctl stop oms

How to start/stop Oem Agent
Be sure run root.sh as root user and run below commands as oracle user:
./emctl stop agent
./emctl start agent

How to check Oem Agent
./emctl status agent

How to restore Oem Agent
Run as grid12 user:
./emctl clearstate agent; and then
./emctl upload agent;

How to alter a profile in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba

SQL> ALTER PROFILE engineer LIMIT 
SESSIONS_PER_USER 5
IDLE_TIME 60
CONNECT_TIME 1200;

SQL> exit

-- SESSIONS_PER_USER; Specify the number of concurrent sessions to which you want to limit the user.
-- IDLE_TIME; Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
-- CONNECT_TIME; Specify the total elapsed time limit for a session, expressed in minutes.

How to define new profile or change old profile in Oracle?

Please login to database server with ssh as oracle user and run that commands.

sqlplus / as sysdba

SQL> CREATE PROFILE engineer LIMIT 
SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;

SQL> exit

-- SESSIONS_PER_USER; Specify the number of concurrent sessions to which you want to limit the user.
-- IDLE_TIME; Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
-- CONNECT_TIME; Specify the total elapsed time limit for a session, expressed in minutes.

other parameters:
CPU_PER_SESSION; Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL; Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
LOGICAL_READS_PER_SESSION; Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL; Specify the permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA; Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). Refer to size_clause for information on that clause.
COMPOSITE_LIMIT; Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

Change current profile
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60;
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 6000;
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 1;

Add this parameter into init.ora file permanently or apply this for already running instance temporary:
Option 1: 
*.resource_limit=TRUE 
Option 2:
alter system set resource_limit = true;

How to know system date in Oracle?

Please run that SQL in your editor's SQL window.

select sysdate from dual;

How to connect linux machine with XStart -interface?

If you have XManager tool, you can easily connect to linux machine like Windows machines.

Firstly, change runlevel in /etc/inittab as follow;
Id:3:initdefault;
Then, reboot the machine.

After that please follow the steps:

Open XStart-> Click New and input a session name 
-> Input IP into Host, select SSH protocol and username/password 
-> Then select execution command like "/usr/bin/gnome-session --display $DISPLAY"
-> Click Run button -> Lastly, you should see the linux window

or

Open XShell-> Connect and login to machine with ssh 
-> Input IP into Host, select SSH protocol and username/password 
-> Then run this command "/usr/bin/gnome-session --display $DISPLAY"
-> Lastly, you should see the linux window

How to check ports in Linux machines?

Please run the command as linux system user.

For example, you want to check .80 port
# netstat -anp | grep :80
or
# netstat -tulpn | grep :80

How to see last updated (DDL) table object in Oracle?

Please run below sql script as desired database user;

SQL> select * from all_objects order by last_ddl_time desc;

How to use Java binary for all linux system users?

To execute java binary (bin) for all users, please run these commands below;

sudo ln -s -v /opt/jdk1.7/bin/java /usr/bin/java
sudo ln -s -v /opt/jdk/bin/javac /usr/bin/javac
              <target_name>      <sembolic-file_name>

<target_name> ; java installation directory
<sembolic-file_name> ; binary directory of all users

How to see PGA size per oracle session?

Please execute following SQL lines as "sys";

select
    1048576+a.value+b.value   pga_size
from
   v$parameter a,
   v$parameter b
where
   a.name = 'sort_area_size'
and
   b.name = 'hash_area_size';

How to set SHMMAX value and what is that?

Setting the kernel.shmmax parameter does not reserve or allocate any shared memory. SHMMAX is a safeguard parameter that sets the upper limit for a process can allocate how much shared memory when requested.

Please execute following commands as “root”;
sysctl -w kernel.shmmax=2147483648
(2147483648 -> 2 GB)

or
modify /etc/sysctl.conf file

sysctl -p
(to apply it)

How to see process that used memory or CPU at most?

Please run the command as linux system user.

# top
at manual -> shift + f and then n (mem) or k (cpu)

to highlight the column: press b

to sort by time: press shift + t

to see all CPUs: press 1

to get the different graphical output: press t

Note: Type and press enter bold ones to run