Monthly Archives: October 2013

Resolve huge archive gap between PRIMARY and STANDBY

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database.

When the logs are missing on standby difference is huge (say more than 500 logs), you have to rebuild the standby database from scratch.

With out rebuild standby database, As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL… FROM SCN can be used to refresh the standby database.

Please use below query to find out archive gap on Standby:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
 Thread        Last Sequence Received      Last Sequence Applied      Difference
———-              ———————-                  ———————             ———-  
1                               8254                                      7954                          300
 

Find the SCN on the PRIMARY:

SQL> select current_scn from v$database;
 
CURRENT_SCN
———–  
242671761

Find the SCN on the STANDBY:

 
SQL> select current_scn from v$database;
CURRENT_SCN
———–  
223771173

Clearly you can see there is difference

Stop and shutdown the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

Shutdown the standby database

SQL> shut immediate

On the primary, take an incremental backup from the SCN number where the standby current value 223771173:

 RMAN> run { allocate channel c1 type disk format ‘/backup/%U.bkp’;
backup incremental from scn 223771173 database;
 }

On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as ‘/backup/for_standby.ctl’;

Database altered.

Copy the standby controlfile to STANDBY and bring up the standby instance in nomount status with standby controlfile:

SQL> startup nomount

SQL> alter database mount standby database;

Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands:

$ rman target=/

RMAN> catalog start with ‘/backup’;

PERFORM RECOVER:

RMAN> recover database;

Start managed recovery process:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Check the SCN’s in primary and standby it should be close to each other.

If archive gap difference is minimal or less, Please check this https://oracledbamasters.wordpress.com/2013/10/22/resolve-archive-gap-primary-standby/

Advertisements

Resolve archive gap between PRIMARY and STANDBY

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database.

When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the logfiles in standby to resolve the gap.

Please use below query to find out archive gap on Standby:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
 
Thread  Last Sequence Received   Last Sequence Applied     Difference
———-  ———————-                          ———————        ———-  
1                   9545                                             9535                   10
 
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED=’YES’;
 MAX(SEQUENCE#)
————–
9535

COPY ARCHIVELOG FILE TO STANDBY FROM PRIMARY:

$ scp log_file_n.arc oracle@standby:/log_location/log_file_n.arc

REGISTER LOGFILE AT STANDBY:

SQL> alter database register logfile ‘/log_location/log_file_n.arc’; logfile registered

Repeat the same process for all the log files which are missing at standby.

If archive gap difference is huge(say more than 500 logs) , Please check this https://oracledbamasters.wordpress.com/2013/10/22/resolve-huge-archive-gap-primary-standby/

New Feature – AFPASSWD utility in EBS 12.1.2

Oracle has introduced a new utility to change the Oracle E-Business Suite passwords in R12.1.2, AFPASSWD is an enhanced version of FNDCPASS utility.

1) AFPASSWD only prompts for passwords required for the current operation. allowing separation of duties between applications administrators and database administrators.

2) AFPASSWD can be run from the database tier as well as the application tier. In contrast, FNDCPASS can only be run from the application tier.

3) This also improves interoperability with Oracle Database Vault. In contrast, the FNDCPASS utility currently requires specification of the APPS and the SYSTEM usernames and corresponding passwords, preventing separation of duties between applications administrators and database administrators.

3) When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

SAMPLE OUTPUT: Changing SYSADMIN Password  

$AFPASSWD -f SYSADMIN  
Enter the ORACLE password of Application Object Library ‘APPSUSER’:  
Connected successfully to APPS.  Working…  
Enter new password for user [SYSADMIN]:  
Verify new password for user [SYSADMIN]:  
Password is changed successfully for user SYSADMIN.  
Password is changed successfully for user SYSADMIN.  
AFPASSWD completed successfully.
 

SAMPLE OUTPUT: Changing APPLSYS Password

$AFPASSWD -c apps@PROD -s APPLSYS  
Enter the ORACLE password of Application Object Library ‘APPSUSER’:  
Connected successfully to APPS.  
Enter the password for your ‘SYSTEM’ ORACLE schema:  
Connected successfully to SYSTEM.  Working…  
Enter new password for user:  
Verify new password for user:  
Working…  
AFPASSWD completed successfully.
 
 

Imporatant – Concurrent Manager Issues

1) Running FND_CONC_CLONE.SETUP_CLEAN

Note : In the case of a clone instance, it is strongly recommended to clean the non – existing nodes

FND_NODE table contains the node entries of both SOURCE environment and TARGET environment after clone.

If u want to get rid of the source node entries

a. Stop all middle tier services including the concurrent managers.

b. Stop the database.

c. Start the database.

d. Connect SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

e. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables.

2) Executing afdcm037.sql – Create The Service Manager “FNDSM”

NOTE: This script will create libraries for FNDSM and create managers for preexisting nodes.

If Service Manager is not running , To create the Service Manager “FNDSM” Please perform below steps.

a. Log in as applmgr

b. cd to $FND_TOP/patch/115/sql

c. Run the script: afdcm037.sql

Check again that FNDSM entries now exist: select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like ‘FNDSM%’;

3) Relink the binaries FNDLIBR, FNDSM, FNDFS and FNDCRM

It is better to relonk the binaries once, If Concurrent Managers do not start for the cloned Instance.

Go to cd $FND_TOP/bin

$ adrelink.sh force=y “fnd FNDLIBR”

$ adrelink.sh force=y “fnd FNDSM”

$ adrelink.sh force=y “fnd FNDFS”

$ adrelink.sh force=y “fnd FNDCRM”

4) Run CMCLEAN.sql, To Clean Concurrent Manager Tables.

Cleaning out the Concurrent Manager tables is a useful method of making sure that there are no invalid statuses that can prevent the managers from starting.

