How to check progress of a long running statistics gathering job

If you have a long-running statistics job running, you can check it from v$session_longops:

For example, you execute:

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.

Check progress with:

SQL> select sofar, totalwork,units,start_time,time_remaining,message  
     from v$session_longops
     where opname = 'Gather Dictionary Schema Statistics';

SOFAR  TOTALWORK UNITS                START_TIM TIME_REMAINING MESSAGE
---------- ---------- -------------------- --------- -------------- ------------------------------------------------
       423        423 Objects              03-JUN-14              0 Gather Dictionary Schema Statistics: Dictionary Schema : 423 out of 423 Objects done

How to solve "unknown nfs status return value: -1" when trying to mount an NFS drive on AIX on Linux

My error:

[root@mylinuxserver/]# mount -o nfsvers=2 -o nolock mynfsserver:/u01/software /myshare
mount: mynfsserver:/u01/software failed, reason given by server: unknown nfs status return value: -1

Solution: Add the client's ip-address and the name to the NFS servers /etc/hosts file, then retry the mount command.

Share is now mounted and usable:

[root@mylinuxserver/]# df -h -F nfs
Filesystem            Size  Used Avail Use% Mounted on
mynfsserver:/u01/software     705G  654G   52G  93% /myshare

Apparently the error can also be overcome by making sure the client is set up with "reversed lookup" in your DNS server.
In other words, nslookup should be able to resolve both the hostname of the client and the ip address of the client.

How to display Linux kernel and version information

List full distribution name:

[root@myserver/]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

List kernel version:

[root@myserver/]# cat /proc/version
Linux version 2.6.18-92.el5xen (brewbuilder@ls20-bc2-13.build.redhat.com) (gcc version 4.1.2 20071124 (Red Hat 4.1.2-41)) #1 SMP Tue Apr 29 13:31:30 EDT 2008

List processor type:

[root@myserver/]# uname -p
x86_64

List all:

root@myserver/]# uname -a
Linux myserver.mydomain.com 2.6.18-92.el5xen #1 SMP Tue Apr 29 13:31:30 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

From RHEL 7, you can use "hostnamectl":

[root@myhost ~]# hostnamectl
   Static hostname: myhost.mydomain.com
         Icon name: computer-vm
           Chassis: vm
        Machine ID: ******
           Boot ID: ******
    Virtualization: vmware
  Operating System: Red Hat Enterprise Linux Server 7.8 (Maipo)
       CPE OS Name: cpe:/o:redhat:enterprise_linux:7.8:GA:server
            Kernel: Linux 3.10.0-1127.19.1.el7.x86_64
      Architecture: x86-64

The contributor slm gives a good explaination on the background for hostnamectl in this article on stackexchange.com

How to recreate the control file and rename the database and the datafiles

To change a database name or file names, it can be practical to recreate the control file, and in some cases your only option.

In this example, I will rename a database and its file structure from "OID11UI" to "OID11U1".

Start by generating a file which holds the current layout of all the database's files:

SQL> alter database backup controlfile to trace as '/u01/oracle/product/11204/dbs/cntr_trace.sql' resetlogs;

Database altered.

The generated file typically look like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OID11UI" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11UI/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11UI/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11UI/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11UI/system01.dbf',
  '/u02/oradata/OID11UI/sysaux01.dbf',
  '/u02/oradata/OID11UI/undotbs01.dbf',
  '/u02/oradata/OID11UI/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11UI/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Exchange the word "REUSE" against the word "SET":

CREATE CONTROLFILE SET DATABASE "OID11U1"

Optionally, on the first line, add a pointer to a correct parameter file:

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'

Change all references to "OID11UI" (the old name):

