Printer is printing in portrait instead of landspace

Here I assumed all prints are coming in portrait instead of landspace and found below workaround with oracle

"Solution

1. Setup for BI Publisher printing as per Note 338990.1 "How To Print XML Publisher PDF Reports Via The Concurrent Manager", however. Install and configure the pasta_pdf.cfg file with the acroread tool instead of pdf2ps or pdftops. If pdftops or pdf2ps is the prefered tool, please check with the vendor an/or with the product's documentation for the equivalent options used in the following example.

2. Copy the pasta_pdf.cfg file and rename it pasta_pdf_land.cfg. Edit the preprocessing section as follows.

% Preprocess for PDF output
% This is an example for PDF output to print.
; Xpdf
; preprocess=pdftops {infile} {outfile}
; Ghost Script
; preprocess=pdf2ps {infile} {outfile}
; Acrobat
preprocess=acroread -toPostScript -pairs -shrink -landscape {infile} {outfile}

3. Copy the "PDF Publisher" print style and rename it "PDF Publisher Landscape" In the Orientation field of the new style type "Landscape" and populate the SRW field with the "PSTLL" landscape print style.

4. Copy the "PASTA_PDF" printer driver and rename it "PASTA_PDF_LAND". Edit the new printer driver, populate the SRW field with the "PSTLL" landscape print style and modify the print argument as follows.

-pn$PROFILES$.PRINTER -f$PROFILES$.FILENAME -c$PROFILES$.CONC_COPIES -Fpasta_pdf_land.cfg

5. Associate the new "PDF Publisher Landscape" style and "PASTA_PDF_LAND" driver to the "--PASTA Universal Printer Type".

6. Bounce the concurrent manager.

NOTE:   Printer type, print style and printer driver information is cached in memory; in order for any printer setup form changes to take effect, you will need to bounce (restart) the concurrent managers on the concurrent processing server or restart the "Request Processing Managers" using OAM. 

Ref Doc:Custom Landscape Reports In PDF Format Print In Portrait Orientation [ID 421358.1]

Demantra performance issue

