Category Archives: Oracle

Oracle

Recreating OEM in 11g on MS Windows

Leave a comment

There are multiple reasons to recreate your Enterprise Manager installation on a Oracle RDBMS system. The following procedures provide the steps to accomplish this task on a MS Windows OS.

1. Remove SYSMAN from database if he exists by using emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -deconfig dbcontrol db -repos drop

EXAMPLE:
—————————————
C:\Windows\system32>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 20, 2013 10:38:10 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
———————————————————————-
WARNING : While repository is dropped the database will be put in quiesce mode.
———————————————————————-
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:38:43 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_38_10.log.
Apr 20, 2013 10:38:45 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 20, 2013 10:39:17 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) …
Apr 20, 2013 10:40:29 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:40:30 AM

C:\Windows\system32>
——————————————-

2. Create OEM repository by using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -repos create

EXAMPLE:
——————————————-
C:\Windows\system32>emca -repos create

STARTED EMCA at Apr 20, 2013 10:42:48 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Password for SYS user:
Password for SYS user: password

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:43:12 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_42_48.log.
Apr 20, 2013 10:43:14 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 20, 2013 10:45:30 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 20, 2013 10:45:30 AM

C:\Windows\system32>
——————————————-

3. Finally, configure EM Grid Control using the emca utility. NOTE: Open a command prompt as administrator to perform this task. You must right click on the CMD prompt and select “Run as administrator” to open the command prompt properly.

COMMAND:
—————————————
emca -config dbcontrol db

EXAMPLE:
——————————————-
C:\Windows\system32>emca -config dbcontrol db

STARTED EMCA at Apr 20, 2013 10:32:32 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl1
Listener port number: 1521
Listener ORACLE_HOME [ C:\oracle\product\11.2.0\dbhome_orcl ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl

Local hostname ……………. orcl_SERVER-PC.CATT.Net
Listener ORACLE_HOME ……………. C:\oracle\product\11.2.0\dbhome_orcl
Listener port number ……………. 1521
Database SID ……………. orcl1
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 20, 2013 10:33:00 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\cfgtoollogs\emca\orcl\emca_201
3_04_20_10_32_31.log.
Apr 20, 2013 10:33:06 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepo
sitory
INFO: Uploading configuration data to EM repository (this may take a while) …
Apr 20, 2013 10:33:41 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 20, 2013 10:33:43 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) …
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 20, 2013 10:33:51 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 20, 2013 10:34:37 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://orcl_SERVER-PC.CATT.Net:5501/em < <<<<<<<<<< Apr 20, 2013 10:34:38 AM oracle.sysman.emcp.EMDBPostConfig invoke WARNING: ************************ WARNING ************************ Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: C:/orac le/product/11.2.0/dbhome_orcl/orcl_SERVER-PC.CATT.Net_orcl/sysman/config/emkey. ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. *********************************************************** Enterprise Manager configuration completed successfully FINISHED EMCA at Apr 20, 2013 10:34:38 AM C:\Windows\system32>
——————————————-

4. Now attempt to access the EM console through the URL given in the INFO section of the last command. In this example, we access the EM console at URL:
https://orcl_SERVER-PC.CATT.Net:5501/em

Larry Catt, OCP

Oracle

ORA-27038

Leave a comment

This error is resulting from the recreation of a database in its original location without removal of all components.

CREATE DATABASE ORCL * ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00200: control file could not be created

ORA-00202: control file: ‘C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL’

ORA-27038: created file already exists

OSD-04010: <create> option specified, file already exists

 

Resolution:

Remove old control file from the original location and the flashback location.

error, Oracle

ORA-00382

Leave a comment

ORA-00382: 32768 not a valid block size, valid range [2048..16384]

This error refers to the OS having limitation on block size selection:   General quide for block sizes per OS

AIX-Based Systems,Compaq Tru64 UNIX,HP 9000 Series HP-UX,Linux Intel,Sun SPARC Solaris, (Windows NT, 95, 98, 2000): Have a DB_BLOCK_SIZE capability of 2048 to 16384

Linux, Solaris,AIX, HP, Tru64: Have a DB_BLOCK_SIZE capability of 2048 to 32768

