To see current stats of your session, one can run the following query.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#;
To view session stats of another session one can run the query below passing the session id.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# and sid = &v_sid;
There are couple of ways of finding if the database was started with spfile or pfile.
One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.
– belows show database was started by spfile
SQL> show parameter spfile;
NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora
Another way to find would be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.
SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup
To identify the parameter one can query v$parameter view to find the parameters that were modified. The column ISMODIFIED is FALSE by default when the instance starts up, when the value is changed it is SYSTEM_MOD indicating the value is changed at the SYSTEM level and if it’s MODIFIED then it’s changed at session level.
-- Before parameter change displaying value of ISMODIFIED SQL> select name, value, ismodified from v$parameter where name = 'open_cursors'; NAME VALUE ISMODIFIED -------------------- ----- ---------- open_cursors 301 FALSE SQL> alter system set open_cursors=300; System altered. SQL> select name, value, ismodified from v$parameter where name = 'open_cursors'; NAME VALUE ISMODIFIED -------------------- ----- ---------- open_cursors 300 SYSTEM_MOD
By having a logon trigger in a database one can monitor or audit OS user, programs, DB users, time users logging into a database. It could also be used the change a behavior of a session by altering user’s session’s based on certain criteria.
CREATE TABLE sys.logon_audit
(
logon_time DATE,
username VARCHAR2(30), — DB user
osuser VARCHAR2(30), — OS user
machine VARCHAR2(64), — machine
program VARCHAR2(48 )
) TABLESPACE dba_tbs;
CREATE OR REPLACE TRIGGER sys.connection_audit
AFTER LOGON ON database
DECLARE
– use sys_context for 9i and above
– for 8i use userenv(‘sessionid’)
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session;
– WHERE audsid = sys_context(‘USERENV’,'sessionid’);
rec_c1 c1%ROWTYPE;
BEGIN
rec_c1 := NULL;
OPEN c1;
FETCH c1 INTO rec_c1;
– record will always be found, so need to check record not found
INSERT INTO user_audit(logon_time, username, osuser, machine, program)
VALUES (sysdate, user, rec_c1.osuser, rec_c1.machine, rec_c1.program);
CLOSE c1;
END;
/
Note: You may also implement a routine to delete data from the table as if the tablespace gets full users won’t be able to login.
If you are doing migrating to a new filesystem and moving files, it may be a good idea to check before migrating the filenames are unique across all filesystems by running the following query you find the files that have the same name.
If the query returns no rows found then you don’t have duplicate filenames.
SELECT SUBSTR(file_name, instr(file_name, ‘/’, -1))
FROM (SELECT file_name FROM dba_data_files
UNION ALL
SELECT file_name FROM dba_temp_files
UNION ALL
SELECT member as file_name FROM v$logfile
UNION ALL
SELECT name as file_name FROM v$controlfile)
GROUP BY file_name
HAVING COUNT(1) > 1;
Note: On windows the filenames are not case-sensitive so adding upper/lower to convert to same case would be required to ensure filenames are unique.
If you are not using RMAN to backup data and use technologies like NetApp snapshot which doesn’t check for physical and logical corruption, one can still use RMAN to check for physical and logical corruption in the datafiles and not back the data up.
connect target /
run {
# set disk to be default device type
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
# number of concurrent sessions to spawn
CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
# check datafiles for corruption 10 datafiles in each session
BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;
}
Corruptions information are recorded in the V$DATABASE_BLOCK_CORRUPTION view.
Using incorrect format when backing up to tape can cause an issue during recovery if the backup pieces have the same file name. For example if the format specified is format ‘fullhot_%d_%t.bus’ and multiple channels are used it could create file with the same name on the tape which will cause database restore fail and RMAN could generate the following error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/06/2008 09:29:05
ORA-19615: some files not found in backup set
ORA-19613: datafile 45 not found in backup set
So using the following format fullhot_%d_%U_%t.bus can ensure the file name of the backup piece is unique.
%d – The name of the database.
%U – A convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, RMAN uses %U by default.
%t = The backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.
To see information on other format option visit the URL http://www.ss64.com/ora/rman_format_string.html
Using the following Unix command one can verify if the file name is unique for a database. If the command below returns an output it means that back piece file is duplicate.
$ grep handle rman_ORATEST.log | awk -F’=’ ‘{print $2}’ | awk ‘{print $1}’ | sort | uniq -d
To check across all databases assuming that the RMAN log files are in one folder would compare within one and also across databases (e.g. DB1, DB2, DB3)
$ grep handle rman_DB1.log rman_DB2.log rman_DB3.log … | awk -F’=’ ‘{print $2}’ | awk ‘{print $1}’ | sort | uniq -d