Oracle R12/11i EBS Password change

Log on to Server

Log in to applmgr or owner of apps

Run apps environment variable

[oracle@oracle12 appl]$. ./APPSPROD_oracle12.env

Stop the application

Change Oracle APPS user password

Ex:-

$FNDCPASS apps/apps 0 Y system/system SYSTEM APPLSYS WELCOME

New apps  password will be set to WELCOME

Note:- before change the apps password you should know the system password,
You can check system password whether is correct using adadmin tool

Note:-
if you don’t know the system password please
use the below mention step to reset the system user password


Change all ORACLE R12 Schema passwords: (Ex :- AP,AR,GL,)

Ex:-

$FNDCPASS apps/apps 0 Y system/system ALLORACLE WELCOME

New  AllOracle password will be set to WELCOME   (EX:- AP,AR.GL,CE )

Note:- After change any password please run the autoconfig for application Tier

To change an Oracle Applications user's password


$FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME


Change Oracle system user password

Ex:-
su oracle
sqlplus / as sysdba

SQL>alter user system identified by manager

New password will be set to manager

Change root user password on Linux

Ex:-
[root@oracle12 ~]# passwd
Changing password for user root.
New UNIX password:

Change Oracle user password on Linux

Ex:-
[root@oracle12 ~]# passwd oracle
Changing password for user oracle.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

Source: Internet/Internal Research 

SSH password less connectivity

You want to use Linux and OpenSSH to automate your tasks. Therefore you need an automatic login from host A / user a to Host B / user b. You don't want to enter any passwords, because you want to call ssh from a within a shell script.

How to do it

First log in on A as user a and generate a pair of authentication keys. Do not enter a 

passphrase:

a@A:~> ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/a/.ssh/id_rsa):

Created directory '/home/a/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/a/.ssh/id_rsa.

Your public key has been saved in /home/a/.ssh/id_rsa.pub.

The key fingerprint is:

3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 a@A

Now use ssh to create a directory ~/.ssh as user b on B. (The directory may already exist, which is fine):

a@A:~> ssh b@B mkdir -p .ssh

b@B's password:

Finally append a's new public key to b@B:.ssh/authorized_keys and enter b's password one last time:

a@A:~> cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'

b@B's password:

From now on you can log into B as b from A as a without password:

a@A:~> ssh b@B

A note from one of our readers: Depending on your version of SSH you might also have to do the following changes:

·         Put the public key in .ssh/authorized_keys2

·         Change the permissions of .ssh to 700

·         Change the permissions of .ssh/authorized_keys2 to 640

Source: Internet

Restoring the SPFILE - with and without AUTOBACKUP

Let us look at some backup and recovery scenarios pertaining to the SPFIILE. The SPFILE is a small but very important file and if we lose the spfile, we cannot start the database even if all the other database files are present and intact.

So in my opinion, it is quite an important thing to consider in our disaster recovery strategy.

Remember best practice is to turn the auto backup of the control file to ON (it is OFF by default).

SPFILE is automatically backed up along with the database control file when any of the following events occur and when the control file auto backup has been enabled in RMAN. .

RMAN> show controlfile autobackup;

RMAN configuration parameters for database with db_unique_name GAVIN are:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

When does the SPFILE gat backed up with AUTOBACKUP now tuned on?

• After every BACKUP or CREATE CATALOG command
• After every BACKUP command contained in a RUN block
• After every structural change to the database occurs like adding a new tablespace, altering the state of a tablespace or datafile (for example, bringing it online), adding a new online redo log, renaming a file

Where does the SPFILE autobackup reside?

RMAN> show controlfile autobackup format;

RMAN configuration parameters for database with db_unique_name GAVIN are:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

This is the default setting for CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK

By default, RMAN will send the autobackup to the flash recovery area (if used).

Let us now remove the default keyword

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

new RMAN configuration parameters:

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

new RMAN configuration parameters are successfully stored

In this case the SPFILE (and control file) autobackup is located anywhere you specify, but default location will be %ORACLE_HOME%\Database on Windows and $ORACLE_HOME/dbs on UNIX.

RMAN> list backup of spfile;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

252     Full    9.73M      DISK        00:00:00     13-JUN-13

        BP Key: 267   Status: AVAILABLE  Compressed: NO  Tag: TAG20130613T144508

        Piece Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/c-2968723077-20130613-00

Let us now specify an actual location on disk instead of just the %F,

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';

old RMAN configuration parameters:

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

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';

new RMAN configuration parameters are successfully stored

RMAN> list backup of spfile;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

256     Full    9.73M      DISK        00:00:01     13-JUN-13

        BP Key: 271   Status: AVAILABLE  Compressed: NO  Tag: TAG20130613T155004

        Piece Name: /u01/backup/c-2968723077-20130613-01

Note the SPFILE autobackup is now located at /u01/backup and we can see the format of the backup file on disk is no longer OMF.

The DBID (2968723077) and the timestamp (20130613) is now contained in the backup file name c-2968723077-20130613-01

Let us now revert the autobackup back to the FRA.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

old RMAN configuration parameters:

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

RMAN configuration parameters are successfully reset to default value

RMAN> show CONTROLFILE AUTOBACKUP FORMAT;

RMAN configuration parameters for database with db_unique_name GAVIN are:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

RMAN> list backup of spfile;

using target database control file instead of recovery catalog

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

