Step by Step RMAN Restoration in R12

1. Extract tech_st

tar -xvf db_tech_st.tar

2. Run postclone

perl adcfgclone.pl dbTechStack

3. Shutdown the Database

SQL> shutdown immediate;

4. modified init.<SID>.ora file same as prod

5. Startup in no mount state 

SQL> startup nomount pfile='init.<SID>.ora';

6. Connect through target DB and restore controlfile from backup

rman> restore controlfile from '/ebiz1/prodback/CONT_pnm_1';

7. Mount the DB

SQL> alter database mount;

8. Catalog the backup pieces

rman> catalog backuppiece '/ebiz1/prodback/DATA_ppja4_10_1';
OR
rman> CATALOG START WITH '/ebiz1/prodback';

9. Crosscheck backup (Sync physical backup with RMAN repository

rman> CROSSCHECK BACKUP;

10. Restore Database

run{
     set newname for datafile '/PRODB01/oraprod/db/apps_st/data/a_ref02.dbf' to '/ebiz2/DEVINST/db/apps_st/data/a_ref02.dbf';
     restore database;
   }

To get the script, use below query

SQL> select 'set newname for datafile '|| file# ||' to '''||name||''''||';' from v$datafile;

11. Crosscheck the Archivelogs

rman> crosscheck archivelog all;

12. Restore Archivelogs

rman> restore archivelog from sequence <Sequence_no>

Use below to find sequence no

rman>list backup of archivelog all;

13. Get the control file to a trace

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/tmp/cf_new.trc';

14. Open created trace file,edit as follows and save as .sql.

 =>remove the content in the file upto STARTUP NOMOUNT
 =>instead of having 'REUSE' make it 'SET' 
 =>Change the name of the DB 
 =>replace 'NORESETLOGS'  with 'RESETLOGS'
 =>remove 'FORCE LOGGING' 
 =>replace 'ARCHIVELOG' with 'NOARCHIVELOG'
 =>remove the cotent after the 'CHARACTER SET 'US7ASCII'
 =>change the datafile paths to the new 

15. Shutdown the DB

SQL> Shutdown immediate

16. Rename existing controlfile

17. Modified init.<SID>.ora  (same as target DB)

18. Execute modified trace file

sql>@<sql_file>

19. Recover Database

SQL> recover database using backup controlfile until cancel;

20. Open the DB with resetlogs option

SQL> alter database open resetlogs;

21. Add temp file

SQL> alter tablespace temp1 add tempfile '/velocity/VLODAIL/db/apps_st/data/temp01.dbf' SIZE 2000M autoextend on next 256M maxsize 10240M;

22. Run post clone for DB tier with already created context file

perl adcfgclone.pl dbconfig /ebiz1/PROINT/db/tech_st/11.1.0/appsutil/PROINT_ct-eagorasvr5.xml

23. Then Finally run postclone for Apps Tier

perl adcfgclone.pl appsTier

Enable Audit Trail and Resolve Fatal error Issue in R12

Enable Audit Trail in R12

Responsibility Needed - System Administrator

1. Define audit Group

Navigation - Security->Audit Trail->Groups

Enter Application Name and Tables What need to be Audit and Save the change

Note: When you first create the audit group the group status will be 'Enable Requested' this will automatically be changed to 'Enabled' once the AuditTrail Update Tables concurrent request is run (Step 5)


2. Define Which columns to be audited

Navigation - Security->Audit Trail->Tables

3. Enable auditing for schema owners

Navigation - Security->Audit Trail->Install

4. Enable ‘AuditTrail Activate’ profile option

5. Submit the 'AuditTrail Update Tables' concurrent request

Solution for AuditTrail Update Tables program error on R12

   While running AuditTrail Update Tables concurrent request It's completed with Error.

  To fix the issue Check if you have enabled auditing for APPLSYS,APPS and the application schema owner you want to audit, if not enable the auditing for the schema you wish to audit.

   Then change the group statue for the Audit Group AP_SYSTEM_PARAMETERS_ALL to disable – purge table and run the concurrent request 'AuditTrail Update Tables'  again.

Enable Audit trail for Oracle DB

Setting parameter 

1. AUDIT_TRAIL = { none | db | db,extended | xml | xml,extended | os }

none or false    - Auditing is disabled.
db or true        - Auditing is enabled, with all audit records stored in the                           database audit trial (SYS.AUD$).
db,extended    - 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  - 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 audit trail.

2. AUDIT_SYS_OPERATIONS - Static parameter enables audit for SYSDBA or SYSOPER privileges including the SYS user.

3. AUDIT_FILE_DEST  -  Parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used.

Ex : ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;   

Finally Restart the DB to apply the changes

Enable Audit for Specific user

$ sqlplus / as sysdba
$ sql> AUDIT ALL BY <user_name> BY ACCESS;
$ sql> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE,ALTER TABLE BY <user_name> BY ACCESS;
$ sql> AUDIT EXECUTE PROCEDURE BY <user_name> BY ACCESS;

DB Audit Related Views

DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

Create Trusted Connection Between two servers on Linux

Connection From server1 to server2

1. Create dsa key on server1 and take default settings, do not enter a passphrase.

[root@server1 /]# ssh-keygen -t dsa

2. Copy key from server1 to server2. 

[root@server1 /]# scp /root/.ssh/id_dsa.pub root@server2:/root/id_dsa.pub 

3. ssh to server 2:

[root@server1 /]# ssh root@server2 

4. Change to the /root/.ssh or if it does not exist create .ssh directory 

[root@server2 /]# mkdir /root/.ssh -p

5. create authorzed_keys file 

[root@server2 /]# cat /root/id_dsa.pub >>/root/.ssh/authorized_keys   

6. Change the permissions so that the owner has read,write and the        group and other has only read

[root@server2 /]# chmod 644  /root/.ssh/authorized_keys

Now you can ssh from server1 to server2 with out password or passphrase.

R12-Custom Top Creation

1)

