Discoverer 10g (10.1.2) Portlets Show 25 Rows Even Though You Have Changed The 'RowsPerHTML' and 'RowsPerPage' Preferences [ID 456868.1]

1. Setting the global setting:

* Set the 'RowsPerPage' parameter in the $ORACLE_HOME/discoverer/util/pref.txt file to your desired global setting for all newly created worksheets.

* Next, run the applypreferences.sh (or .bat) to set the preferences for an new Discoverer sessions.

2. Changing existing portlets / Viewer worksheets:

* To change individual worksheets displayed in a portlet, you will need to log into Discoverer Viewer as the owner of the worksheet.
* Click on the 'Rows and Columns' link above the table. If the link does not exist, then see your Discoverer Administrator, they may have removed it from your layout via a customization.
* Click on Save line under the Actions section on the left.
* The portlets will now display the desired rows

Physical Standby

Как Создать Physical Standby базу в ORACLE 9i

Step 1 :

Добавляем следующие параметры в init<SID>.ora на primary базе данных

log_archive_dest_1 = 'LOCATION=/u01/oracle/pr/prdb/arch MANDATORY'

log_archive_dest_state_1 = ENABLE

log_archive_start = TRUE

log_archive_dest_2 = 'SERVICE=STBY2','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=60 register’

Или ( Просто добавить tns запись для STANDBY базы)

log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=app2.tidici.com)(PORT=1526)))(CONNECT_DATA=(SID=pr)(SERVER=DEDIC ATED)))"','ARCH SYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 reopen=60 register '

standby_file_management=auto

# archive_lag_target = 1800 ( этот параметр используется для автоматического переключения archive log на primary базе. Значение в переделах от 0 до 7200 секунд )

log_archive_dest_state_2 = ENABLE

#log_archive_dest=/home/oracle/proddata/arch (Закомментировать этот параметр на primary базе)

Step 2 :

Добавляем в tnsnames.ora на primary базе

STDBY2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = gc.tidici.com)(PORT = 1526))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME =pr)

)

)

Step 3 :

Редактируем init.ora на standby базе

fal_client = 'fal_stdby2'

fal_server = 'fal_prod'

standby_file_management = AUTO

control_files=/home/users/PROD/m3stndby.ctl

standby_archive_dest = '/ms11/archm3/m3'

log_archive_dest = /ms11/archm3/m3

log_archive_start = true

#db_file_name_convert = ('/u06/','/u06e/','/u07/','/u07e/') -> Использовать если отличаются калаги standby базы то primary базы

#log_file_name_convert = ('/u06/','/u06e/','/u07/','/u07e/') -> Использовать если отличаются калаги standby базы то primary базы

Step 4 : (a).

Редактируем tnsnames.ora на standby базе

fal_prod =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = db.tidici.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME=pr)

)

)

fal_stby2 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = app2.tidici.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pr)

)

)

(b). Редактируем listner.ora на standby базе

FAL_stby1 =

(DESCRIPTION =

(ADDRESS =

(PROTOCOL = TCP)(HOST = 192.168.20.180)(PORT = 1530)

)

)

SID_LIST_FAL_stdby =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PROD)

(ORACLE_HOME = /ms10/oracle/9.2.0)

(SID_NAME = PROD )

)

)

Step 5 :

(a) . Switch one archive on the primary

alter system switch logfile;

(b). Теперь переведём tablespace на primary базе в backup режим

[ Если необходимо создать базу из hot backup ]

select ' alter tablespace '||tablespace_name||' begin backup ;' from dba_tablespaces where CONTENTS <> 'TEMPORARY';

alter tablespace esvc begin backup ;

alter tablespace samora begin backup;

………………………………………

………………………………………

………………………………………

Выполните команду приведенную выше . Она переведет все tablespace в backup режим.

Перед копированием файлов на standby базу. Проверяем статус.

select status,count(*) from v$backup group by status;

STATUS COUNT(*)

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

ACTIVE 29

(b) . Копируем все datafiles c primary базы на standby базу

