Thursday, December 11, 2014

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

Friday, December 5, 2014

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.


Thursday, December 4, 2014

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

Wednesday, December 3, 2014

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.