How to tell uptime on windows?

Using one of the following commands one can tell the uptime on windows i.e. the time since the last reboot occurred.

REM on WinXP
C:> net statistics workstation | find /I “Statistics since”
Statistics since 12/28/2008 10:35 AM

REM on WinXP, Windows 2000
C:\>systeminfo | find /I “System Up Time”
System Up Time: 1 Days, 14 Hours, 18 Minutes, 51 Seconds

How to grant SELECT access to v$session to other users?

One can’t grant direct access V$session as v$session is a synonym.

SQL> GRANT SELECT ON v$session TO scott;
grant select on v$session to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

– shows the V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

– shows the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

But one can grant access the underlying table/view.

SQL> GRANT SELECT ON V_$SESSION TO scott;

Grant succeeded.

Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation.

What are the different options to shutdown?

shutdown (or shutdown normal) – In this method the database shuts down cleanly and oracle will wait for all users process to terminate before shutting down the database, so if there is user with SQL*Plus session that is idle oracle will not terminate till the user session exits it. If user sessions haven’t been terminated then you will see a following message in the alert.log
Shutting down instance: further logons disabled
…
Active process 2408 user ‘SYSTEM’ program ‘ORACLE.EXE (SHAD)’
SHUTDOWN: waiting for logins to complete.

shutdown immediate – In this mode oracle will kill all existing sessions and rollback all uncommitted transactions. Using this option instance recovery is not needed.

shutdown transactional – In this mode oracle will wait for user to commit their transaction so if the user is running a select statement it will terminate the user session unless it’s part of a transaction that is being run.

shutdown abort – In this mode one forces oracle to crash, in this mode instance require will be required not media recovery. This option can still be used even if any of other above shutdown options are in progress.
Example:
From one of the session, shutdown in progress but for some reason it’s hung even after one looks at the alert log file and trace files.
SQL> shutdown
– after the shutdown abort completes this session also terminates with the following message
ORA-03113: end-of-file on communication channel

From another session one can run shutdown abort
SQL> shutdown immediate;
ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file
SQL> shutdown abort;
ORACLE instance shut down.

When shutdown is in progress users will not able to login to the database. They will receive the following error “ORA-01090: shutdown in progress – connection is not permitted”.

Still for some reason one can’t still shutdown the database the only option that may remain is to kill the PMON process which will forcefully kill the process.

How to enable command line history scrolling on SQL*Plus on unix?

Using the utility rlwrap one can perform command line scrolling on SQL*Plus similar to that is available on windows version of SQL*Plus. The source code for it can be downloaded from the following site: http://utopia.knoware.nl/~hlub/uck/rlwrap/. To build rlwrap executable needs a library readline is needed which can be downloaded from http://directory.fsf.org/project/readline/.

One of the requirements to build this to have a C compiler like gcc.

To build the readline library after it’s downloaded and untar.
# directory where the source code was untarred, you will need to rename the directory to “readline” as the rlwrap looks for the header file readline.h in a readline folder
$ cd /u01/code/readline
# This step builds the makefile and it also checks if you have the necessary tools (gcc) and inspects your current env. It logs the information in config.log
$ ./configure
# step to build the library libreadline.a
$ make -f Makefile
# verify the library is built
$ ls -l libreadline.a
-rw-r–r– 1 user dba 1732206 Dec 12 17:28 libreadline.a

# step to build rlwrap
$ cd /u01/code/rlwrap-0.30
# set CPPFLAGS which is used to set for C parser, if not set it will not will be able to find the headerfile readline.h which is used to find the version
$ export CPPFLAGS=-I/u01/code/readline
# This step builds the makefile and it also checks if you have the necessary tools (gcc) and inspects your current env. It logs the information in config.log
$ ./configure CFLAGS=-I/u01/code LDFLAGS=-L/u01/code/readline
# this step will build the rlwrap executable, if no errors then it will build the rlwarp in the src directory
$ make

Then you can copy it this executable to /usr/local/bin and add this directory the PATH env so you can use it.

Example: $ rlwrap sqlplus
Enter user-name: username
Enter password: *******

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

How to fix when backup piece is not found when deleting obsolete backup piece?

As part of the RMAN script during backup to disk for example you may have a statement to delete the obsolete backups. And in cases when backup piece is not found the RMAN fails with the error shown below when it can’t delete the obsolete backup.

Example (portion of the script to delete obsolete backup):
run {
….
DELETE NOPROMPT OBSOLETE REDUNDANCY = 7;
}

Error message displayed when backup piece is not found.
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status

This error can be fixed if the FORCE option is used when deleting obsolete backup, it will update the RMAN repository that the backup has been deleted regardless it was found or not.

run {
DELETE FORCE NOPROMPT OBSOLETE REDUNDANCY = 7;
}

How to enable/disable block tracking for RMAN?

Using block tracking one can improve performance of the backup time of incremental backup from hours to minutes depending on size of database as it no longer needs to scan blocks in datafiles to find blocks changed it can use the block tracking file to identify changed blocks for incremental backup. Enabling this starts a new background process called Change Tracking Writer (CTWR) which manages this file.

Here are the steps to enable block tracking:
– Note if the file already exists the command will fail unless the REUSE option is used so first check if block tracking is not enabled before overwriting the file.

– if it returns DISABLED it means block tracking is off
SQL> SELECT distinct status from V$BLOCK_CHANGE_TRACKING;

STATUS
———-
DISABLED

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/u04/oradata/TEST/rman_block_tracking.f’;

Database altered.

– indicating block tracking is enabled and the find the block tracking file
SQL> select status, filename from V$BLOCK_CHANGE_TRACKING;

STATUS
———-
FILENAME
——————————————————————————–
ENABLED
/u04/oradata/TEST/rman_block_tracking.f’

After block tracking file is created you would need to do level 0 backup to ensure the next incremental backup uses the block tracking file.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

The subsequent incremental backup (differential/cumulative) would use the block change tracking file to identify the changes that need to be backed up.
– For differential incremental backup, this will backup blocks since last last level 0 or level 1 which ever is latter.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

– For cumulative incremental backups, this will backup all blocks changed since level 0 backup
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

To disable block tracking:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Shows the process that are using the file, and when RMAN is running it will have handles on the file when running incremental backup.
$ fuser /u04/oradata/TEST/rman_block_tracking.f
/u04/oradata/TEST/rman_block_tracking.f: 9597302
oracle 9597302 1 0 13:29:41 – 0:31 ora_ctwr_TEST