266     Full    9.73M      DISK        00:00:01     13-JUN-13

        BP Key: 281   Status: AVAILABLE  Compressed: NO  Tag: TAG20130613T162411

        Piece Name: /u01/app/oracle/fast_recovery_area/GAVIN/autobackup/2013_06_13/o1_mf_s_818007851_8vlsdd3n_.bkp

Autobackup is now back to OMF.

Recovery scenarios involving loss of SPFILE

Case 1) Autobackup in Flash (or now called Fast) Recovery Area

The SPFILE has accidently been deleted and now the database is not starting up after a shutdown has been executed.

FRA has been configured.

If FRA has been configured, the backup of the SPFILE is located in the autobackup sub-directory.

For example:

/u01/app/oracle/fast_recovery_area/GAVIN/autobackup/2013_06_10/ o1_mf_s_818007851_8vlsdd3n_.bkp

Note that it is stored in OMF format in this example. The ‘s’ in the string identifies the OMF as a backup related to the SPFILE

To recover from loss of SPFILE if you are NOT using an RMAN Catalog, we need to do two things first:

1) Set the DBID
2) Issue the STARTUP NOMOUNT FORCE command from an RMAN prompt (note – not SQL*PLUS)

RMAN> SET DBID=2968723077;

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initgavin.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes

Variable Size                104859368 bytes

Database Buffers              46137344 bytes

Redo Buffers                   5439488 bytes

This is a typical error we will face when either restoring the SPFILE or control file from an autobackup.

RMAN>  restore controlfile from autobackup;

Starting restore at 13-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130612

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130611

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130610

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130609

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130608

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130607

channel ORA_DISK_1: no AUTOBACKUP in 7 days found

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 06/13/2013 17:15:52

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

The reason in this case is that since the spfile is missing and we have mounted the instance using a dummy spfile, the database needs to know where to look to find the autobackup of the spfile .

So now we include the db_file_recovery_dest and db_name parameters in the RESTORE SPFILE command.

RMAN> restore spfile from autobackup db_recovery_file_dest='/u01/backup/fast_recovery_area' db_name='GAVIN';

Starting restore at 13-JUN-13

using channel ORA_DISK_1

recovery area destination: /u01/backup/fast_recovery_area

database name (or database unique name) used for search: GAVIN

channel ORA_DISK_1: AUTOBACKUP /u01/backup/fast_recovery_area/GAVIN/autobackup/2013_06_10/o1_mf_s_818007851_8vlsdd3n_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/fast_recovery_area/GAVIN/autobackup/2013_06_10/o1_mf_s_818007851_8vlsdd3n_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 13-JUN-13

Case 2) Autobackup in non-FRA location – non OMF

RMAN> startup nomount force

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initgavin.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2226456 bytes

Variable Size                104859368 bytes

Database Buffers              46137344 bytes

Redo Buffers                   5439488 bytes

RMAN> set DBID=2968723077

executing command: SET DBID

RMAN>  run {

2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';

3> restore spfile from autobackup;

4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 13-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613

channel ORA_DISK_1: AUTOBACKUP found: '/u01/backup/c-2968723077-20130613-01

channel ORA_DISK_1: restoring spfile from AUTOBACKUP '/u01/backup/c-2968723077-20130613-01

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 13-JUN-13

Case 3) Total Disaster Recovery ( restore by specifying the backup file name)

In this scenario, the entire database server has crashed and we have lost the entire database files including the SPFILE.

A new server has been provisioned and all the latest backup files have been restored from tape to a location on disk /u01/backup.

In this case the backup files are OMF and we have been able to identify the SPFILE backup file from the ‘s’ keyword in the backup file name

RMAN> restore spfile from '/u01/backup/o1_mf_s_818007851_8vlsdd3n_.bkp';

Starting restore at 13-JUN-13

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/o1_mf_s_818007851_8vlsdd3n_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 13-JUN-13

Case 4) Total Disaster Recovery ( restore by specifying restore from AUTOBACKUP)

In this case, the scenario is the same as the above.

But what happens if we want to use the AUTOBACKUP command to restore the spfile because many backup files have been restored and we are not sure which backup file contains the SPFILE backup.

But what happens in this case when we try to restore the SPFILE from the location where the backup has been restored.

RMAN>  run {

2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';

3> restore spfile from autobackup;

4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 17-JUN-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=171 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130617

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130616

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130615

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130614

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130613

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130612

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130611

channel ORA_DISK_1: no AUTOBACKUP in 7 days found

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 06/17/2013 15:29:58

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

So to work around this, we tried to fool RMAN by creating the directory structure when using a FRA.

We create the directory structure GAVIN/autobackup/2013_06_17 under the to level location /u01/backup and copy the backup pieces to this location.

-bash-3.2$ cd /u01/backup
-bash-3.2$ mkdir -p GAVIN/autobackup/2013_06_17
-bash-3.2$ mv /u01/backup/o1* /u01/backup/GAVIN/autobackup/2013_06_17

Now we are able to restore the SPFILE from autobackup!

RMAN>  run {

2> restore spfile from autobackup db_recovery_file_dest='/u01/backup/' db_name='GAVIN';

3> }

Starting restore at 17-JUN-13

using channel ORA_DISK_1

recovery area destination: /u01/backup/

database name (or database unique name) used for search: GAVIN

channel ORA_DISK_1: AUTOBACKUP /u01/backup/GAVIN/autobackup/2013_06_17/o1_mf_s_818349778_8vx79lo6_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130617

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/GAVIN/autobackup/2013_06_17/o1_mf_s_818349778_8vx79lo6_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-JUN-13


Source: Internet