11g, Oracle, Oracle 10g, RAC, utility

Use of rconfig to modify a single instance 11g database to RAC

Leave a comment

This article covers the conversion of a pre-existing Oracle 11g single instance database into a two node RAC. The following assumptions are made for this procedure to execute correctly:
1. Oracle 11g GRID has been installed on both RAC nodes.
2. Oracle 11g RDBMS has been installed on both RAC nodes.
3. Oracle 11g single instance database has been copied to RAC node 1 and operates correctly as a single instance database.

1. Logon to node 1 of your Oracle RAC server as the RDBMS owner.

2. Copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml

[oracle@mylinux1 dbhome_1]$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml orcl.xml
[oracle@mylinux1 dbhome_1]$

2. The sample XML file gives detailed instruction on how to edit the file for your specific use. In this example edit the following lines:

SourceDBHome = Single instance Oracle Home directory
TargetDBHome = RAC instance Oracle Home, this could be the same as the single instance Oracle Home directory
SourceDBInfo SID= SID of the Source database.
Password = SYS password
Node name= node1 name, normally this is the machine name.
Node name= node2 name, normally this is the machine name.
InstancePrefix = prefix of your RAC database, normally this is equal to the SID of your source database.
SharedStorage type=”ASM” or “OCFS”

3. Ensure that the single instance database is up and running.

[oracle@mylinux1 dbhome_1]$ ps -ef|grep smon
oracle 18508 1 0 02:12 ? 00:00:00 ora_smon_orcl
oracle 26774 7777 0 02:46 pts/1 00:00:00 grep smon
[oracle@mylinux1 dbhome_1]$

4. Execute the rconfig command and pass your edited xml file to it.
[oracle@mylinux1 bin]$ ./rconfig ./orcl.xml
Converting Database “orcl” to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Flash Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database
< ?xml version=”1.0″ ?>

Operation Succeeded

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@mylinux1 bin]$>

5. Now the database can be confirmed in rac mode on both nodes.

[oracle@mylinux1 bin]$ ps -ef|grep smon
oracle 16544 1 0 03:04 ? 00:00:00 ora_smon_orcl1
oracle 21072 7777 0 03:07 pts/1 00:00:00 grep smon
[oracle@mylinux1 bin]$

[root@mylinux2 ~]# ps -ef|grep smon
oracle 27298 1 0 03:03 ? 00:00:00 ora_smon_orcl2
root 29474 23387 0 03:07 pts/4 00:00:00 grep smon
[root@mylinux2 ~]#

This completes configuration of single instance to Oracle 11g RAC.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, error, Oracle, Oracle 10g, oracle 9i

Oracle Error ORA-32004 and ORA-00845

Leave a comment

Error:

[oracle@mylinux1 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl -sysDBAUserName larry -sysDBAPassword larry
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-00845: MEMORY_TARGET not supported on this system

Solution:

1. Logon to the database as sysdba

2. Execute the command.

SQL> show parameter memory_target;

NAME TYPE VALUE
———————————— ———– ——————————
memory_target big integer 3104M
SQL>

3. Issue the command.

SQL> alter system reset memory_target;

System altered.

SQL>

4. Shutdown the database

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

5. Startup the database.

SQL> startup open
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL>

6. Execute command:

SQL> show parameter memory_target

NAME TYPE VALUE
———————————— ———– ——————————
memory_target big integer 0
SQL>

7. re-execute

[oracle@mylinux1 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl -sysDBAUserName larry -sysDBAPassword larry
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

[oracle@mylinux1 bin]$

This completes resolution of Oracle Error ORA-32004 – ORA-00845.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, Oracle, Oracle 10g, RAC

Move Oracle RAC database into Archive log mode

Leave a comment

Moving Oracle RAC databases into and out of archive log mode requires a few more steps due to the existence of multiple instances. This procedure covers the steps required to move a non-archive log mode database into archive log mode.

1. Logon to Oracle node1 server as the oracle software owner.

2. Validate the current archive log mode of the database, by logging onto the database and issuing the [archive log list] command.


[oracle@mylinux1 ~]$ export ORACLE_SID=orcl1
[oracle@mylinux1 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 20 22:57:07 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u05/oradata/orcl/archive
Oldest online log sequence 66
Current log sequence 68

3. Move the node out of cluster mode by changing the parameter cluster_database equal to false and exit.

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@mylinux1 ~]$

4. Stop the clustered database with srvctl utility.

[oracle@mylinux1 ~]$ srvctl stop database -d orcl

5. Logon to sqlplus on node1 and startup the instance in single instance mode with mount option.

[oracle@mylinux1 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 20 22:59:08 2011

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 367476736 bytes
Fixed Size 2213376 bytes
Variable Size 293603840 bytes
Database Buffers 67108864 bytes
Redo Buffers 4550656 bytes
Database mounted.
SQL>

6. Alter the database to archive log mode.

SQL> alter database archivelog;

Database altered.

SQL>

7. Change the parameter cluster_database back to true, shutdown the instance, and exit.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

8. Use the srvctl utility to startup the clustered database.

[oracle@mylinux1 ~]$ srvctl start database -d orcl

9. Logon to sqlplus on node1 and check the archive log mode with the command [archive log list].

[oracle@mylinux1 ~]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 20 23:01:22 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u05/oradata/orcl/archive
Oldest online log sequence 66
Next log sequence to archive 68
Current log sequence 68
SQL>

This completes moving an Oracle RAC database into archive log mode.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Find the Oracle parameter causing ORA-32004 and removing

Leave a comment

During the normally life cycle of a database, multiple upgrades will occur, resulting in parameters becoming obsolete. This article shows how to determine the parameter which is obsolete and removing it.

Typical error generated by an obsolete parameter:

[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl
-sysDBAUserName larry -sysDBAPassword larry
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
[oracle@mylinux2 bin]$

1. Logon to the database as the sys user.

[oracle@mylinux2 bin]$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 01:32:07 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Execute the following SQL to find the problem

select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

SQL> select name, value from v$parameter where ISDEPRECATED=’TRUE’ and ISDEFAULT=’FALSE’;

NAME
——————————————————————————–
VALUE
——————————————————————————–
remote_os_authent
FALSE
SQL>

3. Reset this parameter

SQL> alter system reset remote_os_authent;
System altered.
SQL>

4. Stop database.

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

5. Start database.

SQL> startup
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
Database mounted.
Database opened.
SQL>

This completes removing of obsolete or deprecated Oracle parameters.

Larry Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, installer, Oracle, RAC, unix

Undo root.sh script in 11g GRID or RDBMS install

Leave a comment

If you have executed root.sh on the installation of an Oracle 11g clusterware or RDBMS install and it failed. Remove the changes made by root.sh before re-execution with the following procedure.

1. Logon as the root user.

2. Navigate to the Oracle GRID_HOME

3. Execute the following command.

/opt/app/11.2.0/grid/crs/install/rootcrs.pl -deconfig –force -verbose

This completes the undoing the execution of root.sh from an Oracle Grid Installation.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, OPatch, Oracle, Oracle 10g, oracle 9i

How to debug Oracle opatch utility

Leave a comment

The following procedure shows how to debug the Oracle opatch utility when you receive an error.

1. Execution of opatch utility results in the following error.

[oracle@mylinux1 OPatch]$ opatch lsinventory
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.6
OUI version : 11.2.0.1.0
OUI location : /u01/app/oracle/product/11.2.0/dbhome_1/oui
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2011-04-02_19-17-51PM.log

Patch history file: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch_history.txt
Inventory load failed… OPatch cannot load inventory for the given Oracle Home.
LsInventorySession failed: Unable to create patchObject

OPatch failed with error code 73
[oracle@mylinux1 OPatch]$

2. Set the environmental variable OPATCH_DEBUG equal to true.

[oracle@mylinux1 OPatch]$ export OPATCH_DEBUG=true
[oracle@mylinux1 OPatch]$

3. Re-execute your opatch utility and every action performed by opatch will be displayed in your buffer. Use the messages to determine the error.

This completes use of debug with the opatch utility.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com