a.  Log in as APPS user

b.  Run the script: cmclean.sql

c.  COMMIT;

Find version of Oracle E-Business suite

We can find the Application version in two ways:

1) From the Form Application:

Help > About Oracle Applications

Capture

2)  Connect to database as user apps and execute below command.

SQL> SELECT RELEASE_NAME FROM FND_PRODUCT_GROUPS;

RELEASE_NAME
————————————————–
12.0.6

UNIX Useful Commands

1) Find whether OS is 64/32 Bit Kernel in UNIX.

uname -a

2) Find free physical memory in UNIX.

free -m

3) Find CPU details in UNIX.

cat /proc/cpuinfo

4) Find files modified within specific time.

find . -mtime -3 (modified less than 3days ago)

5) command used to alter file permissions.

chmod 777 abc.txt

6) Command used to reset the Ownership.

chown oracle:dba abc.txt

7) command used to set, or reset, the users login password.

Passwd username

8) Kill specific process in UNIX.

Kill -9 processid

9) Command used for display last given lines of a file.

tail -n alert_PROD.log

10) Command used for intall a rpm package.

rpm -ivh packagename.rpm

11) Command used to querry about any rpm package

rpm -q packagename

12) Command to Check the server up time

uptime

13) Command to check the file versions

strings -a <filename> |grep ‘$Header’

14) Command will keep ‘n’ number of days files and remove rest of file.

find . -mtime +n -exec rm  {} \; &

15) Basic commands for  vi editor

i   :- insert before cursor.

l   : insert begining of the line.

a  :- append after the cursor.

A  :- Append at the end of the line.

o :- insert a blank line below the cursor.

O :- insert a blank line above the cursor position.

h :- from current position one char towards left .

I :- from current position one char towards right.

j :- from current position one line towards down.

k :- from current position one line towards up.

Shift+g  :- go to end of the file.

Shift+:1 :- go to top of the file.

dd –> delete the ful line.

:q! —> closing the file without saving any changes.

:wq! –> save the changes and force close.

:w –> to save the changes without closing the file.

Find Concurrent manager logs on Oracle Apps

Concurrent Mangers log files are located in the $APPLCSF/$APPLLOG location.

cd $APPLCSF/$APPLLOG

For ICM Log                                       –> ls -lrt *$TWO_TASK*

For Standard manager Log                  –> ls -lrt w*.mgr

For Conflict Resolution manager Log –> ls -lrt c*.mgr

We can also get the ICM manager logfile location with below query

SELECT ‘ICM_LOG_NAME=’ || fcp.logfile_name

FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq

WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

AND fcp.queue_application_id = fcq.application_id

AND fcq.manager_type = ‘0’

AND fcp.process_status_code = ‘A’;

Sample Output:

SQL> SELECT ‘ICM_LOG_NAME=’ || fcp.logfile_name

2  FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq

3  WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id

4  AND fcp.queue_application_id = fcq.application_id

5  AND fcq.manager_type = ‘0’

6  AND fcp.process_status_code = ‘A’;

‘ICM_LOG_NAME=’||FCP.LOGFILE_NAME

——————————————————————————–

ICM_LOG_NAME=/u01/PROD/inst/apps/PROD_oracle-master/logs/appl/conc/log/PROD_0712.mgr

SIMPLE DATABASE CREATION

CREATION OF DATABASE IN COMMAND LINE:

1) Set Environment file:

#su – oracle

$pwd

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/PRODuct/11.2.0/db_1

export PATH=$ORACLE_HOME/bin:$PATH

2) Create Parameter file:

$cd $ORACLE_HOME/dbs

$cpinit.ora initPROD.ora

vi $ORACLE_HOME/dbs/initPROD.ora

db_name = PROD

control_files=/u01/PROD/control1.ctl,/u01/PROD/control2.ctl

undo_management = AUTO

undo_tablespace = UNDOTBS1

diagnostic_dest=/u01/PROD/

:wq!

3) Start the instance:

Edit the file $ORACLE_HOME/sqlplus/admin/glogin.sql and add in line as below.
setsqlprompt “_USER’@’_CONNECT_IDENTIFIER> ”

sqlplus / as sysdba

startup nomount

Note : In the nomount state oracle reads all initialization parameter values from the pfile(initPROD.ora)

4) Create the database:

vi create.sql

create database test

logfile group 1 (‘/u01/PROD/redo1.log’) size 100M,

group 2 (‘/u01/PROD/redo2.log’) size 100M,

group 3 (‘/u01/PROD/redo3.log’) size 100M

datafile ‘/u01/PROD/system.dbf’ size 500M autoextend on next 10M maxsize unlimited extent management local

sysauxdatafile ‘/u01/PROD/sysaux.dbf’ size 100M autoextend on next 10M maxsize unlimited

undotablespace undotbs1 datafile ‘/u01/PROD/undotbs1.dbf’ size 100M

default temporary tablespace temp tempfile ‘/u01/PROD/temp01.dbf’ size 100M;

sqlplus / as sysdba

@create.sql

5) Run catalog and catproc:

After Successful creation of the database we need to execute catalog.sql and catproc.sql.These two scripts updates the data dictionary tables and views. And pupbld.sql must be executed from Systemuser.This script updates users product information.

sqlplus / as sysdba

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

connect system/manager

@?/sqlplus/admin/pupbld.sql

####### Use Below dictionary view to see database information #####

sys@PROD>select * from v$version;

sys@PROD>select * from v$tablespace;

sys@PROD>select name from v$datafile;

sys@PROD>select name from v$tempfile;

sys@PROD>select name from v$controlfile;

sys@PROD>set wrap off

sys@PROD>select * from v$logfile;