Moving Control File into ASM

Steps to move your db controlfile into ASM:-

1. Check the parameter on control_files to see current path.

2. Set control_files parameter in db to new path.

    alter system set control_files='/DEV/+VMU01','/DEV/+PSO01' scope=spfile;

3. Now -
     shutdown immediate;
     startup nomount;

4. Using RMAN, restore controlfile from old control_file.
 RMAN> restore controlfile from '+VMU01/vinaydev_streams/controlfile/current.2648.134262629';

5. Now Mount and open the instance.
 alter database mount;
     alter database open;
 

Database Flashback and Backup

Database Flashback and backup:
------------------------------------------------
If you to flashback a database to Guaranteed Restore Point(GRP) that has already been set in the database. Now the backups that were taken after the Guaranteed Restore Point(GRP) was created can no longer be used.  But, you can restore the database to a point in time before to the GRP creation.

Here are the steps:
SQL> shutdown immediate
startup mount
flashback database to restore point VINAYDEV;

Total System Global Area  424647870 bytes
 Fixed Size                  5514791 bytes
 Variable Size             328358173 bytes
 Database Buffers           45679529 bytes
 Redo Buffers                5892829 bytes

Database mounted.
 SQL>
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

Now we can recover and restore the database from backup that was taken prior to the GRP was created-
Recovery Manager: Release 11.1.0.7.0 - Production on Thu Nov 8 09:16:45 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: VINAYORD (DBID=251753272, not open)

RMAN> run {
2> restore database;
3> recover database;
4> }

Starting restore at 08-NOV-2014
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=252 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/VINAYORD/vinaydev_streams01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/VINAYORD/express01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/VINAYORD/apexdb_01.dbf
channel ORA_DISK_1: reading from backup piece /VINAY/DEVBKP/VINAYORD/rman/datafile_09/df_VINAYORD_08Nov14_0959_321_2_dbf.rmn
channel ORA_DISK_1: piece handle=/VINAY/DEVBKP/VINAYORD/rman/datafile_09/df_VINAYORD_08Nov14_0959_321_2_dbf.rmn tag=VINAYORD_INCR. LEV 0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
.....
.....
.....
channel ORA_DISK_1: piece handle=/VINAY/DEVBKP/VINAYORD/rman/datafile_09/df_VINAYORD_08Nov08_0958_320_2_dbf.rmn tag=VINAYORD_INCR. LEV 0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 08-NOV-2014

Starting recover at 08-NOV-2014
using channel ORA_DISK_1


starting media recovery


archived log file name=/VINAY/DEVBKP/DEV/ARCH/1_1_736684842.dbf thread=1 sequence=1
archived log file name=/VINAY/DEVBKP/DEV/ARCH/1_2_736684842.dbf thread=1 sequence=2
archived log file name=/VINAY/DEVBKP/DEV/ARCH/1_3_736684842.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:02
Finished recover at 08-NOV-2014

RMAN> sql 'alter database open';
now,alter database open.

Note - Always try your work in lower environments first. Never try this or any work in Production/higher environments.

This is just for knowledge sharing.
 

Changing Control File location on Database and Re-Creating Control Files

Changing the location of Control File:

SQL> ALTER SYSTEM SET control_files='E:\oracle\oradata\VINAYDEV\CONTROL02.CTL','
E:\ORADATA\VINAYDEV\CONTROL01.CTL' SCOPE=SPFILE;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

move the physical file on OS-(differenet OS examples):
windows- cut , paste and rename the file accordingly.
SQL> HOST MOVE E:\oracle\oradata\VINAYDEV\CONTROL02.CTL E:\ORADATA\VINAYDEV\CONTROL01.CTL
$ mv E:\oracle\oradata\VINAYDEV\CONTROL02.CTLE:\ORADATA\VINAYDEV\CONTROL01.CTL

SQL> startup
ORACLE instance started.

Total System Global Area 3423965184 bytes
Fixed Size                  2257984 bytes
Variable Size            1862273984 bytes
Database Buffers         1543503872 bytes
Redo Buffers               15929344 bytes
Database mounted.
Database opened.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Re-Creating Control Files:

Since this file is heart of a Oracle Database, and unfortunately if anything goes wrong below are the steps to re-create the control files-

CREATE CONTROLFILE REUSE DATABASE "vinaydev" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 32
    MAXINSTANCES 1
    MAXLOGHISTORY 449
LOGFILE
  GROUP 1 'E:\oracle\oradata\VINAYDEV\REDO01.LOG'  SIZE 50M,
  GROUP 2 'E:\oracle\oradata\VINAYDEV\REDO02.LOG'  SIZE 50M,
  GROUP 3 'E:\oracle\oradata\VINAYDEV\REDO03.LOG'  SIZE 50M
# STANDBY LOGFILE
DATAFILE
  'E:\oracle\oradata\VINAYDEV\SYSAUX01.DBF',
  'E:\oracle\oradata\VINAYDEV\SYSTEM01.DBF',
  'E:\oracle\oradata\VINAYDEV\UNDOTBS01.DBF',
  'E:\oracle\oradata\VINAYDEV\USERS01.DBF'
