Thursday, January 29, 2015

ORA-65026 XML metadata file error : LPX-00202: could not open

In the process of converting a non-CDB database to a PDB database, once you describe a non-CDB database into and xml file and then try creating a PDB using this xml file, your CREATE PLUGGABLE DATABASE command may fail with this error message.

SQL> create  pluggable database pdb2 using '/u01/app/oracle/noncdb.xml' copy  FILE_NAME_CONVERT=('+DATA','+NEWDATA');
create  pluggable database pdb2 using '/u01/app/oracle/noncdb.xml' copy  FILE_NAME_CONVERT=('+DATA','+NEWDATA')
*
ERROR at line 1:
ORA-65026: XML metadata file error : LPX-00202: could not open
"/u01/app/oracle/noncdb.xml" (error 200)

There could be 2 main reasons for this error message. Either you have provided wrong name/path of the xml file, or you don’t have permissions on this xml file.

To solve the issue, double check if you have provided wrong name (also check case sensitivity on UNIX based platforms) or path, and also confirm that your OS user has permissions to read this xml file.

Tuesday, January 27, 2015

OEM 12c Job Failing With "ERROR: Failed to create command process"

I received OEM alert that one of my OEM 12c Cloud Control jobs is failing with message Error: Failed to create command process. Job activity page was showing details as follows

















I realized that “perl” path in the “output log” had some problem, as you can see that back slash is missing after drive letter “d:”. After seeing this, I had a little idea about why job failed. But I really had no idea why automatically this change happened and job started failing automatically. Anyway, I went on to fix this and following is the method to fix this issue if you face any problem like this.

Log into the OEM Cloud Control as Administrator
Click on Setup -> Manage Cloud Control -> Agents












From next screen, click on the agent which was responsible for execution of this job on the target. It will take you to the agent’s home screen.

Now from agent's home screen, click Agent -> Target Setup -> Monitoring Configuration
















On next screen we see that back slash is missing from the driver letter.  Somehow OEM is showing forward slashes in the path which should be back slash in case of Windows. I did not change existing forward slashes, jut corrected the path by adding backslash after “d:”. Click OK.
















Once we save by clicking OK, and then reload the page, we see that OEM has started showing all correct back slashes in all paths













After that, re-execution of job should complete successfully.

Monday, January 26, 2015

ORA-01031 insufficient privileges during switchover using DGMGRL

Problem
If you have Dataguard broker configured in your Dataguard environment and you are doing a switchover using DGMGRL, you may see ORA-013031 as you can see in the following.

[oracle@SGPvGMSIMDG01 ~]$ dgmgrl sys /
DGMGRL> switchover to mydb_prod
Performing switchover NOW, please wait...
New primary database "mydb_prod" is opening...
Operation requires shutdown of instance "mydb" on database "mydb_stby"
Shutting down instance "mydb"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        shut down instance "mydb" of database "mydb_stby"
        start up and mount instance "mydb" of database "mydb_stby"

The reason for this error is logging into the DGMGRL using OS authentication instead of by supplying the password.

Solution
You should always log in as user sys with password while logging in to DGMGRL utility before initiating any maintenance tasks on your Dataguard. Following is the example

[oracle@SGPvGMSIMDG01 ~]$ dgmgrl sys/password
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - MYDBDG

  Protection Mode: MaxPerformance
  Databases:
    mydb_prod - Primary database
    mydb_stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to mydb_stby
Performing switchover NOW, please wait...
New primary database "mydb_stby" is opening...
Operation requires shutdown of instance "mydb" on database "mydb_prod"
Shutting down instance "mydb"...
ORACLE instance shut down.
Operation requires startup of instance "mydb" on database "mydb_prod"
Starting instance "mydb"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "mydb_stby"
DGMGRL>

Sunday, January 25, 2015

ORA-01031 While Creating or Accessing a View

ORA-01031: insufficient privileges

If your CREATE VIEW, CREATE MATERIALIZED VIEW or SELECT statement on a view is failing with this error message, it means that you are using a table in your view which is in another schema and there are some privilege issues. Reason for this error could one of the following

Reason: You don’t have SELECT privilege on this table which you are using in your view (while creating or accessing the view).
Solution: Grant SELECT privileges on this table to your schema before you can create a view on this table, or you can access a view created on this table.


Reason: You have been granted SELECT privilege on this table form different schema, but this privilege was not granted directly, but through a role.
Solution: To use a table from a different schema; in your view, you need to have SELECT privilege granted directly to you, not through a role. Privilege should be granted as follows

GRANT SELECT ON <schema_name>.<table_name> TO <your_schema_name>; 

Saturday, January 24, 2015

ORA-00942 table or view does not exist

ORA-00942:
table or view does not exist
This is one of the most common errors which end users faces, returned by the Oracle RDBMS, and the meanings of this error message is that table, view, materialized view or cluster you are trying to access (SELECT, INSERT. UPDATE or DELETE). There are several different reasons for this error being returned, I will be discussing a few of them in the following and would try to update this document every time I find a new reason for this error message. When I would refer here “Table”, it would mean table, view, materialized view or cluster, unless explicitly either of these objects type is mentioned.

Reason: You have typed the table name wrongly while accessing it.
Solution: Check the name of the table you are trying to access and correct it.

Reason: Table is in another schema and you have not prefixed the table name with the schema name.
Solution: Either create a synonym in your schema for this table which is in a different schema which you are trying to access, or prefix the schema name with the table name while accessing it.