scp oracle@192.168.14.32:/u06/oracle/pr/prdata/a_txn_data14.dbf /u06/oracle/pr/prdata/

(d). Теперь возвращаем tablespace в обычный режим.

select ' alter tablespace '||tablespace_name||' end backup ;' from dba_tablespaces where CONTENTS <> 'TEMPORARY';

alter tablespace esvc end backup ;

alter tablespace samora end backup;

……………………………………..

……………………………………..

Выполняем команды приведённые вверху. Тем самым возвращаем tablespace в обычный режим. Проверяем состояние.

select status,count(*) from v$backup group by status;

STATUS COUNT(*)

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

NOT ACTIVE 29

(е). После завершения копирования. Создаём standby controlfile с помощью команды:

alter database create standby controlfile as ' /u04/oracle /stbycf.ctl ';

Затем , переместим его на Standby в каталог указанный в init.ora

Step 6:

Now up the Standby database as follows

(a ). Запустим standby базу nomount режиме

startup nomount pfile=’u01/file/initPROD.ora’

(b). Монтируем standby базу

alter database mount standby database ;

Заметка :

Теперь , наша standby база в mount режиме. Для автоматического восстановления standby, необходимо перегрузить нашу PRIMARY базу. Таким образом, все изменения, которые мы внесли в initPROD.ora на primary базе будут применены.

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

C.1 # После перезагрузке primary базы. Для введения standby базы в managed recover режим, выполним команду:

alter database recover managed standby database disconnect from session ;

C.2 # Если нет возможности перегрузить primary базу, то необходимо в ручную скопировать archive логии на standby базу . И выполнить команду:

recover standby database;

далее, выбираем “AUTO” режим, чтобы применить все недостающие archive логии.

Note 1 : При создании standby базы на одном и том же сервере с primary базой

Необходимо использовать “LOCK_NAME_SPACE” параметр .

LOCK_NAME_SPACE = standby

И добавить “instance_name” в init.ora на Primary и Standby.

Note 2 : Как проверить max количество archive log применённых на standby базе.

