Converting a partitioned table to nonpartitioned table

I have met with circumstance to change the partitioning option used for table. This needs to make the particular tbale nonpartioned.

I have used the DBMS_REDEFINITION package to acheive this.

1. Create a interim table with same structure as of the souce table WITHOUT partition.

2. Check the redefinition is possible using the following command on source tbale
EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'SOURCE_TABLE');

3. If no errors are reported proceed with the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/
4. Synchronize new table with interim data before index creation

BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'SOURCE_TABLE', int_table => 'ITERIM_TABLE');
END;
/

5. Create Contraints and indexes. The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors.

6. Complete the redefinition process
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'SOURCE_TABLE',
int_table => 'INTERIM_TABLE');
END;
/

Now the interim table has become the real table and their names have been switched in the data dictionary. Now perform some cleanup operations.
- Remove original table which now has the name of the interim table
DROP TABLE INTERIM_TABLE'
- Rename all the constraints and indexes to original name
- Make grants as on original table

Changing OC4J Password in Oracle Application Server 10g R3

We have Oracle Application Server webCenter Suite(10.1.3.1) installed on Linux Platform. Today i was trying to change the password of oc4jadmin user as describedin the Oracle Documentation.



- I logged in to the EM console as o44jadmin user and clicked on the 'setup' link top of the console page.

- Clikcked the 'Password' link and entered old and new passwords in the text box provided.

- Clicked Apply button to save.


The password changed sucessfully message shown at the top of the page.


Now here goes the worst part! I am not able to open any of the OC4J instance on this server. When i try to open any of the OC4j instance i am getting the following error :

"Unable to make a connection to OC4J instance [OC4J instance name] on Application Server [AS instance name]. A common cause for this failure is an authentication error. The administrator password for each OC4J instance in the Cluster must be the same as the administrator password for the OC4J instance onwhich Application Server Control is running."


So you can not simply change the password of OC4jadmin. Following is the method how i did to change the password.


- Login to the EM console as oc4jadmin.

- Click on the OC4J instance

- Click on the Administration tab of the OC4J instance

- Click on the security providers on the table

- Click on the instance level security in security providers page

- click Realms on the instance level security page

- In the jazn.com row of the Results table, click the number (for example, 3) in the Users column

- Click oc4jadmin to modify the oc4jadmin user account.

- Enter the old and new passwords on the text boxes provided.

- Click apply to save changes.

Do this for all your OC4J instance. The password must be same across all OC4J instances.
Once you are done with OC4J instances, go to the cluster topology page and click on the setup link and change the password of oc4jadmin. This oc4jadmin password must be same as the password of the OC4J instances.

Patching oracle apps with data guard

Lately we have applied a ptach on our apps instance with physical standby database.



Following are the steps used.



1. Stopped the recovery delay in standby db

2. Stopeed application tier services on production

3. Switch the log in prod db and get the last sequence#

4. verify on standby that last log applied

5. Stop recovery on standby

6. Take the apps prod instance to maintenance mode using adadmin

7. Perfoem patching

8. Re-start redo shipping and apply on standby

9. Run middle tier pre-clone on prod

10. Run DB tier pre-clone (Only if patching updates ORACLE_HOME)

11. Synchronize the application tier file system with standby (use rsync on linux)

12. When sync completes create new context file using adclonectx.pl

13. After new context file created configure file system using adconfig.pl

14. Optinally configure standby DB file system if you did step 10.

15. End.



Now the production and stnadby oracle applications instances are in sync.

Just a simple beginning

Hi everybody.......

Problems, performance, challenges and many more ....

As i am providing 24X7 support on production databases, it is a challenge to meet the customer expectations.

So i thought of logging the challenges faced in the practice as Oracle DBA and that might help others also i can get help from others!!

..........................