ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

This problem is caused due to rman  the backup location is  NFS file system if the NFS file system has the wrong Permissions or Stale then the error occurs while Restoring to Resolve it   .. we need to umount the mount point and mount with the correct parameters

Check whether the   RMAN is  configured  to store the backup's in the O/S file system path 

In the below example /shared is the nfs file system 

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/shared/oracle/%U';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/shared/oracle/%F';

Once this parameter is configured and your default device is disk then all the backups will be place in the particular path by rman

when we run the recovery script to Restore the backup then the backup piece is not readable due to the mount point and the below error Message will be displayed 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/c-728861041-20120710-02

channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /shared/oracle/c-728861041-20120710-02

ORA-19505: failed to identify file "/shared/oracle/c-728861041-20120710-02"

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Additional information: 3

Additional information: 9

failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/14nfonv7_1_1

channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /shared/oracle/14nfonv7_1_1

ORA-19505: failed to identify file "/shared/oracle/14nfonv7_1_1"

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Additional information: 3

Additional information: 9

Here the file which is inside the /shared is not readable by the destination machine so we need to check the mount parameters for the /shared

192.168.5.90:/shared on /shared type nfs (rw,rsize=8192,wsize=8192,timeo=14,intr,addr=192.168.5.90)

 stop all the Process which is running on the /shared and then 

umount /shared

Now  we have to mount the /shared by giving the necessary parameters

mount -o rw,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,tcp 192.168.5.90:/shared /shared

If the server is the NFS file system then you have to specify which NFS version and for the fake nfs we need not specify the version 

execute the command in both of the servers

Starting restore at 11-JUL-12

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=2828 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: copied control file copy

input file name=/shared/oracle/control01.dbf

output file name=/u06/app/oracle/oradata/datafile/control01.ctl

output file name=/u06/app/oracle/oradata/datafile/control02.ctl

Finished restore at 11-JUL-12

Shut Down or Reboot a Solaris System

Shut Down or Reboot a Solaris System

Solaris is usually used as a server operating system. Because of this, you want to make sure that you shut the system down as gracefully as possible to ensure there isn’t any data loss.
For every application that is installed on your server, you should make sure that you have the correct scripts in /etc/rc(x).d to gracefully shut down the service.

ShutdownYou have more than one command option that you can use. The best command is this, executed as root:

shutdown -y -i5 -g0

This will immediately shut the system down. You can also use the older command that still works:

sync;sync;init 5

You can even use:

poweroff

Reboot If you are trying to reboot the system as opposed to turning it off, you could use:

shutdown -y -i6 -g0

Or:

sync;sync;init 6

Or even:

reboot

How do I restart sshd on my Linux/Unix system

After you have changed your configuration on your remote Unix/linux server you must restart your ssh service. The easiest way to do this is to simply restart your Unix machine. This is not always possible however.
To restart sshd without restarting your whole system, enter the following command as root

RedHat and Fedora Core Linux

/sbin/service sshd restart

Suse linux

/etc/rc.d/sshd restart

Debian/Ubuntu

/etc/init.d/sshd restart

Solaris 9 and below

/etc/init.d/sshd stop
/etc/init.d/sshd start

Solaris 10

svcadm disable ssh
svcadm enable ssh

AIX

stopsrc -s sshd
startsrc -s sshd

HP-UX

/sbin/init.d/secsh stop
/sbin/init.d/secsh start

Reference: http://www.starnet.com/xwin32kb/restart_sshd

How to check expdp progress

How to check expdp progress

V$SESSION_LONGOPS :This view helps determine Data Pump export progress indicator through the MESSAGE column.

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,
t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT';

Source: http://sivasiva2kdba.blogspot.com/2010/01/how-to-check-expdp-progress.html

Auditing in Oracle

Auditing in Oracle

The auditing mechanism for Oracle is extremely flexible. Oracle stores information that is relevant to auditing in its data dictionary.

Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated (in the case or a session record) and generates the record in a table owned by the SYS user called AUD$. This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

init parameters
Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.

From Oracle 11g, auditing is enabled for some system level privileges.

SQL> show parameter audit

NAME	TYPE	VALUE
----------------------	------------	-------------
audit_file_dest	string	?/rdbms/audit
audit_sys_operations	boolean	FALSE
audit_syslog_level	string	NONE
audit_trail	string	DB
transaction_auditing	boolean	TRUE

AUDIT_TRAIL can have the following values.
AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

The following list provides a description of each value:

• NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.
• DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
• DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
• XML-> Auditing is enabled, with all audit records stored as XML format OS files.
• XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
• OS -> Auditing is enabled, with all audit records directed to the operating system's file specified by AUDIT_FILE_DEST.

Note: In Oracle 10g Release 1, DB_EXTENDED was used in place of "DB,EXTENDED". The XML options were brought in Oracle 10g Release 2.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the OS, XML and XML_EXTENDED options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

