Responsibility not shown in Home Page(Navigator)

I need to use XML Publisher Administrator but the responsibility is not shown in the Navigator panel. While checking the responsibilities of SYSADMIN user, it is already assigned but still it is not shown.

In such case follow this post to solve the issue.

If still the issue remains the same, then proceed with the below steps.

1. First check the responsibility is end-dated or not.

If it is end-dated, then remove end-date, clear the functional cache, logout, login and check.

If it is not end-dated, then follow the below steps.

Go to Responsibility -> Define form, query the specific responsibility, then change the value of "Available From"  to 'Oracle Applications' if it has any other options selected.

Now save the changes made, clear the functional cache as below and re-login and check the status. This time the responsibility will be shown in the Navigator.

Ref: E-Business Suite : Responsibilities Are Not Visible When End Date Is Removed (Doc ID 1926095.1)


                            ****************************நன்றி****************************

Reclaim space from TEMP Tablespace

In this post we'll see how to reclaim unused space from TEMP tablespace of Oracle database.

My Environment:
DB : Oracle 12c (12.1.0.2.0)

First check the tablesapce usage of TEMP.

Here the above SS shows free space is 0. This should not be the case. So we need to check the space used by TEMP segments from different views.

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE",  FREE_SPACE/1024/1024 "FREE_SPACE" from dba_temp_free_space;

Select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 "TABLESPACE_SIZE", ALLOCATED_SPACE/1024/1024 "ALLOCATED_SPACE", FREE_SPACE/1024/1024 "FREE_SPACE"  from dba_temp_free_space;

So now it's clear there is some free space available with the TEMP tablespace.

Now we'll try to reclaim the unused space from the temp tablespace by using shrink method.

In the above SS, I tried to shrink the space of TEMP1 tablespace upto 500M, so it errors out since it uses more data than the instructed size.

This time I tried to give the actual size and the least possible size of the tablespace. Now it worked well on the temp tablespace TEMP2.

Now we can compare the sizes of the temp tablespaces TEMP1 and TEMP2 before and after the shrink operation, to understand the difference in sizes of the tablespaces.

In tablespace TEMP1 there is no difference in size but on TEMP2 the  shrink operation has shown its effect and resized the table to 1GB from 110GB.

Using this technique we can reclaim the unused space from TEMP tablesapces.

Note: Shrink operation will be time consuming if the tablespace is large tablespace. Check the size of the tablespace and then perform the shrink operation.


                         ****************************நன்றி****************************

Roll Forward a Standby database using Compressed backupset

In this post we'll see about Roll forwarding a physical standby database using a compressed backupset in a detailed step by step process.

Identifying the issue

For some reasons logs are not getting applied in my standby site. So I follow the below steps to identify the issue.

@Primary

SQL> select name,database_role,open_mode,protection_mode from v$database;

Now we should Identify which datafile of standby database is out of sync with Primary database by using the below query.

SQL> col SUBSTR(HXFNM,1,40) for a40

SQL> set lines 200 pages 250

SQL> select HXFIL File_num,substr(HXFNM,1,40),fhscn from x$kcvfh;

SQL> select current_scn from v$database;

@Standby

Stop the MRP process

SQL> alter database recover managed standby database cancel;

SQL> select name,database_role, open_mode, protection_mode from v$database;

Run the same query we executed on Primary here at the standby for crosschecking purpose.

Now we can see datafile 109 is shown size zero and there is no name for that datafile which means this particular file is out of sync with primary database. Also check this entire list with the output obtained from Primary database to identify what other files are out of sync by comparing the FHSCN value.

Confirming the Issue

@Standby

SQL> col name for a50

SQL> select file#, name, status from v$datafile where name like '%UNNAMED%';

@Primary

SQL> col name for a50

SQL> select file#,name,status from v$datafile where file#=109;

Backup & Restore

Now backup the particular datafile '109' and restore the same on the standby database.

rman target /