STARTUP NOMOUNT PFILE='/u01/oracle/product/11204/dbs/initOID11U1.ora'
CREATE CONTROLFILE SET DATABASE "OID11U1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u02/oradata/OID11U1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u02/oradata/OID11U1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u02/oradata/OID11U1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u02/oradata/OID11U1/system01.dbf',
  '/u02/oradata/OID11U1/sysaux01.dbf',
  '/u02/oradata/OID11U1/undotbs01.dbf',
  '/u02/oradata/OID11U1/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata/OID11U1/temp01.dbf'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.

Change the folder name that oracle uses:

usu0oid01:OID11UI>cd /u02/oradata
usu0oid01:OID11UI>mv OID11UI OID11U1

Change the control_files directive in the parameter file:
Before

*.control_files='/u02/oradata/OID11UI/control01.ctl','/u02/oradata/OID11UI/control02.ctl'

After:

*.control_files='/u02/oradata/OID11U1/control01.ctl','/u02/oradata/OID11U1/control02.ctl'

Remove the old controlfiles physically from disk:

cd /u02/oradata/OID11U1
rm *.ctl

Shutdown the database:

SQL> shutdown abort
ORACLE instance shut down.

Change the oracle profile in the operating system:

cd
vi .profile

Before:

export ORACLE_SID=OID11UI

After:

export ORACLE_SID=OID11U1

Source the new profile, and check that the ORACLE_SID environmental variable is correctly set:

. .profile
usu0oid01:OID11U1>echo $ORACLE_SID
OID11U1

Start sqlplus and run script:

usu0oid01:OID11UI>sqlplus / as sysdba @cntr_trace.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 28 14:00:50 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2247072 bytes
Variable Size            1107297888 bytes
Database Buffers          603979776 bytes
Redo Buffers                6803456 bytes

Control file created.

Database altered.

Tablespace altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check the location of the data files after the change:

SQL> select file_name from dba_data_files union select member from v$logfile  union  select name from v$controlfile;

FILE_NAME
--------------------------------------------------
/u02/oradata/OID11U1/control01.ctl
/u02/oradata/OID11U1/control02.ctl
/u02/oradata/OID11U1/redo01.log
/u02/oradata/OID11U1/redo02.log
/u02/oradata/OID11U1/redo03.log
/u02/oradata/OID11U1/sysaux01.dbf
/u02/oradata/OID11U1/system01.dbf
/u02/oradata/OID11U1/undotbs01.dbf
/u02/oradata/OID11U1/users01.dbf

Lag en spfile (anbefales):

SQL> create spfile from pfile; 

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup the database for normal use:

SQL> startup

How to use adrci to tail the alert log

oracle@myhost:[PRODDB01]# adrci

ADRCI: Release 11.2.0.4.0 - Production on Wed May 28 15:06:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/oracle"

Display which homes that exists, and set the homelocation to point to the database's home:

adrci> show homes

ADR Homes:
diag/rdbms/PRODDB01/PRODDB01
diag/tnslsnr/myhost/listener
adrci> set home diag/rdbms/PRODDB01/PRODDB01

Tail the log:

adrci> show alert -tail -f

Other handy comments related to the alert log:

show alert
show alert -tail 50
show alert -p "module_id='DBMS_SCHEDULER'"
show alert -p "module_id != 'DBMS_SCHEDULER'"
show alert -p "module_id LIKE '%SCHEDULER%'"

If you want to extract the Streams related statements from the alert log, you would use:

show alert -p "message_text like '%STREAM%'"

See also this post for searching in adr using adrci

How to backup and delete a specific range of archivelogs

Use the "BACKKUP ARCHIVELOG FROM LOGSEQ .. UNTIL LOGSEQ" syntax:

RMAN> backup archivelog from logseq 3 until logseq 4 delete input;

Output will be similar to:

Starting backup at 27-MAY-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=3 STAMP=848499170
input archived log thread=1 sequence=4 RECID=4 STAMP=848499311
channel ORA_DISK_1: starting piece 1 at 27-MAY-14
channel ORA_DISK_1: finished piece 1 at 27-MAY-14
piece handle=/u04/archive/PRODDB01/backupset/2014_05_27/o1_mf_annnn_TAG20140527T115156_9r8r1wt7_.bkp tag=TAG20140527T115156 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_3_9r3qksmy_.arc RECID=3 STAMP=848499170
archived log file name=/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_4_9r3qp6mt_.arc RECID=4 STAMP=848499311

A check in the V$ARCHIVED_LOG reveals what Oracle has done:

SQL>  select name,sequence#,archived,applied,deleted,status,IS_RECOVERY_DEST_FILE,compressed from v$archived_log;

NAME                                                                      SEQUENCE# ARC APPLIED   DEL S IS_ COM
------------------------------------------------------------------------ ---------- ---------- --- --------- --- - --- ---
                                                                                   1 YES NO        YES D YES NO
                                                                                   2 YES NO        YES D YES NO
                                                                                   3 YES NO        YES D YES NO
                                                                                   4 YES NO        YES D YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_5_9r3r0fo6_.arc                5 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_6_9r3rc6pf_.arc                6 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_7_9r3rmkpr_.arc                7 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_8_9r3s0vs3_.arc                8 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_9_9r3sbst4_.arc                9 YES NO        NO  A YES NO
/u04/archive/PRODDB01/archivelog/2014_05_25/o1_mf_1_10_9r3so0v9_.arc              10 YES NO        NO  A YES NO

How to set up a standard listener on port 1521

Recently I have seen cases where customers have defined multiple listeners in the $TNS_ADMIN/listener.ora file, but nontheless have started only the default listener called "LISTENER".

Unless you actually start the separate listeners, they will not do you any good. Each listener must be configured to listen to different ports and then started explicitly, as follows:

If desirable to run separate listeners, configure your listener.ora as follows:

SID_LIST_LISTENER_PRODDB01 =
  (SID_LIST =
     (SID_DESC =
       (SID_NAME = PRODDB01)
       (ORACLE_HOME = /u01/oracle/product/11202)
   )
)
 
LISTENER_PRODDB01 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT =1521))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
  )
 
SID_LIST_LISTENER_PRODDB02 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PRODDB02)
      (ORACLE_HOME = /u01/oracle/product/11202)
     )
  )
 
LISTENER_PRODDB02 =
  (DESCRIPTION_LIST =
     (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT=1526))
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
     )
 )
 

Then start each listener:

LSNRCTL> set current_listener LISTENER_PRODDB01
Current Listener is LISTENER_PRODDB01
LSNRCTL> start
LSNRCTL> set current_listener LISTENER_PRODDB02
Current Listener is LISTENER_PRODDB01
LSNRCTL> start

After having started all LISTENERS, check that they are running (on unix):

ps -ef | grep LISTEN|grep -v grep

 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB01 -inherit
 oracle 7864350 1 0 15:41:47 - 0:12 /u01/oracle/product/11203/bin/tnslsnr LISTENER_PRODDB02 -inherit

If you only specify one - 1 - listener per server, you can set it up to listen for incoming Connections for multiple databases, as specified in the SID_LIST:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
      (SID_NAME =PRODDB01)
      (ORACLE_HOME = /u01/oracle/product/11202)
    )
   (SID_DESC =
     (SID_NAME =PRODDB02)
     (ORACLE_HOME = /u01/oracle/product/11202)
   )
)

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
 )

I prefer using a $TNS_ADMIN/listener.ora file consequently. It makes it easier to configure the listener later.
I recommomed the following lines to be added to 11gR2 listeners:

DIAG_ADR_ENABLED=on             <-- Use the ADR as base for all future logging and tracing of the listener
ADR_BASE_LISTENER = /u01/oracle <-- The value of Your $ORACLE_BASE variable goes here
LOGGING_LISTENER=off            <-- Turn logging on only if needed
TRACE_LEVEL_LISTENER=off        <-- Turn tracing on only if needed