Run the $ORACLE_HOME/rdbms/admin/cataudit.sql script while connected as SYS (no need to run this, if you ran catalog.sql at the time of database creation).

Start Auditing
Syntax of audit command:
audit {statement_option|privilege_option} [by user] [by {session|access}] [whenever {successful|not successful}]

Only the statement_option or privilege_option part is mandatory. The other clauses are optional and enabling them allows audit be more specific.

There are three levels that can be audited:

Statement level
Auditing will be done at statement level.
Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.
SQL> audit table by scott;

Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;

Object level
Auditing will be done at object level.
These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;

Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;

Privilege level
Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create tablespace, alter tablespace by all;

Specify ALL PRIVILEGES to audit all system privileges.

Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;

Audit options
BY SESSION
Specify BY SESSION if you want Oracle to write a single record for all SQL statements of the same type issued and operations of the same type executed on the same schema objects in the same session.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.
SQL> audit create, alter, drop on currency by xe by session;
SQL> audit alter materialized view by session;

BY ACCESS
Specify BY ACCESS if you want Oracle database to write one record for each audited statement and operation.

If you specify statement options or system privileges that audit data definition language (DDL) statements, then the database automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.
SQL> audit update on health by access;
SQL> audit alter sequence by tester by access;

WHENEVER [NOT] SUCCESSFUL
Specify WHENEVER SUCCESSFUL to audit only SQL statements and operations that succeed.
Specify WHENEVER NOT SUCCESSFUL to audit only SQL statements and operations that fail or result in errors.

If you omit this clause, then Oracle Database performs the audit regardless of success or failure.
SQL> audit insert, update, delete on hr.emp by hr by session whenever not successful;
SQL> audit materialized view by pingme by access whenever successful;

Examples
Auditing for every SQL statement related to roles (create, alter, drop or set a role).
SQL> AUDIT ROLE;

Auditing for every statement that reads files from database directory
SQL> AUDIT READ ON DIRECTORY ext_dir;

Auditing for every statement that performs any operation on the sequence
SQL> AUDIT ALL ON hr.emp_seq;

View Audit Trail
The audit trail is stored in the base table SYS.AUD$.
It's contents can be viewed in the following views:
· DBA_AUDIT_TRAIL
· DBA_OBJ_AUDIT_OPTS
· DBA_PRIV_AUDIT_OPTS
· DBA_STMT_AUDIT_OPTS
· DBA_AUDIT_EXISTS
· DBA_AUDIT_OBJECT
· DBA_AUDIT_SESSION
· DBA_AUDIT_STATEMENT
· AUDIT_ACTIONS
· DBA_AUDIT_POLICIES
· DBA_AUDIT_POLICY_COLUMNS
· DBA_COMMON_AUDIT_TRAIL
· DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
· DBA_REPAUDIT_ATTRIBUTE
· DBA_REPAUDIT_COLUMN

The audit trail contains lots of data, but the following are most likely to be of interest:
Username - Oracle Username.
Terminal - Machine that the user performed the action from.
Timestamp - When the action occurred.
Object Owner - The owner of the object that was interacted with.
Object Name - name of the object that was interacted with.
Action Name - The action that occurred against the object (INSERT, UPDATE, DELETE, SELECT, EXECUTE)

Fine Grained Auditing (FGA), introduced in Oracle9i, allowed recording of row-level changes along with SCN numbers to reconstruct the old data, but they work for select statements only, not for DML such as update, insert, and delete.

From Oracle 10g, FGA supports DML statements in addition to selects.

Several fields have been added to both the standard and fine-grained audit trails:

• EXTENDED_TIMESTAMP - A more precise value than the existing TIMESTAMP column.
• PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
• GLOBAL_UID - Global Universal Identifier for an enterprise user.
• INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
• OS_PROCESS - Operating system process id for the oracle process.
• TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
• SCN - System change number of the query. This column can be used in flashback queries.
• SQL_BIND - The values of any bind variables if any.
• SQL_TEXT - The SQL statement that initiated the audit action.
  The SQL_BIND and SQL_TEXT columns are only populated when the AUDIT_TRAIL=DB_EXTENDED or AUDIT_TRAIL=XML_EXTENDED initialization parameter is set.

Maintenance
The audit trail must be deleted/archived on a regular basis to prevent the SYS.AUD$ table growing to an unacceptable size.

Only users who have been granted specific access to SYS.AUD$ can access the table to select, alter or delete from it. This is usually just the user SYS or any user who has permissions. There are two specific roles that allow access to SYS.AUD$ for select and delete, these are DELETE_CATALOG_ROLE and SELECT_CATALOG_ROLE. These roles should not be granted to general users.

Auditing modifications of the data in the audit trail itself can be achieved as follows
SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