RMAN> backup datafile 109 format 'arbitrary path/df109_pr.bk' tag 'PRIMARY_109';

If suppose more than 1 datafile is out of sync, backup those datafiles as below

RMAN> backup datafile <missing file number>, <missing file number>, <missing file number>, <missing file number> format 'arbitrary path/df109_pr.bk' tag 'PRIMARY_%U';

@Standby

rman target /

RMAN> catalog backuppiece 'arbitrary path/df109_pr.bk';

RMAN> list backuppiece 'arbitrary path/df109_pr.bk';

RMAN> list backup of datafile 109;

RMAN> run

2> {

3> set newname for datafile 109 to 'DBF file path/apps_undots1.399.1008589393';

4> restore datafile 109;

5> }

In case of muliple files are out of sync and need to restore the same, execute the script as below.

rman target /

RMAN> catalog start with 'arbitrary path';

RMAN> run

2> {

3> set newname for datafile X to 'DBF file path/filename';

4> set newname for datafile Y to 'DBF file path/filename';

5> set newname for datafile Z to 'DBF file path/filename';

6> restore datafile X,Y,Z;

7> }

 If you are using ASM storage, no need to give datafile name and all. Diskgroup name is enough.

For eg : set newname for datafile X to '+Diskgroup';

Now we have restored the datafile. Now run a report schema and check the status of that datafile.

RMAN> report schema;

Now the name is assigned but it is not recognized by the standby database. To overcome this we need to copy the datafile.

sqlplus '/as sysdba'

SQL> show parameter standby_file_management;

SQL> alter system set standby_file_management='AUTO';

rman target /

switch datafile 109 to copy;

In case of multiple datafiles has been restored, then switch all those datafiles. Then run a report to confirm all the datafiles are identified by the standby database and if it is found to be successful, then Roll-Forward the standby database to same point-in-time as primary database by using a compressed backupset.

report schema;

Now the datafile is known to the database.

Roll-Forward (Compressed Backupset)

rman target sys/password

RMAN> recover database from service <Net service name> noredo using compressed backupset;

Now restore the control file from primary database.

SQL> select name,open_mode,database_role from v$database;

SQL> shut immediate;

SQL> startup nomount;

rman target sys/password

RMAN> restore standby controlfile from service prod;

RMAN> alter database mount;

To confirm all the datafiles are fine and known to the database, run a report.

RMAN> report schema;

Now it is clear that the datafiles marked in red are not known to the database. To overcome this we need to switch and copy the database.

Switch & Copy

First check the incarnation on both the databases.

RMAN> list incarnation;

Now catalog the datafiles at the standby site and then switch the database.

RMAN> catalog start with '+DATA/PROD/DATAFILE/';

RMAN> catalog start with '+DATA/ORADATA/PROD/';

RMAN> switch database to copy;

Since none of the datafiles are cataloged, database copy is failed. The thing should once all the unknown datafiles are cataloged to the database. To achieve this catalog the diskgroup rather than specifying the exact location of the datafiles.

rman target /

RMAN> catalog start with ‘+DATA’;

RMAN> switch database to copy;

Still the switching is failed!. So we need to check the datafile status.

SQL> set lines 1000

SQL> set pages 1000

SQL> col name for a55

SQL> col creation_time for a20

SQL> col checkpoint_time for a20

SQL> select file#,name,creation_time,checkpoint_time,checkpoint_change# from v$datafile_copy;

Since it is not clear, we need to identify which are the datafiles needs to be switched before switching the database.

SQL> set pages 1000 lines 200

SQL> select 'switch datafile '|| file#||' to copy;' from v$datafile_copy where file#<>0 and name is not null;

rman target /

RMAN> switch datafile 47 to copy;

RMAN> switch datafile 43 to copy;

.

.

.

.

RMAN> switch datafile 109 to copy;

RMAN> report schema;

RMAN> list copy of database;

RMAN> switch datafile 98 to copy;

Recover the database

sqlplus '/as sysdba'

SQL> recover standby database;