CHARACTER SET WE8MSWIN1252;

SQL>SELECT VALUE FROM V$PARAMETER WHERE NAME = 'control_files';
- will show the updated paths.

Reset OC4J Password

Retset  OC4J instance password

1.      Log into the Application Sever

2.      CD E:\oracle\ora10gASR3\j2ee\instance_name\config

3.      Edit system-jazn-data.xml with text editor

Locate OC4Jadmin and update credentials line

  <name>oc4jadmin</name>

  <display-name>OC4J Administrator</display-name>

  <guid>625FCC40BD5811DDBFD58391AB1EFB4C</guid>

  <description>OC4J Administrator</description>                                                                           

<credentials>{903}twsacWylQXKUDa42787BEBCEEcAnFuWy2NmHarVEBHM=</credentials>

With new password

</credentials>!new_password_321</credentials>

- The exclamation point ensures that the password will be encrypted in the configuration file.

4. Save your changes and exit the system-jazn-data.xml file

5.       Restart the instance

$ORACLE_HOME/opmn/bin/opmnctl shutdown
$ORACLE_HOME/opmn/bin/opmnctl startall

OR

Restart the instance separately  

$ORACLE_HOME/opmn/bin/opmnctl restartproc process-type=home
$ORACLE_HOME/opmn/bin/opmnctl restartproc process-type=oc4j_soa
$ORACLE_HOME/opmn/bin/opmnctl restartproc process-type=HTTP_Server

Full DB Export Failing With EXP-00008: ORA-01031 and ORA-06512: EXP-00085:

Error while Full DB Export:

. exporting post-schema procedural objects and actions

EXP-00008: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2296
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 52
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.grant_exp for object 358534
EXP-00008: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2296
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 52
ORA-06512: at line 1EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.grant_exp for object 358535
EXP-00008: ORACLE error 1031 encountered
ORA-01031: insufficient privilegesORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 2296
ORA-06512: at "SYS.DBMS_SCHED_JOB_EXPORT", line 52
ORA-06512: at line 1EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_JOB_EXPORT.grant_exp for object 358533.
. exporting user history table

Cause:

OCM jobs ORACLE_OCM Job MGMT_CONFIG_JOB scheduled to be running at the same time, hence export is failing. The scheduled jobs are interrupting the export completion. Oracle was able to identify this from their internal document(not available to outside users).

Solution:

SQL> select object_name, object_type, status from dba_objects where object_id in (358534,358535,358533);

-- see if this returns any rows or not. For me it did not return any values, this means that the objects with IDs 358534, 358535, 358533 do not exist in DBA_OBJECTS 

Check the owner of the jobs with statement:

connect / as sysdba
select owner, job_name, state, next_run_date, last_start_date, last_run_duration from dba_scheduler_jobs;

Than disable the jobs with following commands:

exec dbms_scheduler.disable ('<owner>.MGMT_STATS_CONFIG_JOB');
exec dbms_scheduler.disable ('<owner>.MGMT_CONFIG_JOB');

Or:

Make sure that export job and OCM jobs are not running at the same time.

OCM Jobs:
OCM jobs are default jobs that were introduced starting in Oracle11i. You can disable them and purpose of OCM is to proactively monitor Oracle environment and sending to My Oracle Support(metalink).

Error While Creating a New Oracle APEX Workspace

Error provisioning sandbox.

ORA-00001:uniqueconstraint (APEX_040200.WWV_FLOW_PROV_COMPANY_NAME) violated

Fix:

            select COMPANY_NAME from APEX_040200.WWV_FLOW_PROVISION_COMPANY ;

            select SHORT_NAME from  APEX_040200.WWV_FLOW_COMPANIES;

            delete from apex_040200.WWV_FLOW_PROVISION_COMPANY where company_name='SANDBOX';

commit;

-- this resolves the issue

Oracle APEX Errors

Issues we may encounter post installation of Oracle Application Express:

In my previous post we have seen how to install Oracle Application Express, and below are the errors that I've encountered after installation-

First time login to admin page - localhost:8080/apex/apex_admin throws error  like -

A user name and password are being requested by http://localhost:8080. The site says: “XDB”

Fix:

1) Try restarting listener and see if the issue is resolved or not.

2) Unlock xdb user and change password if necessary (default pwd is xdb)

       And try accessing the admin page.

3) If you still see the same error

Check if user ANONYMOUS is locked. If yes, unlock him and change the password if required.

SQL> alter user ANONYMOUS account unlock;

just in case, is to check whether or not APEX is installed properly. The status should show VALID.                                                                    

SQL> select comp_name, version, status  from dba_registry  where comp_name='Oracle Application Express';

  COMP_NAME

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

VERSION                        STATUS

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

Oracle Application Express

4.2.2.00.11                    VALID

User altered.

SQL> alter user ANONYMOUS identified by ANONYMOUS;

User altered.

We can also run a sql “epgstat.sql” to see what exactly is going on with XDB user, and take necessary steps.

Script location is-

Windows-  C:\app\vmummadi\product\11.2.0\dbhome_1\RDBMS\ADMIN

Unix- $ORACLE_HOME/RDBMS/ADMIN