Monthly Archives: November 2017

Making System tablespace Offline – Oracle

Posted in Oracle, Technology by

If you search the Oracle documentation it says that you cannot bring the system tablespace offline.
However, here is a way to do it —

– Each tablespace is assigned one or more datafile. You can bring the datafile offline.
In the below case, I have deleted the system datafile and trying to start the database.

>>
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2182592 bytes
Variable Size 335544896 bytes
Database Buffers 725614592 bytes
Redo Buffers 5595136 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘C:\ORACLE\ORADATA\WINDEV\SYSTEM01.ODF’

 

SQL> alter database datafile 1 offline;

Database altered.

and then you can bring it back up using the same datafile.

In my case, i have done a rman restore of the tablespace.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: ‘C:\ORACLE\ORADATA\WINDEV\SYSTEM01.ODF’

 

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;

Database altered.

SQL>

Advertisement

EM dbconsole not starting and giving NET HELPMSG 3547

Posted in Oracle, Technology, Uncategorized by

 

Here are the steps you can use to check if you face problems with EM DB Consle

Database version : 11.2.0.0.2 standard edition

EM DB Console not working.

1) check the status of dbconsole as follows

C:\WINDOWS\system32>emctl status dbconsole

2) Try to start the dbconsole

C:\WINDOWS\system32>emctl start dbconsole

if the dbconsole cannot be started, sometimes this problem may occur because SYSMAN and DBSNMP users are locked, so check their account status

C:\WINDOWS\system32>sqlplus / as sysdba

SQL> select username,account_status from dba_users;

USERNAME ACCOUNT_STATUS
—————————— ——————————–
OUTLN                                            OPEN
MGMT_VIEW                                 OPEN
SYS                                                   OPEN
SYSTEM                                           OPEN
DBSNMP                                         OPEN
SYSMAN                                          OPEN
DIP                                                   EXPIRED & LOCKED
ORACLE_OCM                                EXPIRED & LOCKED
APPQOSSYS                                    EXPIRED & LOCKED

so from above, its clear that both the user accounts are open

5) Check if agent is running or not

C:\WINDOWS\system32>emctl status agent

6) If the agent is not running try to start it

C:\WINDOWS\system32>emctl start agent

sometimes you get the message NET HELPMSG 2185

C:\WINDOWS\system32>NET HELPMSG 2185

The service name is invalid.
EXPLANATION

You tried to start a service that is not configured on this system.

ACTION

Check the spelling of the service name or check the configuration information for the service using
the Services option from Server Manager.

Eventually recreate the repository please remember that recreating the repository will not impact your database functionality

before recreating, make sure you have the following information with you
Database hostname
Database SID
Listener port number
password for SYS user
password for DBSNMP user
password for SYSMAN user
also, it is important that DBSNMP and SYSMAN user’s account should be opened

 

C:\WINDOWS\system32>emca -config dbcontrol db -repos recreate -silent -DB_UNIQUE_NAME WINDEV -PORT 1521 -EM_HOME C:\oracle\11g\R2 -LISTENER LISTENER -SERVICE_NAME WINDEV -SID WINDEV -ORACLE_HOME C:\oracle\11g\R2 -HOST HP -LISTENER_OH C:\oracle\11g\R2 -DBCONTROL_HTTP_PORT 1159 -DBSNMP_PWD oracle -SYSMAN_PWD oracle -SYS_PWD oracle