Mostly at this recover point, issue should be resolved. In my case it is differenet. If you also get similar issue, kindly proceed with the remaining steps, else start the MRP process, perform a couple log switches at primary and check those logs got applied at the standby database or not.

Using Incremental Backup

SQL> col current_scn format 999999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> set numwidth 50

SQL> select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

@Standby

@Primary

Choose the least scn value from the above result

rman target /

RMAN> BACKUP INCREMENTAL FROM SCN 12222566161924 DATABASE FORMAT '/ebsprd/oracle/STDBY_BKP/ForStandby_%U' tag 'FORSTANDBY';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/STDBY_BKP/ForStandbyCTRL.bck';

@Standby

rman target /

RMAN> CATALOG START WITH '/backup/STDBY_BKP/';

RMAN> RECOVER DATABASE NOREDO;

rman target /

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/backup/STDBY_BKP/ForStandbyCTRL.bck';

RMAN> SHUTDOWN;

RMAN> STARTUP MOUNT;

Now check the incarnation at both the sides.

RMAN> CATALOG START WITH '';

@Primary

SQL> col NAME for a55

SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 12222566161924;

@Standby

SQL> col NAME for a55

SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 12222566161924;

SQL> col CURRENT_SCN format 999999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> set numwidth 50

SQL> select min(checkpoint_change#) from v$datafile_header;

Minimum checkpoint value is '0'. This is expected since we've restored the controlfile. So now we need to backup, restore, recover and copy those files(111, 112) alone on the standby database.

rman target /

RMAN> backup datafile 111 format '/backup/STDBY_BKP/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup datafile 112 format '/backup/STDBY_BKP/ForStandby_%U' tag 'FORSTANDBY';

RMAN> backup current controlfile for standby format '/backup/STDBY_BKP/ForStandbyCTRL.bck'; 

@Standby

Copy the backup files to standby and then,

RMAN> CATALOG START WITH '/backup/STDBY_BKP/';

RMAN> shutdown;

RMAN> startup nomount;

RMAN> restore standby controlfile from '/backup/STDBY_BKP/ForStandbyCTRL.bck';

RMAN> alter database mount;

RMAN> CATALOG START WITH '/backup/STDBY_BKP/';

RMAN> list backuppiece '/backup/STDBY_BKP/ForStandby_vlu2trvp_1_1';

RMAN> list backuppiece '/backup/STDBY_BKP/ForStandby_vku2tru8_1_1';

RMAN> list backup of datafile 111;

RMAN> list backup of datafile 112;

RMAN> run

2> {

3> set newname for datafile 111 to '+DATA';

4> set newname for datafile 112 to '+DATA';

5> restore datafile 111,112;

6> }

RMAN> CATALOG START WITH '+DATA/ORADATA/PROD/';

RMAN> CATALOG START WITH '+DATA/PROD/DATAFILE/';

RMAN> CATALOG START WITH '+DATA/PRODDR/DATAFILE/';

Now copy database.

List the database to check which files needs to be copied.

sqlplus '/as sysdba'

SQL> select file#,error from v$datafile_header where length(error)>=1;

SQL> set pages 150

SQL> select file# from v$datafile_header where length(error)>=1;

Now switch all those datafiles

rman target /

RMAN> switch datafile 39,40,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64 to copy;

RMAN> switch datafile 65,66,67,68,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89 to copy;

RMAN> switch datafile 90,91,92,93,94,95,96,97,98,99,100,101,102,103,104 to copy;

RMAN> switch datafile 105,106,107,108,109,110,111,112 to copy;

Now run the same query again to confirm, still any file needs to be copied.

rman target /

RMAN> RECOVER DATABASE NOREDO;

Now check the status of log groups and clear them.

SQL> set lines 1000

SQL> select * from v$log;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Now start the MRP process.

sqlplus '/as sysdba'

SQL> alter database recover managed standby database disconnect;

Ref : Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1)

                      ****************************நன்றி****************************