Issue: User is not able open worksheets
Solution: Rebuild the schema
Step 1) 
# ---------------------------------------------------------------------------
# Clean temp_tables and rebuild msdem schema
# ---------------------------------------------------------------------------
sqlplus /nolog << EOF
conn msdem/******
col PCT_CHAIN_CNT for  999.99
spool /tmp/${ORACLE_SID}_demantra_maintenance.log
select 'Start drop_temps at '|| to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') from dual;
exec DROP_TEMPS(0);
select count(1) from audit_values;
select 'Start drop_temps at '|| to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') from dual;
exec clean_log_tables();
select count(1) from audit_values;
select 'Start rebuild_schema at '|| to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') from dual;
Select table_name,num_rows, chain_cnt,(chain_cnt/num_rows)*100 pct_chain_cnt
from user_tables where chain_cnt >  0 order by chain_cnt desc;
exec rebuild_schema('1');
Select table_name,num_rows, chain_cnt,(chain_cnt/num_rows)*100 pct_chain_cnt
from user_tables where chain_cnt >  0 order by chain_cnt desc;
select 'Start exception_checking at '|| to_char(sysdate,'YYYY-MON-DD HH24:MI:SS') from dual;
select  to_char(err_date,'YYYYY-MON-DD HH24:MI:SS')||'|'||
proc_name||'|'||err_msg  from db_exception_log where trunc(err_date)=trunc(sysdate);
spool off
@ /home/oracle/dba/gen_msdem_index_rebuild.sql
@ /home/oracle/dba/msdem_index_rebuild.sql
EOF


Step 2)
Rebuild table and index
alter table MDP_MATRIX move; -- connect to object owner and start
select 'alter index '||INDEX_NAME ||' rebuild online nologging parallel 8;' from user_indexes where
table_name='MDP_MATRIX' and status='UNUSABLE';
select INDEX_NAME,status from user_indexes where table_name='MDP_MATRIX' and
status='UNUSABLE';
alter index SALES_DATA_LUD_IDX rebuild online nologging parallel 8;
ANALYZE table MSDEM.MDP_MATRIX estimate statistics sample 10 percent;
Gather stats
exec DBMS_STATS.GATHER_TABLE_STATS('MSDEM','SALES_DATA',NULL,5,FALSE,'FOR ALL COLUMNS SIZE 1',5,'ALL',TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS('MSDEM','MDP_MATRIX',NULL,5,FALSE,'FOR ALL COLUMNS SIZE 1',5,'ALL',TRUE);
execute dbms_stats.gather_schema_stats (ownname => 'MSDEM', estimate_percent => 50, degree => 5, cascade => true);
use below querry to compare the data after gather stats.
SQL> select count(*) from mdp_matrix
COUNT(*)
----------
222400
@@@@@@@@@chaning row information@@@@@@@
select table_name, chain_cnt, num_rows, (chain_cnt/num_rows)*100 percent
from user_tables where chain_cnt >0 and num_rows > 0 order by chain_cnt desc;
SELECT table_name, tablespace_name, chain_cnt, num_rows, (chain_cnt / num_rows) * 100 PERCENT, last_analyzed FROM user_tables
WHERE chain_cnt > 0 AND num_rows > 0;
SELECT TABLE_NAME, CHAIN_CNT, NUM_ROWS, (CHAIN_CNT/NUM_ROWS)*100 PERCENT FROM USER_TABLES where table_name='MDP_MATRIX';

How to clear Apache cache from Application without bouncing listener in R12

1) Navigate to "Functional Administrator" responsibility
2) Once logged in click on the "Core Services" tab
3) Click on "Caching Framework" link in the blue menu bar
4) Click on "Global Configuration" link in the left vertical menu
5) In the "Cache Policy" region click on the "Clear All Cache" button
6) Click the "Yes" button to confirm the action
7) Click the "Apply" button to apply the changes

Database States

Remember that there are, in essence, two different entities: the Oracle Instance and the database.  The instance is used to access and manipulate the database data and storage structures.

An instance can be Started up which builds the SGA in memory and starts the background processes.  The instances can also be SHUT DOWN which closes the database, stops the instance and removes the memory structures.  The database itself may be open, closed or mounted.

Database Start-up

It can be observed as below that the database can be altered in the upward direction (up the stairs) but not back down them. 

NOMOUNT	Starts up the instance without mounting the database.  Activities are limited to things like creating  a database.  The database is inaccessible with no dictionary available for password checking.
MOUNT	Starts up the instance and mounts the database.  It allows restricted use of the database for “DBA” tasks such as file management and database structuring.  There is no normal user access possible
OPEN	It starts the instance, mounts and then opens the database.  Other file management activities available such as Manipulating log files, taking datafiles offline, backing up control files.  Normal users processing is allowed.
FORCE	Shuts down an instance before starting it up in the specified mode and is the same as performing SHUTDOWN ABORT followed by a start-up.
RECOVER	This is an OPEN option which forces automatic recovery procedures on start-up.
RESTRICT	This is an OPEN option which limits access to the database to users who have been granted both CREATE SESSION AND RESTRICTED SESSION system privileges.  It is useful for doing tasks like database exports while there is no activity on the database. SQL>STARTUP OPEN RESTRICT Can be removed while open by doing the following command: SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;

The above options can be combined at the SQL prompt to create the state you wish the database to start.

SQL>STARTUP [FORCE] [NOMOUNT | MOUNT | OPEN [open_options]][oracle_sid_name][PFILE=parameter file];

Examples:

SQL>STARTUP NOMOUNT

SQL>ALTER DATABASE MOUINT;

SQL>ALTER DATABASE OPEN;

Database Shutdown

The SHUTDOWN command closes the database, dismounts the database and then shuts down the instance.  There are several SHUTDOWN options that can be employed.

1        SQL>SHUTDOWN NORMAL

o     The database will only SHUTDOWN when all users have logged off and all work is committed (or rolled back). 

o     No new connections are allowed.

o     This option is difficult to use in man environments since the users are applications and all the applications have to be shutdown before the database would shutdown.

2        SQL>SHUTDOWN IMMEDIATE

o     Disconnects all users and performs rollback on all uncommitted data by using PMON

o     Terminates all current SQL statements

3        SQL>SHUTDOWN ABORT

o     Shuts down without tidying up; akin to system failure and requires system recovery on start-up

o     No rollback of uncommitted transactions

Script to find log & out files of a concurrent request

SELECT logfile_name, logfile_node_name, outfile_name, outfile_node_name,
       controlling_manager
  FROM fnd_concurrent_requests
 WHERE request_id = &&request_id

Error : FRM-92050 : Failed to connect to the Server: /forms/lservlet-1

Oracle Application R12

When you login Oracle Application R12 have an error that

" Error “FRM-92050 : Failed to connect to the Server: /forms/lservlet-1” "

1. Internet --> Tools --> Internet Options
Security TAB --> Trusted Sites and click Custom level
2. Didsble XSS filter
3. Retest issue .

Concurrent Processing - CCM.sql Diagnostic Script to Diagnose Common Concurrent Manager Issues

 - - - - Code begins here - - - - - - - - - - - - - - -

REM #########################################################################
REM ## Purpose:  Diagnostic Script for Concurrent Manager
REM ## Author:   nabil albadin
REM ## Email:    nabil.albadin@oracle.com
REM ## Filename: ccm.sql
REM ## Cert:     10.7, 11, 11.5, 12.0
REM ## Note:
REM ## Usage:    sqlplus apps/<passwd> @ccm.sql
REM ## Output:   reqcheck.lst
REM ## Notes:
REM ##   Enter value for request ID WHEN PROMPT
REM ##
REM ##      $Id: request.sql, v 1.0 4/17/2002 10:22 nalbadin Exp $
REM #########################################################################

spool ccm.lst
prompt Step 1 Checking how many rows in FND_CONCURRENT_REQUEST.

select  count(*) from fnd_concurrent_requests;
prompt
-----------------------------------------

prompt Step 2 Checking how many rows in FND_CONCURRENT_PROCESSES table.

select  count(*) from fnd_concurrent_processes;
prompt
-----------------------------------------------


prompt Step 3 Checking sys.dual table which should have one and only one row.
select  count(*) from sys.dual;

prompt If you have more than one row in sys.dual, please delete it

prompt sql> delete rownum from SYS.DUAL;  
Prompt rownum= the row number to delete
prompt
prompt
prompt
---------------------------------------------


prompt Step 4 Checking fnd_dual. There must be at lest one row:

select  count(*) from fnd_dual;
prompt If there are no record selected,
prompt Update fnd_dual table to have at lest one record
prompt
----------------------------------------------

prompt Step 5 Checking the Internal Manager queue name "FNDICM" which should be=1

select concurrent_queue_id from fnd_concurrent_queues
where concurrent_queue_name='FNDICM';
prompt
----------------------------------------------

prompt Step 6 Checking for Active processes under the Internal Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
  , substr(b.os_process_id,0,10) "OS Proc"
  , b.oracle_process_id "Oracle ID"
  , b.process_status_code
  from fnd_concurrent_queues a
  , fnd_concurrent_processes b
  where a.concurrent_queue_id=b.concurrent_queue_id
  and a.concurrent_queue_name='FNDICM'
  and b.process_status_code='A'
  order by b.process_status_code;
 
prompt If any rows found with process_status_code with value = 'A' (= Active)    
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
-----------------------------------------
prompt Step 7 Checking for Active processes under the Standard Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
  , substr(b.os_process_id,0,10) "OS Proc"
  , b.oracle_process_id "Oracle ID"
  , b.process_status_code
  from fnd_concurrent_queues a
  , fnd_concurrent_processes b
  where a.concurrent_queue_id=b.concurrent_queue_id
  and a.concurrent_queue_name='STANDARD'
  and b.process_status_code='A'
  order by b.process_status_code;
 
prompt If any rows found with process_status_code with value = 'A' (= Active)    
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
------------------------------------------
prompt Step 8 Checking for Active processes under the Conflict Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
  , substr(b.os_process_id,0,10) "OS Proc"
  , b.oracle_process_id "Oracle ID"
  , b.process_status_code
  from fnd_concurrent_queues a
  , fnd_concurrent_processes b
  where a.concurrent_queue_id=b.concurrent_queue_id
  and a.concurrent_queue_name='FNDCRM'
  and b.process_status_code='A'
  order by b.process_status_code;
 
prompt If any rows found with process_status_code with value = 'A' (= Active)    
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
---------------------------------------------------
prompt Step 9 Checking Actual and Target Processes for Internal Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDICM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
--------------------------------------------------------

prompt Step 10 Checking Actual and Target Processes for the Standard Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='STANDARD';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------
prompt Step 11 Checking Actual and Target Processes for Conflict Resolution  Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDCRM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------

Prompt Step 12 Checking if the control_code set to 'N':

select control_code from fnd_concurrent_queues
where control_code='N';
prompt
prompt If any rows selected, please update the table fnd_concurrent_queues:
prompt Update fnd_concurrent_queues set control_code = null
prompt where control_code ='N';
PROMPT Update fnd_concurrent_queues set target_node = null;
PROMPT commit;
prompt
prompt
--------------------------------

PROMPT Step 13 Checking terminated processes:
PROMPT
select count (*) from fnd_concurrent_requests
where status_code='T';
prompt
prompt If you have terminated processes run the following sql statement:
prompt
prompt SQL> Update fnd_concurrent_requests
prompt      set status_code = 'E', phase_code = 'C'
prompt      where status_code = 'T';
prompt
------------------------------------------


prompt Step 14 Checking pending requests:

select count(*) from fnd_concurrent_requests
where status_code='P';
prompt If any rows selected please run the following sql statement:

prompt SQL> Update fnd_concurrent_requests
prompt      set status_code = 'E', phase_code = 'C'
prompt      where status_code = 'P';
prompt
------------------------------------------------------
prompt Step 15 Checking Running processes:
prompt
select count (*) from fnd_concurrent_requests
where status_code='R';
prompt
prompt If you have Running processes run the following sql statement
prompt SQL> Update fnd_concurrent_requests
prompt      set status_code = 'E', phase_code = 'C'
prompt      where status_code = 'R';
prompt
------------------------------------------

prompt Step 16 Checking the PMON method, which should be set to LOCK:
prompt
select profile_option_id , profile_option_value
from FND_PROFILE_OPTION_VALUES
where profile_option_id= (select profile_option_id
from FND_PROFILE_OPTIONS
where profile_option_name='CONC_PMON_METHOD');
prompt
prompt If the PROFILE_OPTION_VALUE was't LOCK please
prompt Reset PMON to LOCK by running afimpmon.sql script(The manager should be down) 

prompt 1-At UNIX command prompt:

prompt 2-cd $FND_TOP/sql

prompt 3-Log into SQLPLUS as apps/

prompt SQL> @afimpmon.sql
prompt   prompt1:dual
prompt   prompt2:LOCK     (LOCK MUST BE ALL UPPERCASE)  
    
prompt For Oracle Applications Release 11.5 and 12.0, when you check the PMON
prompt Method you may get no rows selected which is normal,
prompt because in apps 11.5 and 12.0 the PMON Method is hard coded to Lock at
prompt the Operating System level.
prompt
prompt
-------------------------------------------------------

prompt Step-17 Checking how many FNDLIBR processes are running:
prompt -For Unix :From unix command  prompt $ ps -ef |grep -i fndlibr
prompt If you have any FNDLIBR processes running,please kill them before
prompt starting or shuting down the internal manager
prompt
prompt
prompt -For NT, through Task Manager, check the entries under the Processes tab
       for FNDLIBR.exe processes.
prompt If there are any, Highlight and click [End Process] button to kill processes
     
prompt
----------------------------------------------------------

prompt Step-18 Checking how many "FND_%"invalid objects:

 select substr(owner,1, 12) owner, substr(object_type,1,12) type,
 substr(status,1,8) status, substr(object_name, 1, 25) name
 from dba_objects
 where object_name like 'FND_%'
 and status='INVALID';

prompt If you have any invalied objects please see Note 113947.1

prompt
--------------------------------------------------------------

prompt Step-19-How to find the PID in the O/S for request_id:
prompt If you do not like to check this enter any number then click Enter to Exit

select r.request_id, p.os_process_id
from FND_CONCURRENT_REQUESTS r,FND_CONCURRENT_PROCESSES p
where r.controlling_manager = p.concurrent_process_id
and request_id=&request_id;

prompt
prompt Please upload the "ccm.lst" output to Support, Thanks.
prompt
spool off
 - - - - - - - - - - - - - - - -  Code ends here  - - - - - - - - - - - - - - - -

Issue: “Active Users” concurrent request is completed with error

Issue: “Active Users” concurrent request is completed with error

Error: ar60run: relocation error: /d03/ivdev1/oracle/ivdev1ora/8.0.6/network/jre11/lib/i686/native_threads/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference

Fix: add the following entry in adovars.env
LD_PRELOAD=/lib/libcwait.so
export LD_PRELOAD
Bounce Concurrent manager

Corrupt blocks by NOLOGGING - Error explanation and solution

This document is intended for Customers and Oracle Support.

ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution

When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

If the associated redo/archived log file is used to RECOVER the data files, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads.

Errors Example:

SQL> select * from test_nologging;

ORA-01578: ORACLE data block corrupted (file # 11, block # 84)
ORA-01110: data file 4: '/oradata/users.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The NOLOGGING attribute is stored in column LOGGING in data dictionary views like:

DBA_TABLES, DBA_INDEXES, DBA_LOBS, DBA_TAB_PARTITIONS, DBA_LOB_PARTITIONS, DBA_TAB_SUBPARTITIONS, etc.

LOGGING='NO' indicates NOLOGGING.

The way for Oracle to identify that the block was previously invalidated due to NOLOGGING is by updating most of the bytes in that block with 0xff only if that "invalidate" redo is applied in a Recovery.

The block is then marked as Soft Corrupt meaning that the next block read will report the ORA-1578/ORA-26040 errors.

The SCN in the block corresponds to the SCN in the REDO RECORD for when the "INVALIDATE" change was applied in a recovery. This is useful to know the timestamp for when the block was marked as soft corrupt due to NOLOGGING.

RMAN/DBV and Corrupted Blocks by NOLOGGING

DBV prints the generic message DBV-200 in rdbms versions lower than 10.2.0.4 and error DBV-201 in RDBMS versions greater or equal to 10.2.0.4  ( Note  5031712.8 ):

DBV-00200: Block, dba 46137428, already marked corrupted
DBV-00201: Block, DBA 46137428, marked corrupt for invalid redo application

In rdbms versions lower than 10.2.0.5 RMAN reports is with a generic message like:

RMAN reports it in v$database_block_corruption with CORRUPTION_TYPE=LOGICAL

When there is a generic message besides the error ORA-26040, a block dump might be taken and see if the byte 0xff is along the block or if the block is associated to a segment, try to read it with a SQL statement for which errors ORA-1578/ORA-26040 will be produced if the block is corrupt due to a recovery with a NOLOGGING operation.

For RMAN to identify if the block is corrupt by NOLOGGING, an enhancement has been provided in Bug 7396077.  See Note 7396077.8

RMAN backups don't fail due to NOLOGGING corrupt blocks. In general RMAN does not fails with soft corrupt blocks so the MAXCORRUPT clause is not necessary in such cases.

Important change in 11g

FORCE LOGGING is irrelevant in NOARCHIVELOG mode; this was a change introduced in 11g.

Reference Note 1071869.1

SOLUTION

Note that the data inside the affected blocks is not salvageable. Methods like "Media Recovery" or "RMAN blockrecover" will not fix the problem unless the data file was backed up after the NOLOGGING operation was registered in the Redo Log.

In order to resolve the errors and if it is not an INDEX the segment can be recovered from a backup like an export dump or from another source. If backups are not available the segment might be recreated following the next steps:

• Identify the object as described in Note 819533.1

• If it is an INDEX, drop/create the index.

• If it is a TABLE then procedure DBMS_REPAIR.SKIP_CORRUPT_BLOCKS can be used to skip the corrupt block in SQL statements and decide to re-create the table. Note 556733.1 has an example of DBMS_REPAIR. 

• If it is a LOB segment associated to a LOB column in a Table, use Note 293515.1

• If the error is produced in a Physical STANDBY database, the option is to restore the affected file from the PRIMARY database (only if the problem is not present in the PRIMARY).

Run script provided in Note 472231.1 to identify any additional corrupted objects.

 

References

NOTE:1071869.1 - ORA-1578 ORA-26040 in 11g for DIRECT PATH with NOARCHIVELOG even if LOGGING is enabled
NOTE:290161.1 - The Gains and Pains of Nologging Operations
NOTE:293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
NOTE:472231.1 - How to identify all the Corrupted Objects in the Database reported by RMAN
NOTE:556733.1 - DBMS_REPAIR SCRIPT
NOTE:7396077.8 - Bug 7396077 - RMAN does not differentiate NOLOGGING corrupt blocks that produce ORA-1578/ORA-26040
NOTE:819533.1 - How to identify the corrupt Object reported by ORA-1578 / RMAN / DBVERIFY

 ORA-1578 / ORA-26040 Corrupt blocks by NOLOGGING - Error explanation and solution [ID 794505.1]

Creating a Single Instance Physical Standby for a RAC Primary(Dual Node)

PREAMBLE :-

This Document gives basic information about the steps carried out to setup an Standby Database. The primary database (production DB) is a dual node RAC database. The Physical standby database is a Single Node/Single instance. The DB version on both the Primary and Target is 10.2.0.3.

The Environment:-

Production
(2 Linux Servers) With DB version 10.2.0.3.0 Real Application Cluster.
Standby Server(1 Linux Server) with the same version of DB as the Primary.
The Physical layout of the mounts/DB files/Log file etc in the standby are exact replica of the the primary instance.

1) Setup the primary Database.

a) Enabled the Force logging in the Primary Database using the below command as sysdba
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
b) Created the password file on both the RAC nodes in Primary as the Redolog files from both the servers will be transferred to the Standby.
/d02/oraprod/product/10.2.0/dbs
cd $ORACLE_HOME/dbs
orapwd file=orapwPROD1 password=PASSWORD force=y
(Repeat the same in the other Node)
c) The Standby Log is not created now as it is only during the testing of the failover.

d) Setup the Primary database Initialization parameters need for Standby.(These parameters should be added in both the nodes)

Note: the Parameter Remote_login_passwordfile should be set to exclusive.
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/arch01'
*.log_archive_dest_2='location=/oracle03/oraprod/arch02'
.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/d01/oraprod/prodarc'

e) Once the new parameters needed for Standby are added bounce the instance for the parameters to take effect.

2) Standby Database steps

a) Create control file in the primary database which will be used by the standby database.

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
'/data01/home/oraprod/PRODSTBY.ctl';
Database altered.
3) Backup the primary Database and copy the data files to the target server to the required mounts.

In our case i had shutdown the database and took a Tar backup in the local server and copied to the Standby server. We should copy all the archive files to the standby server which will be used for recovery.

4) Copy the initialization parameter file from the primary to the standby node and make necessary changes.

[oraprod@standby dbs]$ more initPROD.ora
PROD.__db_cache_size=2097152000
PROD.__java_pool_size=33554432
PROD.__large_pool_size=16777216
PROD.__shared_pool_size=2097152000
PROD.__streams_pool_size=33554432
*._b_tree_bitmap_plans=FALSE# Required 11i setting
*._fast_full_scan_enabled=FALSE
*._immediate_commit_propagation=TRUE
*._like_with_bind_as_equality=TRUE
*._lm_global_posts=TRUE
*._sort_elimination_cost_ratio=5
*._sqlexec_progression_cost=2147483647
*._system_trig_enabled=true
*._trace_files_public=TRUE
*.aq_tm_processes=1
*.background_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/bdump'
#*.cluster_database_instances=2
#*.cluster_database=TRUE
*.compatible='10.2.0'
*.control_files='/oracle03/oraprod/data01/PRODSTBY.ctl','/oracle03/oraprod/data01/PRODSTBY1.ctl'
*.core_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD1_PROD_standby/cdump'
*.cursor_sharing='EXACT'# Required 11i settting
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_file_multiblock_read_count=8# Required 11i setting
*.db_files=1024# Max. no. of database files
*.db_name='PROD'
*.dml_locks=10000
*.instance_name='PROD'
*.instance_number=1
*.job_queue_processes=2
*.log_buffer=10485760
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200# Checkpoint at least every 20 mins.
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'# trace file size
*.nls_comp='binary'# Required 11i setting
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'# Required 11i setting
*.nls_numeric_characters='.,'
*.nls_sort='binary'# Required 11i setting
*.nls_territory='america'
*.olap_page_pool_size=4194304
*.open_cursors=600# Consumes process memory, unless using MTS.
*.optimizer_secure_view_merging=false
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=1G
*.plsql_code_type='INTERPRETED'# Default 11i setting
*.plsql_native_library_dir='/d02/oraprod/product/10.2.0/plsql/nativelib'
*.plsql_native_library_subdir_count=149
*.plsql_optimize_level=2# Required 11i setting
*.processes=800# Max. no. of users x 2
*.service_names='PROD'
*.session_cached_cursors=500
*.sessions=400# 2 X processes
*.sga_target=4G
*.shared_pool_reserved_size=200M
*.shared_pool_size=2000M
*.thread=1
*.timed_statistics=true
*.undo_management='AUTO'# Required 11i setting
*.undo_tablespace='APPS_UNDOTS1'# Required 11i setting
*.user_dump_dest='/d02/oraprod/product/10.2.0/admin/PROD_standby/udump'
*.utl_file_dir='/usr/tmp','/usr/tmp','/d02/oraprod/product/10.2.0/appsutil/outbound/PROD1_standby','/usr/tmp','/d02/pr
od/oracle/prodappl/xxdn/11.5.0/data','/d02/prod/oracle/prodappl/xxdn/11.5.0/data/inbound','/d02/prod/oracle/prodappl/xxdn/11.
5.0/data/outbound','/usr/tmp'
*.workarea_size_policy='AUTO'# Required 11i setting
#Archiving
.log_archive_config='send'
*.log_archive_dest_1='location=/oracle04/oraprod/prodarc'
*.log_archive_dest_2='SERVICE=PROD_PROD LGWR ASYNC REOPEN=100'
#.log_archive_dest_3='SERVICE=PRDN_STANDBY LGWR ASYNC REOPEN=100'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='defer'
#*.log_archive_dest_state_3='ENABLE'
*.log_archive_format='prod_%s_%t_%r.arc'
*.log_archive_max_processes=8
*.log_archive_start=TRUE
*.standby_archive_dest='/oracle04/oraprod/prodarc'
*.standby_file_management='AUTO'

5) Create all the required dump directories and archive log destinations mentioned in the parameter file.

6) Copy and place the standby control file created in the Primary database to the Standby server.


7) Configure the listener ,tnsnames in the standby DB to reflect the entries of the both the RAC instances.

Tnsnames.Ora entry:

PROD_PROD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =prod1)(PORT = 1581))
(ADDRESS = (PROTOCOL = TCP)(HOST =prod2)(PORT = 1581))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVICE_NAME = PROD)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 200)
(DELAY = 5)
)
)
)

Also Add the entry of the Standby Instance in the tnsnames of both the RAC nodes and test is by issuing tnsping



8) Setup the Standby instance and bring up the instance .

a) Setup the initialization parameters to point to the standby database, and bringup the instance with mount option and place it in the standby mode.


SQL> Startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

9) The Standby database will automatically apply all the archives and the current logs will applied. Check the Alert logs and confirm that the standby is in Sync.