Please execute the commands as "sys";
SQL> select version from v$instance;
SQL> select version from v$instance;
Wednesday, 15 October 2014
How to see resource limits?
Please execute the commands as "sys";
SQL> SELECT * FROM v$resource_limit;
SQL> SELECT * FROM v$resource_limit;
How to see all tablespace quotas?
Please execute the commands as "sys";
SQL> SELECT * FROM DBA_TS_QUOTAS;
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;
SQL> SELECT * FROM DBA_PROFILES ORDER BY PROFILE;
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;
set ORACLE_SID=<YOURSID>
emca -deconfig dbcontrol db -repos drop;
then;
emca -config dbcontrol db -repos create;
or
emca -config dbcontrol db -repos recreate;
Tuesday, 14 October 2014
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′;
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;
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;
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.
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;
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;
Monday, 13 October 2014
How to know system date in Oracle?
Please run that SQL in your editor's SQL window.
select sysdate from dual;
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
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
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;
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
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';
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)
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
# 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
Note: Type and press enter bold ones to run
What should you do if electricty down or reboot exists in lvm system?
For this case,
You should mount the logic volume (lv) to related path.
For example, you are using ftp server;
Run this command after system is up:
mount /dev/my_vg_ora/vol01 /srv/ftp/myhome/
(mount - logic volume path - mounted path)
You should mount the logic volume (lv) to related path.
For example, you are using ftp server;
Run this command after system is up:
mount /dev/my_vg_ora/vol01 /srv/ftp/myhome/
(mount - logic volume path - mounted path)
Subscribe to:
Posts (Atom)