Make the directory structure for your custom application files.

cd $APPL_TOP
mkdir xxtest
mkdir xxtest/12.0.0
mkdir xxtest/12.0.0/admin
mkdir xxtest/12.0.0/admin/sql
mkdir xxtest/12.0.0/admin/odf
mkdir xxtest/12.0.0/sql
mkdir xxtest/12.0.0/bin
mkdir xxtest/12.0.0/reports
mkdir xxtest/12.0.0/reports/US
mkdir xxtest/12.0.0/forms
mkdir xxtest/12.0.0/forms/US
mkdir xxtest/12.0.0/lib
mkdir xxtest/12.0.0/out
mkdir xxtest/12.0.0/log

2)

Add the custom module into the application context file

vi $CONTEXT_FILE
<au_top oa_enabled="FALSE" oa_type="PROD_TOP" oa_var="s_autop">/u01/TEST/apps/apps_st/appl/au/12.0.0</au_top>
<xxtest_top oa_enabled="FALSE" oa_type="PROD_TOP" oa_var="s_xxtesttop">/u01/TEST/apps/apps_st/appl/xxtest/12.0.0</xxtest_top>

3)
Stop the application and run autoconfig

cd $ADMIN_SCRIPTS_HOME
sh adstpall.sh apps/apps
sh adautocfg.sh
sh adstrtal.sh apps/apps

Log out from the session and login and verify whether custom top is viewable

$echo $XXTEST_TOP
/u01/TEST/apps/apps_st/appl/xxtest/12.0.0

4)

Create Tablespace for Custom top

SQL>create tablespace XXTEST
datafile '/u01/TEST/db/apps_st/data/>01.dbf' size 2048M
autoextend on next 256M maxsize 20480M;

5)

Create User for Custom top

create user XXTEST
identified by XXTEST
default tablespace XXTEST
temporary tablespace TEMP
quota unlimited on XXTEST;

User created.

SQL> grant connect,resource to XXTEST;

6)