SYSDBA can do more than start/stop the database. It has a lot more functionality that sysoper (Operator mode) which is normally used for basic database functions such as start/stop.
SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks
without the ability to look at user data.
SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when
previously connected INTERNAL.
Thursday, April 29, 2010
Size of SWAP file during installing Oracle in linux !
Sizing Swap Space:
------------------
The amount of swap space to allocate depends on the amount of RAM installed on the machine. The following conditions show the RAM size followed by the reasonable swap space size, respectively:
<= 2 GB: 150% of the RAM size
Between 2 GB and 8 GB: Equal to the RAM size
> 8 GB: 75% of the RAM size
Some examples are shown in the slide. You can use the free command to see the size of the swap space, and also to see how much of it is being used. You can also view the contents of /proc/swaps.
Note: Diagram sizes are not to scale.
1GB RAM = 1.5 GB swap
4 GB RAM = 4 GB swap
10 GB RAM = 7.5 GB swap
http://dbatricksworld.com/oracle11gr2-software-and-database-installation-on-oracle-linux-6.5
www.dba-oracle.com/t_server_swap_space_allocation.htm
------------------
The amount of swap space to allocate depends on the amount of RAM installed on the machine. The following conditions show the RAM size followed by the reasonable swap space size, respectively:
<= 2 GB: 150% of the RAM size
Between 2 GB and 8 GB: Equal to the RAM size
> 8 GB: 75% of the RAM size
Some examples are shown in the slide. You can use the free command to see the size of the swap space, and also to see how much of it is being used. You can also view the contents of /proc/swaps.
Note: Diagram sizes are not to scale.
1GB RAM = 1.5 GB swap
4 GB RAM = 4 GB swap
10 GB RAM = 7.5 GB swap
http://dbatricksworld.com/oracle11gr2-software-and-database-installation-on-oracle-linux-6.5
www.dba-oracle.com/t_server_swap_space_allocation.htm
How to get scn value from Database !
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
8653578
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
8653579
CURRENT_SCN
-----------
8653578
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
8653579
Find Username and SPID !
Find Username and SPID :
------------------------
select a.username,a.sid,b.spid from v$session a, v$process b where a.sid = '1638634'
select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#
Here , 'SPID' of the Database is equivalent the 'process id' of oracle processes .We get the process id using below commands :
"ps -ef | grep oracle"
If we need to kill it then give the below command :
kill -9 "procee id" //In both Linux and AIX environment .
------------------------
select a.username,a.sid,b.spid from v$session a, v$process b where a.sid = '1638634'
select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#
Here , 'SPID' of the Database is equivalent the 'process id' of oracle processes .We get the process id using below commands :
"ps -ef | grep oracle"
If we need to kill it then give the below command :
kill -9 "procee id" //In both Linux and AIX environment .
OEM Error : EmKey not configured properly !
EmKey not configured properly. Run "emctl status emkey" :
===========================================================
you need to follow the steps as below :
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create
help :
http://forums.oracle.com/forums/thread.jspa?threadID=911292
http://www.akadia.com/services/ora_dbconsole.html
===========================================================
you need to follow the steps as below :
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create
help :
http://forums.oracle.com/forums/thread.jspa?threadID=911292
http://www.akadia.com/services/ora_dbconsole.html
RECOVER A DATAFILE WITH MISSING ARCHIVELOGS !
RECOVER A DATAFILE WITH MISSING ARCHIVELOGS :
==============================================
Metalink Doc ID: 418476.1
Applies to:
===========
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.4
This problem can occur on any platform.
Symptoms
=========
Database cannot be opened, because a datafile checkpoint is lagging behind from the rest of the datafiles.
Cause
=====
A datafile was restored from a previous backup, but archivelogs required to recover the said datafile are missing.
Solution
========
There are 3 options available, as shown below:
Option#1:
Restore the database from the same backupset, and then recover it by applying up to the last available archivelog to roll it forward, but any updates to the database after the point-in-time of recovery will be lost.
Option#2:
Force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE in the init.ora. But there is no 100% guarantee that we can open the database. However, once the database is opened, then you must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a new and separate database, and finally (3) import the recent export dump.
Note:
When exporting after opening the database with the allow corruption parameters, you should set the Event 10231 before export to extract data from all non-corrupt > blocks in order to be able to import the data successfully. For additional information about Event 10231, please review Note 21205.1 : EVENT: 10231 "skip corrupted blocks on _table_scans_".
Option#3:
Manually extract the data using the Oracle's Data Unloader (DUL), which is performed by Oracle Field Support on-site for an extra charge.
==============================================
Metalink Doc ID: 418476.1
Applies to:
===========
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.4
This problem can occur on any platform.
Symptoms
=========
Database cannot be opened, because a datafile checkpoint is lagging behind from the rest of the datafiles.
Cause
=====
A datafile was restored from a previous backup, but archivelogs required to recover the said datafile are missing.
Solution
========
There are 3 options available, as shown below:
Option#1:
Restore the database from the same backupset, and then recover it by applying up to the last available archivelog to roll it forward, but any updates to the database after the point-in-time of recovery will be lost.
Option#2:
Force open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE in the init.ora. But there is no 100% guarantee that we can open the database. However, once the database is opened, then you must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a new and separate database, and finally (3) import the recent export dump.
Note:
When exporting after opening the database with the allow corruption parameters, you should set the Event 10231 before export to extract data from all non-corrupt > blocks in order to be able to import the data successfully. For additional information about Event 10231, please review Note 21205.1 : EVENT: 10231 "skip corrupted blocks on _table_scans_".
Option#3:
Manually extract the data using the Oracle's Data Unloader (DUL), which is performed by Oracle Field Support on-site for an extra charge.
Run rman scripts with prompting rman password !
Linux :
=======
read -s -p "Enter password:" mypass
rman target sys/$mypass <run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
AIX
====
echo "password please:\c"
stty -echo
read mypass
stty echo
rman target sys/$mypass@onlinete << !
run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
RMAN runs from hidden password :
================================
1) create /home/oracle/.passwd
vi .passwd
sys123
2) vi rmanback.sh
export ORACLE_HOME=/orafs/app/oracle/10.2.0/db_1
pwd=`cat /home/oracle/.passwd`
$ORACLE_HOME/bin/rman target sys/$pwd@onlinete << !
run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
=======
read -s -p "Enter password:" mypass
rman target sys/$mypass <run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
AIX
====
echo "password please:\c"
stty -echo
read mypass
stty echo
rman target sys/$mypass@onlinete << !
run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
RMAN runs from hidden password :
================================
1) create /home/oracle/.passwd
vi .passwd
sys123
2) vi rmanback.sh
export ORACLE_HOME=/orafs/app/oracle/10.2.0/db_1
pwd=`cat /home/oracle/.passwd`
$ORACLE_HOME/bin/rman target sys/$pwd@onlinete << !
run
{
allocate channel t1 type disk;
backup format 'df_%t_%s_%p'
(database);}
Configure Listener with two ports !
Listener with two ports:
=======================
listeners1 =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1522))
)
=======================
listeners1 =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1522))
)
Script for Deleting users from database !
From Toad :
We can use the below query :
SELECT 'DROP USER ' || USERNAME || ' CASCADE;' FROM DBA_USERS WHERE USER_ID>54
From OS we can use the below scripts :
script for Linux :
==================
[oracle@rptsvr ~]$ cat drop.sh
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
read -s -p "Enter username:" user
$ORACLE_HOME/bin/sqlplus sys/sys123 as sysdba << !
drop user $user cascade;
Script for AIX :
================
bash-3.00$ cat drop.sh
export ORACLE_HOME=/orafs/app/oracle/10.2.0/db_1
export ORACLE_SID=onlinete
echo "user1 please:\c"
read user1
echo "user2 please:\c"
read user2
$ORACLE_HOME/bin/sqlplus sys/sys123@onlinete as sysdba << !
drop user $user1 cascade;
drop user $user2 cascade;
We can use the below query :
SELECT 'DROP USER ' || USERNAME || ' CASCADE;' FROM DBA_USERS WHERE USER_ID>54
From OS we can use the below scripts :
script for Linux :
==================
[oracle@rptsvr ~]$ cat drop.sh
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
read -s -p "Enter username:" user
$ORACLE_HOME/bin/sqlplus sys/sys123 as sysdba << !
drop user $user cascade;
Script for AIX :
================
bash-3.00$ cat drop.sh
export ORACLE_HOME=/orafs/app/oracle/10.2.0/db_1
export ORACLE_SID=onlinete
echo "user1 please:\c"
read user1
echo "user2 please:\c"
read user2
$ORACLE_HOME/bin/sqlplus sys/sys123@onlinete as sysdba << !
drop user $user1 cascade;
drop user $user2 cascade;
How to disable Sysdba Authentication !
You can 'disable' the OS auth of sysdba by putting this line into sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NONE)
OR,
REMOTE_LOGIN_PASSWORDFILE=NONE
However, it could be 'enabled' just as easy by removing it. So it's not really adding too much security .
SQLNET.AUTHENTICATION_SERVICES=(NONE)
OR,
REMOTE_LOGIN_PASSWORDFILE=NONE
However, it could be 'enabled' just as easy by removing it. So it's not really adding too much security .
Subscribe to:
Posts (Atom)