Recovering UNDO Tablespace

Hello guys,

Hope you all doing well.
What would you do if your UNDO tablespace is deleted physically.

Nothing will work and database would be hanged totally.

Here is the steps what you would follow to over come and made database up and running.

SQL> startup
Total System Global Area  753278976 bytes
Fixed Size                  1374724 bytes
Variable Size             385877500 bytes
Database Buffers          360710144 bytes
Redo Buffers                5316608 bytes
SQL>
SQL>
ERROR at line 1:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'D:\APP\Administrator\ORADATA\testdb\UNDOTBS01.DBF'

SQL> shutdown abort;
Oracle Instance closed.

SQL> startup mount;

Now, this time our database is in mount stage.
As of now, just go to RMAN and perform restore and recover operation for deleted UNDO tablespace.

C:\Users\Administrator>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 16 16:27:35 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTDB (DBID=2904859346, not open)
RMAN> restore tablespace undotbs1;
Starting restore at 16-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 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 00003 to D:\APP\Administrator\ORADATA\testdb\UNDOTBS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\Administrator\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2012_10_16\O1_MF_NNNDF_TAG20121016T144421_87VO9P01_.BKP
channel ORA_DISK_1: piece handle=D:\APP\Administrator\FLASH_RECOVERY_AREA\testdb\BACKUPSE
T\2012_10_16\O1_MF_NNNDF_TAG20121016T144421_87VO9P01_.BKP tag=TAG20121016T144421
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 16-OCT-12

Now, you can check UNDO tablespace to it's relevant location and you will find all is there.

So, this is time to recover the same.

RMAN> recover tablespace undotbs1;
Starting recover at 16-OCT-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-OCT-12
RMAN>

Finally just open your database.

SQL> alter database open;
Database Opened.

Great.............Needful Done.

Happy Recovery................................

Regards,
Raj Kumar

Oracle 11g R2 EM Agent Unreachable

Usually Agent Unreachable status shown when agent is not running so first try to start the agent and wait for sometime that to be reflected in OEM or if  you have changed your hostname/IP or timezone of your oracle server then follow up the following steps.

1. emctl resetTZ agent

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

Updating E:\app\Administrator\product\11.2.0\dbhome_1/rajkumar-pc_orcl/sysman/config/

emd.properties...

Successfully updated E:\app\Administrator\product\11.2.0\dbhome_1/rajkumar-pc_orcl/sy

sman/config/emd.properties.

2. Login as the em repository user and run the  script:

exec mgmt_target.set_agent_tzrgn('rajkumar-pc:3938','America/New_York')

and commit the changes

SQL> exec mgmt_target.set_agent_tzrgn('rajkumar-pc:3938','America/New_York')

SQL> commit

3. emctl status dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

https://rajkumar-pc:5500/em/console/aboutApplication

EM Daemon is not running.

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

Logs are generated in directory E:\app\Administrator\product\11.2.0\dbhome_1/COL-OR

A01_orcl/sysman/log

4. emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

https://rajkumar-pc:5500/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ...The OracleDBConsoleor

cl service is starting...................

The OracleDBConsoleorcl service was started successfully.

Enjoy.............................

"ORA-01031: insufficient privileges"

The ORA-01031: “insufficient privileges” error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group). 

There are similar permission in the Windows registry.
In windows just have to assign the ORA-DBA group to the corresponding OS user who is getting the error,

Oracle on Solaris 10 : Fixing the 'ORA-27102: out of memory' Error
Symptom:
As part of a database tuning effort you increase the SGA/PGA sizes; and Oracle greets with an
ORA-27102: out of memory error message.
The system had enough free memory to serve the needs of Oracle.
SQL> startup
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument
Diagnosis
$ oerr ORA 27102
27102, 00000, "out of memory"
// \*Cause: Out of memory
// \*Action: Consult the trace file for details

Not so helpful. Let's look the alert log for some clues.

% tail -2 alert.log
WARNING: EINVAL creating segment of size 0x000000028a006000
fix shm parameters in /etc/system or equivalent
Oracle is trying to create a 10G shared memory segment (depends on SGA/PGA sizes), but operating system (Solaris in this example) responded with an invalid argument (EINVAL) error message. There is a little hint about setting shm parameters in /etc/system.
Prior to Solaris 10, shmsys:shminfo_shmmax parameter has to be set in /etc/system with maximum memory segment value that can be created. 8M is the default value on Solaris 9 and prior versions; where as 1/4th of the physical memory is the default on Solaris 10 and later. On a Solaris 10 (or later) system, it can be verified as shown below:

% prtconf | grep Mem
Memory size: 32760 Megabytes

% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
Now it is clear that the system is using the default value of 8G in this scenario, where as the application (Oracle) is trying to create a memory segment (10G) larger than 8G. Hence the failure.
So, the solution is to configure the system with a value large enough for the shared segment being created, so Oracle succeeds in starting up the database instance.
On Solaris 9 and prior releases, it can be done by adding the following line to /etc/system, followed by a reboot for the system to pick up the new value.

set shminfo_shmmax = 0x000000028a006000

However shminfo_shmmax parameter was obsoleted with the release of Solaris 10; and Sun doesn't recommend setting this parameter in /etc/system even though it works as expected.
On Solaris 10 and later, this value can be changed dynamically on a per project basis with the help of resource control facilities . This is how we do it on Solaris 10 and later:

% prctl -n project.max-shm-memory -r -v 10G -i project 3

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      10.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
Note that changes made with the prctl command on a running system are temporary, and will be lost when the system is rebooted. To make the changes permanent, create a project with projadd command and associate it with the user account as shown below:

% projadd -p 3  -c 'eBS benchmark' -U oracle -G dba  -K 'project.max-shm-memory=(privileged,10G,deny)' OASB
% usermod -K project=OASB oracleFinally make sure the project is created with projects -l or

cat /etc/project commands.
% projects -l
...
...
OASB
        projid : 3
        comment: "eBS benchmark"
        users  : oracle
        groups : dba
        attribs: project.max-shm-memory=(privileged,10737418240,deny)

% cat /etc/project
...
...
OASB:3:eBS benchmark:oracle:dba:project.max-shm-memory=(privileged,10737418240,deny)
With these changes, Oracle would start the database up normally.

SQL> startup
ORACLE instance started.
Total System Global Area 1.0905E+10 bytes
Fixed Size                  1316080 bytes
Variable Size            4429966096 bytes
Database Buffers         6442450944 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.

SPM: SMB space usage (810418176) exceeds 10.000000% of SYSAUX size (6492782592).

SQL Plan baselines are strored in the tablespace SYSAUX and by default, at most 10 percent of the tablespace can be used for this purpose. You can get the current value throught the data dictionary view DBA_SQL_MANAGEMENT_CONFIG.

SQL> select parameter value 
from dba_sql_management_config 
where parameter_name='SPACE_BUDGET_PERCENT';


PARAMETER_VALUE
---------------
         10

When this default value (default threshold) is exceeded, a warning message gets written in the alert log of the database, it is not any error but just a warning so don't bothered about it. If it occurs intermittently, you can change(increase) its default value using the procedure CONFIG in the package DBMS_SPM.

Supported values for its threshold is between 1 percent to 50 percent. Below example illustrate how to change it to 20 percent.

If PARAMETER_VALUE is set to NULL then default value is restored, i.e. 10 percent.

SQL> dbms_spm.configure(
parameter_name => 'space_budget_percent',
parameter_value => 20
);

After successful execution of procedure restart your oracle instance and now you will notice that there would be no more such entry in your alert log file.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 5361582080 bytes
Fixed Size                  2185112 bytes
Variable Size            1728055400 bytes
Database Buffers         3623878656 bytes
Redo Buffers                7462912 bytes
Database mounted.

SQL> alter database open;
Database altered.

Hope it wold help someone....!!

Database object and it's corresponsing datafiles, owner, tablespace

Here you could find which database object is located in which datafile of it's tablespace and it's owner, object name, object type and it's respective size:

SQL> select t.ts#, t.name as Tablespace_name, d.name as Datafile, s.owner, s.segment_name, s.segment_type, s.bytes/1024/1024 as "MB"
from v$tablespace t, v$datafile d, dba_segments s
where t.ts#=d.ts#
and t.name=s.tablespace_name
and s.owner='SCOTT';

TS# TS_NAME DATAFILE                                  OWNER  SEGMENT_NAME SEGMENT_TYPE Size(MB)
--- ------ ------------------------------------------ ------ ------------ ------------ ----------
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  DEPT         TABLE        .0625
4 USERS    E:\APP\RAJKUMARN\ORADATA\ORCL\USERS01.DBF SCOTT  EMP          TABLE        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  PK_EMP       INDEX        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  PK_DEPT      INDEX        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  SALGRADE     TABLE        .0625

SQL>