DBA_ROLES
DBA_COL_PRIVS
DBA_ROLE_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS
SESSION_PRIVS
SESSION_ROLES
Find Blocks in the Buffer cache
Below is the query to find how many blocks for each segment are currently in the buffer Cache.
RAC Database:
select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);
Non-RAC Database:
select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);
Excluding Tablespace from RMAN Backup
You have a tablespace with test data that you don't need to backup. You can exclude such tablespaces from a full backup of the database.
Below command shows the list of tablespaces that are already configured to be excluded from backups:
RMAN> show exclude;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters
Use following command to exclude tablespace from full database backup.
RMAN> configure exclude for tablespace users;
Tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
To confirm that the tablespace is excluded RMAN backup, run the below command
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';
Now, if you want to include a previously excluded tablespace in your backup and that is done by using the following command.
RMAN> configure exclude for tablespace users clear;
Tablespace USERS will be included in future whole database backups
old RMAN configuration parameters are successfully deleted
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters
Now, if you want include all tablespaces that are configured in "exclude tablespace", You can use the 'noexclude' option as part of a backup database command.
RMAN>backup database noexclude;
Below command shows the list of tablespaces that are already configured to be excluded from backups:
RMAN> show exclude;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters
Use following command to exclude tablespace from full database backup.
RMAN> configure exclude for tablespace users;
Tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored
To confirm that the tablespace is excluded RMAN backup, run the below command
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';
Now, if you want to include a previously excluded tablespace in your backup and that is done by using the following command.
RMAN> configure exclude for tablespace users clear;
Tablespace USERS will be included in future whole database backups
old RMAN configuration parameters are successfully deleted
RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters
Now, if you want include all tablespaces that are configured in "exclude tablespace", You can use the 'noexclude' option as part of a backup database command.
RMAN>backup database noexclude;
Find user's IP address
select sid, machine,UTL_INADDR.get_host_address
substr(machine,instr(machine,'\')+1))ip from v$session
where type='USER' and username is not null order by sid;
substr(machine,instr(machine,'\')+1))ip from v$session
where type='USER' and username is not null order by sid;
Drop Database using RMAN
Connect to a target database and make sure that the database is in "mount exclusive" state and not open. You need to start in the RESTRICT mode.
RMAN> connect target /
connected to target database: TEST (DBID=233344476, not open)
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "TEST" and DBID is 233344476
Do you really want to drop all backups and the database (enter YES or NO)? YES
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
database name is "TEST" and DBID is 233344476
database dropped
RMAN> connect target /
connected to target database: TEST (DBID=233344476, not open)
RMAN> SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';
using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION
RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "TEST" and DBID is 233344476
Do you really want to drop all backups and the database (enter YES or NO)? YES
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=421 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
database name is "TEST" and DBID is 233344476
database dropped
Database Size
For Non-RAC database:
select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
For RAC database:
select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from gv$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
For RAC database:
select DATA.TOTAL/1024/1024 "DataFile Size Mb",
LOG.TOTAL/1024/1024 "Redo Log Size Mb",
CONTROL.TOTAL/1024/1024 "Control File Size Mb",
(DATA.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1024/1024 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DATA,
(select sum(b.bytes) TOTAL from gv$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
OS Specific Commands
OS
|
SWAP
|
RAM
|
OS
VERSION
|
AIX
|
/usr/sbin/lsps
-a
|
/usr/sbin/lsattr
-HE -l sys0 -a realmem
|
oslevel
|
HP
PA-RISC
|
swapinfo
-a
|
grep
"Physical:" /var/adm/syslog/syslog.log
|
uname
-a
|
HP
Itanium
|
swapinfo
-a
|
/usr/contrib/bin/machinfo
| grep -i Memory
|
uname
-a
|
Tru64
|
swapon
-s
|
vmstat
-P
|
/usr/sbin/sizer
-v
|
Solaris
|
swap
-l
|
/usr/sbin/prtconf
| grep -i memory
|
uname
-r
|
Linux
|
free
|
free
|
uname
-a
|
Mac
OS X
|
#
df -h /
|
#
/usr/sbin/system_profiler SPHardwareDataType | grep Memory
|
#
sw_vers
|
OS
|
LOCATION
|
COMMAND
TO SEARCH
|
AIX
|
automatically
configured
|
do
'env' for LINK_CNTRL,
To determine if AIX is 64 bit enabled do 'genkex | grep 64' or 'genkex | grep call' |
HP
|
/stand/system
or use SAM -->Kernel Configuration
|
/etc/sysdef,
/usr/sbin/kmtune (kmtune desupported in 11.31, use /usr/sbin/kctune -v), or
/usr/sbin/kcweb -F
|
Tru64
|
/etc/sysconfigtab
|
/sbin/sysconfig
-q ipc or /sbin/sysconfig -q vm or /sbin/sysconfig -q proc
|
Solaris
|
/etc/system
|
"/etc/sysdef
| grep SHM" or "/etc/sysdef | grep SEM"
|
Linux
|
/usr/src/linux/include/asm/shmparam.h
/usr/src/linux/include/linux/sem.h /proc/sys/kernel/sem /proc/sys/kernel/shmall /proc/sys/kernel/shmmax /proc/sys/kernel/shmmni |
ipcs
-lms
|
Mac
OS X
|
/etc/sysctl.conf
|
"#
/usr/sbin/sysctl -a | grep "
|
OS
|
COMMAND
FOR PACKAGES
|
COMMAND
FOR PATCHES
|
AIX
|
lslpp
-w | grep -i "software title" (applies to APARs and PTFs)
|
/usr/sbin/instfix
-ik patch number
|
HP
|
Prior
to 11: /usr/sbin/swlist -lproduct PH\* hp-ux 11 and after: * /usr/sbin/swlist
-l patch \*\.*,c=patch * /usr/contrib/bin/show_patches (from patch
PHCO_19550)
|
/usr/sbin/swlist
-l fileset | grep -i
|
Solaris
|
/bin/pkginfo
-l | grep -i "software title"
|
/bin/showrev
-p
|
Tru64
|
/usr/sbin/setld
-i | grep -i "software title"
|
/usr/sbin/setld
-i | more
For patchkits: /usr/sbin/dupatch -track -type kit |
Linux
|
To see if a particular RPM is installed (without the architectur): $ rpm -qa | grep "package name" To see if a particular RPM is installed (with the architectur): $ rpm -qa --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" | grep "package name" To see what RPM provided a particular object/library: $ rpm -q --whatprovides --qf "%{NAME}-%{VERSION}-%{RELEASE}_%{ARCH}\\n" "full path/library name" To see the contents listing of an RPM: $ rpm -ql "RPM name" For example: $ rpm -ql compat-libstdc++-33-3.2.3-47.3.ppc /usr/lib/libstdc++.so.5 /usr/lib/libstdc++.so.5.0.7 |
|
Mac
OS X
|
to
be supplied later
|
to
be supplied later
|
To Check if an OS is 64 bit Capable or not
COMMAND
|
RESULTS
|
|
Aix
|
lslpp -L | grep 64bit
|
It should return
"bos.64bit"
|
HP
|
getconf KERNEL_BITS
|
It should return "64"
|
Solaris
|
/bin/isainfo -kv
|
If the isainfo command does not
exist it is not 64-bit. It should return "64-bit sparcv9 kernel
modules"
|
Relationship between Init.ora parameters and Kernel parameters
The following table documents Unix and Linux kernel parameters that should be monitored and possibly increased after changes are made to the related init.ora parameter. Please check your Operating System document for specific details on the parameter changes.
Init.ora Parameter Kernel Parameter
db_block_buffers shmmax, shmall
db_files(maxdatafiles) nfile, maxfiles
large_pool_size shmmax, shmall
log_buffer shmmax, shmall
processes nproc, semmsl, semmns
shared_pool_size shmmax, shmall
Init.ora Parameter Kernel Parameter
db_block_buffers shmmax, shmall
db_files(maxdatafiles) nfile, maxfiles
large_pool_size shmmax, shmall
log_buffer shmmax, shmall
processes nproc, semmsl, semmns
shared_pool_size shmmax, shmall
Subscribe to:
Posts (Atom)