To delete all audit records from the audit trail:
SQL> DELETE FROM sys.aud$;

From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT package.

Disabling Auditing
The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY scott, hr;
SQL> NOAUDIT DELETE ON emp;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;

Source=http://satya-dba.blogspot.com/2009/05/auditing-in-oracle.html

Install Solaris 10 Update 9 - ZFS only

Solaris 10 Update 9 was the first edition of solaris after Oracle took over Sun. There hasn't changed much in the install procedure, but I hadn't done a full ZFS install yet, so I decided to create a new howto. This page will have a follow up for post install configuration.
This installation was done in a VMware vSphere VM, with a vmxnet3 network adapter. That adapter is not natively supported by VMware, so the VMware Tools will have to be installed before the network card will be available.

Note: If you're looking for a installation with the root filesystem being UFS, take a look at Install Solaris 10 Update 8.

Install

For this installation, this media was used:

• Used Media: sol-10-u9-ga-x86-dvd.iso

During the installation the following options were chosen to make sure all filesystems use ZFS and all software would be available:

• Install Option 4: Solaris Interactive Text (Console session) (Option 3 or 4 is required to install a ZFS root file system)

• Keyboard Layout: US-English
• Language: English

• Hostname: solarixbox
• TimeZone: Europe
• Countries and Regions: Netherlands

• Root Password: RootPass
• Remote Services Enabled: No

• Installation Method: Standard
• Eject a CD Automatically: No
• Auto Reboot: Yes
• Accept License: Yes
• Geographic Regions: Western Europe
• System Locale: Posix C (C)

• No Additional Products

• Filesystem Type: ZFS

• Software Selection: Entire Distribution

• ZFS Pool Name: rpool (default)
• ZFS Root Dataset Name: s10x_u9wos_14a (default)
• ZFS Pool Size (in MB): 45019 (default - max size)
• Size of Swap Area (in MB): 0 (will be created on different disk)
• Size of Dump Area (in MB): 2560 (default)
• Keep / and /var combined: yes

Check the profile summary:

Install VMware Tools

Install VMware Tools according to the manual which comes down to:

• Click VM in the virtual machine menu, then click Guest → Install/Upgrade VMware Tools and click OK.
• Then use these commands to install the tools:

# cp /cdrom/vmwaretools/vmware-solaris-tools.tar.gz /tmp
# cd /tmp
# gunzip vmware-solaris-tools.tar.gz
# tar xvf vmware-solaris-tools.tar
# cd vmware-tools-distrib
# ./vmware-install.pl

Follow the prompts and reboot
Afterwards you can check the installation like this, depending on the VMware tools version you'll see this:

# /etc/init.d/vmware-tools status
vmware-guestd is running
vmware-memctld is running

or this:

# /etc/init.d/vmware-tools status
vmtoolsd is running

How to enable oracle flashback in Oracle 11g

How to enable oracle flashback in Oracle 11g

1. To see flashback ON or OFF on running database
   
 SQL> select flashback_on from v$database;

    FLASHBACK_ON
    ------------------
    NO 

2. To get flashback ON

SQL > shut immediate;
SQL > startup mount;
SQL > alter database flashback ON;
SQL > alter database open;

3. To see flashback ON

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES


Enjoy :)

How to see login history of a oracle user

select c.username,a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.module, a.machine, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users c
where a.SQL_ID = b.SQL_ID(+)
and a.user_id=c.user_id
and c.username='&username'
order by a.SQL_EXEC_START asc;

ORA-01591: lock held by in-doubt distributed transaction

ORA-01591: lock held by in-doubt distributed transaction

 SQL> select local_tran_id from dba_2pc_pending;
LOCAL_TRAN_ID
 ------------------------------------------------------------------
7.55.64129

 SQL> rollback force '7.55.64129';
 Rollback complete.

 SQL> exec dbms_transaction.purge_lost_db_entry('7.55.64129'); 
 PL/SQL procedure successfully completed.

 SQL> select local_tran_id from dba_2pc_pending;
 no rows selected  

Enjoy :)

How to list and fix failure in RMAN

Step to list any failure in Oracle RMAN

          $ rman target /
          RMAN > LIST FAILURE;

Step to fix RMAN failure

          $ rman target /
          RMAN > ADVISE FAILURE;
          RMAN > REPAIR FAILURE PREVIEW;
          RMAN > REPAIR FAILURE;
         

After fix failure not close automatically, then we need to close this failure manually
          RMAN > change failure <failure_id> closed;


Enjoy :)

How to list Server S/N and P/N on Solaris 10

How to list Server S/N and P/N on Solaris 10

pfexec ipmitool fru
smbios -t SMB_TYPE_SYSTEM
You cat try prtfru (only for SPARC servers)

How to list Serve S/N and P/N in linux

How to list Serve S/N and P/N in linux

$dmidecode -t system