select max(sequence#) from v$archived_log where applied=’YES’;

Switch Stand-by database to Primary Database role

1. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

2. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH SKIP STANDBY LOGFILE;

3. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

After this , you cannot use this database as a stand-by database anymore.
You will need to create a new standby database as a replica of this new primary database.

How To Change The Default Export Location For Discoverer Plus 10g(DefaultExportPath)

Applies to:

Oracle Discoverer - Version: 10.1.2.0.0 to 10.1.2.3
Information in this document applies to any platform.

Goal

When exporting to various formats via Discoverer Plus 10g (10.1.2), the file is always sent to a location like "C:\Documents and Settings\."

How can this default export location be changed?

Solution

The Sun java virtual machine plug-in used for Discoverer Plus is installed and is specific to each PC.
You can set some system-level properties per the Sun JVM "Deployment Configuration File and Properties Guide.

However, the guide does not specifically indicate you can set the user home at the system-level.
Setting the user home at the local PC level will allow changing the default export path for Sun JRE 1.4.
This option is only available for Sun JRE 1.4.

NOTE: For Sun JRE 1.5 and above, you can only specify a different location for all users (not for individual user), for more information please refer to Note 365245.1.

The user (or administrator) is required to make the following changes locally on every PC.

For Sun Java Plugin:

1. Locate the file "C:\Documents and Settings\\Application Data\Sun\Java\Deployment\deployment.properties",
   and add the entry below :
   
   
   
   user.home=
   
   
   For example: If the new directory is: 'D:\DISCO_1012\TEMP', then the entry would look like this
   (with double backslashes in lieu of a single backslash):
   
   
   user.home=D:\\DISCO_1012\\TEMP
   
   
   
2. Save the "deployment.properties" file once the entry is added.
   
   
3. Close all browsers and relaunch Discoverer Plus in a new browser session.
   
   
4. Run a workbook and perform export, which will attempt to export to the new directory.

For Oracle Jinitiator Plug-in:

1. Open Windows Control Panel,  double click on "Jinitiator 1.3.1.xx" icon,
click on cache tab and note down the value set for Location under
"JAR cache Settings".
Suppose the value is set to "C:\WINNT\Profiles\jdoe\Oracle Jar Cache"

2. Click on Start >> Run
and type the command:

  explorer %userprofile%\.jinit

This opens Windows Explorer with the current directory as %userprofile%\.jinit

3. Check if there is any file with name:

  properties13xx (used by Jinitiator 1.3.1.xx)

4. If there is no such a file create a new file with name properties13xx
(for example properties1318, if using jinitiator 1.3.1.8) and add
the following entries:

  jarcache.directory=

(make sure to put two backslashes in the place of single backslash)

  user.home=

(make sure to put two backslashes in the place of single backslash)

For example:

  user.home=D:\\TEMP
  jarcache.directory=C:\\WINNT\\Profiles\\jdoe\\Oracle Jar Cache

(Note there are two '\' between directory name separators)

5. If there is already existing file properties13xx in the
%userprofile%\.jinit directory and jarcache.directory entry
 exists in this file and it is pointing to a directory, then do not change it.
Only add the entry for user.home as shown in step 4 at the end of the file.
Before modifying properties13xx file, take a backup of those files.

6. Save the file.

7. Exit all the browser windows.

8. Invoke a new browser window to run Discoverer Plus.

Now export from Discoverer Plus should create files under the directory to
which user.home entry points to.

Where is listener information stored in Database

Where is listener information stored in Database

In E-Business Suite, the information about listeners is stored in the tables

APPS.FND_TNS_LISTENERS
APPS.FND_TNS_LISTENER_PORTS

SQL> SELECT A.LISTENER_NAME,B.PORT
FROM APPS.FND_TNS_LISTENERS A, APPS.FND_TNS_LISTENER_PORTS B
WHERE A.LISTENER_GUID = B.LISTENER_GUID
/

LISTENER_NAME########## PORT
---------------------------###########-------
1 APPS_app_pr_APPS##########1626
2 db_pr_DB##################1521

How to find out which users are logged on to an Apps instance

FND_USER table stores the details of all the end users. If we give this query:

select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

USER_NAME TO_CHAR(LAST_LOGON_DATE,'D
------------------------------------
GUEST 05-MAR-2010 16:01:47
SYSADMIN 05-MAR-2010 16:02:06


The above query can give you a good idea who is logged on.

For a more accurate result, refer to metalink note 269799.1 which says:

You can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into Oracle Applications.This diagnostic test will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs. Please note that to collect the audit information about the users who have logged into Forms, the "Sign-On:Audit Level" profile option should be set to Form. You can access this Diagnostic test via Metalink Note# 233871.1.

Calculate number of concurrent users of an existing instance

Calculate number of concurrent users of an existing instance

The view v$license keeps track of concurrent sessions and users.

SQL> desc v$license

Name Null? Type

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

SESSIONS_MAX NUMBER

SESSIONS_WARNING NUMBER

SESSIONS_CURRENT NUMBER

SESSIONS_HIGHWATER NUMBER

USERS_MAX NUMBER

CPU_COUNT_CURRENT NUMBER

CPU_CORE_COUNT_CURRENT NUMBER

CPU_SOCKET_COUNT_CURRENT NUMBER

CPU_COUNT_HIGHWATER NUMBER

CPU_CORE_COUNT_HIGHWATER NUMBER

CPU_SOCKET_COUNT_HIGHWATER NUMBER

select sessions_current from v$license;

The above query will give you the number of concurrent users right now.

You can write a small job which will capture this information every hour for a week. Once you have this data, you can take an average of this data to get the number of concurrent users.

FAILED: file icxwtab.odf during adpatch

Recently while patching, the worker running icxwtab.odf failed:

ATTENTION: All workers either have failed or are waiting:

FAILED: file icxwtab.odf on worker 1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

adworker log showed:

Start time for statement below is: Wed Jul 09 2008 17:12:20

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.

AD Worker error:
The index cannot be created as the table has duplicate keys.


Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys because of the error above

As specified in Metalink Note 430673.1:

Symptoms

adpatch fails on script icxwtab.odf with the following errors:

ERROR
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Mon May 07 2007 14:23:44

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.
AD Worker error:
The index cannot be created as the table has duplicate keys.

Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above

SPECIFIC DATA
Ran the suggested query, and here is the output:

TRANSACTION_ID COUNT(*)
-------------------------- -------------
148341124 2
431640607 2
555224577 2
1202811809 2

Cause

These duplicate transactions are there because the concurrent program that deletes temporary
session data (program that removes old entries in ICX_SESSIONS and ICX_TRANSACTIONS) is not
executed on a regular basis. As a result, these tables grow in space and there is the possibility
that the sequences cycle and restart, creating duplicate primary keys.

The following justifies how the issue is related to this specific customer:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

TRANSACTION_ID COUNT(*)
-------------------------- --------------
148341124 2
431640607 2
555224577 2
1202811809 2

This is explained in the following unpublished bug: Bug 5001287 PERFORMANCE PROBLEM WHEN APPROVING POS WITH ICX_TRANSACTIONS

Solution

To implement the solution, please execute the following steps:

1. Run the purge program:
a. The name of the program is "Purge Inactive Sessions" located under the "Apps for the Web Manager" responsibility.
b. The internal name is ICXDLTMP.
c. Also you can find this SQL script under $ICX_TOP/sql (named ICXDLTMP.sql).

2. Rerun the failed worker (icxwtab.odf).

3. Migrate the solution as appropriate to other environments.

4. This program should be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION tables, otherwise they will continue to grow.

Running this sql returned two rows:

SQL> SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
2 3 4 5
SQL> /

TRANSACTION_ID COUNT(*)
-------------- ----------
746007924 2

SQL> desc icx_transactions
Name Null? Type
----------------------------------------- -------- ----------------------------
TRANSACTION_ID NOT NULL NUMBER
SESSION_ID NOT NULL NUMBER
RESPONSIBILITY_APPLICATION_ID NUMBER
RESPONSIBILITY_ID NUMBER
SECURITY_GROUP_ID NUMBER
MENU_ID NUMBER


1 select rowid,transaction_id,session_id
2 from icx_transactions
3* where transaction_id='746007924'
SQL> /

ROWID TRANSACTION_ID SESSION_ID
------------------ -------------- ----------
AAAaZsAGeAAAIsjAAU 746007924 499638533
AAAaZsAAbAAAHb4AAV 746007924 888513258

SQL> delete icx_transactions
2 where rowid='AAAaZsAGeAAAIsjAAU'
3 /

1 row deleted.

SQL> commit;

Commit complete.

Failed worker was restarted through adctrl and it went fine.

#adctrl

AD Controller Menu
---------------------------------------------------

1. Show worker status

2. Tell worker to restart a failed job

3. Tell worker to quit

4. Tell manager that a worker failed its job

5. Tell manager that a worker acknowledges quit

6. Restart a worker on the current machine

7. Exit
Enter your choice [1] : 1


Control
Worker Code Context Filename Status
------ -------- ---------------------- -------------------- --------------
1 Done AutoPatch R115 icxwtab.odf FAILED
2 Done AutoPatch R115 Wait
3 Done AutoPatch R115 Wait
4 Done AutoPatch R115 Wait
5 Done AutoPatch R115 Wait
6 Done AutoPatch R115 Wait
7 Done AutoPatch R115 Wait
8 Done AutoPatch R115 Wait
9 Done AutoPatch R115 Wait
10 Done AutoPatch R115 Wait
11 Quit AutoPatch R115 Wait
12 Quit AutoPatch R115 Wait
13 Quit AutoPatch R115 Wait
14 Quit AutoPatch R115 Wait
15 Quit AutoPatch R115 Wait
16 Quit AutoPatch R115 Wait

Review the messages above, then press [Return] to continue.