Reason: Table is in another schema and you have typed schema name wrongly while prefixing schema name with the table name.
Solution: Check the schema name and correct it if you have typed schema name wrongly while prefixing it with the table name.

Reason: Table is in another schema and you don’t have any right/privilege on this table to access it.
Solution: Grant appropriate privileges on the table in another schema to the schema which is trying to access this table


Reason: This error can be seen during import process. The reason is that you are importing a table which has a foreign key constraint on it and its parent table was not imported along with this child table. During last step of import when constraints are enabled, constraint validation will fail with this error because parent table was not found during the validation phase of this constraint.
Solution: Export parent and child tables together and then also import them together. If you are looking to import only child table, export the table with option CONSTRAINTS=N so that foreign key constraint is not exported. Lastly, you can simply ignore this error if it does not hurt you because import of data has already been completed successfully.
 

Wednesday, January 21, 2015

Error 1031 received logging on to the standby

In a Dataguard environment, if you see messages similar to the following in the alert log file of your Primary database, there could be multiple reasons for these errors.
#########################################
Error 1031 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'mydb_standby'. Error is 1031.
#########################################
OR
#########################################
Error 1031 received logging on to the standby
FAL[server, ARC2]: Error 1031 creating remote archivelog file 'mydb_standby'
FAL[server, ARC2]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance mydb - Archival Error. Archiver continuing.
#########################################
OR
#########################################
Wed Jan 21 13:22:49 2015
Error 1031 received logging on to the standby
Errors in file /u01/app/oracle/diag/rdbms/mydb_prim/mydb/trace/mydb_arc1_2334.trc:
ORA-01031: insufficient privileges
FAL[server, ARC3]: FAL archive failed, see trace file.
#########################################


In my experience, there are 2 major causes of these errors. I will be discussing both in the following
Difference in Password file
This is mandatory to have same password file on both primary and standby database server. You should make sure that once you build your standby database, you copy the password file from primary database to the standby database. There could also be a possibility that password file on standby server is accidentally deleted or you might have recreated the passwordfile using orapwd utility - in either of the case, you can right away copy the passwordfile from primary to the standby database server. Location of password file is %ORACLE_HOME%\database on Windows platform and $ORACLE_HOME/dbs on Unix based platforms.
If you find that this is the cause of the error messages, you will also observe an archive gap at the standby database because primary database authentication is failing on standby database and logs are not being shipped.
You will also need to make sure that REMOTE_LOGIN_PASSWORDFILE is set to SHARED or EXCLUSIVE on both primary and standby database.
In case or RAC, you can copy password file from any node and paste it to all standby nodes and rename the password file to match the name of the particular standby instance.
Difference in password file may also cause to cause Error 1017 to appear in the alert log file.

Insufficient Resources on the Primary Server
If your primary server is lacking in resources (CPU is choking or RAM is depleted), you may see this error message in your alert log file of primary database. In this case, you would need to make sure the availability of resources on the primary server because lacking in sufficient resources may also affect normal operations of your primary database server.
If errors are because of this reason, usually there are no archive gaps. These messages only show that primary database is having difficulty in logging in to the standby database

Tuesday, January 20, 2015

Stamp Disks on Windows for ASM

For both 11g and 12c, when we start the installer for the Grid Infrastructure installation, and select either “Install and configure Oracle Grid Infrastructure for a Cluster” or “Install and configure Oracle Grid Infrastructure for a Standalone Server” option on the first screen




























later we see a screen where we need to select disk(s) for the creation of ASM diskgroups. As you see in the following screenshot

As you see in the above e screenshot, there is no disk visible in the list which we can select. The reason is because we have not done disk stamping which is required to be done on Windows platform; before we can use disks for the creation of diskgroup(s). To “Stamp Disk”, first we need to identify the disks, initialize them, and then create logical partitions.

Initialize the Disks
Right click on “My Computer” and click “Manage” 


























Now click on “Disk Management”. If a box pops up to initialize the disks, select the appropriate disks and then select “MBR (Master Boot Record)” and click OK. 


























Now the disks have been initialized and ready for partitions creation. You may proceed with this GUI and create partitions after right clicking on each dist, your you can use DISKPART utility to create the disk partitions. First you need to create an extended partition and then logical partition.



























Partition the Disks using DISKPART utility
Open a command line window and type “diskpart” - it will take us into DISKPART command line tool to partition the disks. Issue “list disk” command to view all the disks.

















Issue “Select disk <disk_number>” command to select the disk, then create extended partition, and then logical partition; as shown in this screenshot.

















We will repeat same steps for all the disks which we want to use for ASM.

Stamp Disks
Now we need to come back to installation screen and click on “Stamp Disk” button.




























When we click on “Stamp Disk” button; it actually initiates asm tool (which can also be initiated manually using %ORACLE_HOME%\bin\asmtoolg.exe after the installation completes and if we want to stamp the disks in future).
Click Next on first screen of the wizard.


























Next screen will automatically list all the disks available for stamping. For this example, I selected all the available disks. Non available disks can be seen in grey and cannot be selected. Click the checkbox and provide any text which you want to be prefixed with the stamped disks. It is better to specify the name of our disk group here so that we can easily identify which disks belong to which diskgroup. Click Next.


























Click Next

























Click Finish

























And now we see that these stamped disks are listed in the installation screen which can now be selected to create the diskgroup.




























Related Articles