Recovering of tablespace without backup

We are not using recovery catalog for testing purpose.

C:\Users\jayva>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 2 13:07:42 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: ORACLE (DBID=1662593856)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORACLE are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORACLE.ORA'; # default

Deleting all the current backup.

RMAN>

RMAN> delete noprompt backup;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device type=DISK

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

4       4       1   1   AVAILABLE   DISK        C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\06NRK7ET_1_1

5       5       1   1   AVAILABLE   DISK        C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\07NRK7GL_1_1deleted backup piece

backup piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\06NRK7ET_1_1 RECID=4 STAMP=800726494 deleted backup piece

backup piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\07NRK7GL_1_1 RECID=5 STAMP=800726550 Deleted 2 objects

RMAN> list backup;

specification does not match any backup in the repository

RMAN>

Backup the fresh copy

RMAN> backup database plus archivelog;

Starting backup at 02-JAN-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=102 RECID=99 STAMP=803654842

input archived log thread=1 sequence=103 RECID=100 STAMP=803654844

input archived log thread=1 sequence=104 RECID=101 STAMP=803654851

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0DNUDJ63_1_1 tag=TAG20130102T132731 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting backup at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF

input datafile file number=00003 name=C:\APP\JAYVA\ORADATA\ORACLE\UNDOTBS01.DBF

input datafile file number=00002 name=C:\APP\JAYVA\ORADATA\ORACLE\SYSAUX01.DBF

input datafile file number=00004 name=C:\APP\JAYVA\ORADATA\ORACLE\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0ENUDJ65_1_1 tag=TAG2

0130102T132733 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

Finished backup at 02-JAN-13

Starting backup at 02-JAN-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=105 RECID=102 STAMP=803654929

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0FNUDJ8H_1_1 tag=TAG20130102T132849 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting Control File and SPFILE Autobackup at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\C-1662593856-20130102-01 comment=NONE

Finished Control File and SPFILE Autobackup at 02-JAN-13

RMAN>

Create new tablespace and new table. This new tabelspace is not included in bakcup.

SQL> create tablespace jay DATAFILE 'C:\APP\JAYVA\ORADATA\ORACLE\jay01.dbf' size 1M autoextend on next 1M maxsize 100M;

Tablespace created.

SQL> create table backup_test (a number, b number) tablespace jay;

Table created.

SQL> begin

  2  for indx IN 1 .. 1000

  3  loop

  4  execute immediate 'insert into backup_test(a,b) values (:1, :2)' using indx, indx*2;

  5  end loop;

  6  commit;

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Although we have configure autobackup for controlfile but 11g onwards controfile autobackup not happening immediately after structure change.

So instead of waiting for auto backup, we are taking manual backup.

RMAN> backup current controlfile;

Starting backup at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0HNUDJCE_1_1 tag=TAG20130102T133054 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting Control File and SPFILE Autobackup at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\C-1662593856-20130102-02 comment=NONE

Finished Control File and SPFILE Autobackup at 02-JAN-13

RMAN>

Once done with controlfile backup, we will shutdown the DB and delete the controlfile and datafiles of 'JAY' tablespace.

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 14:49:39 2013

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

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

select open_mode from v$database

                      *

ERROR at line 1:

ORA-01507: database not mounted

SQL> conn / as sysdba

Connected.

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             406848040 bytes

Database Buffers          109051904 bytes

Redo Buffers                5832704 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL>

Restoring the controlfile from autobackup.

RMAN> set DBID=1662593856

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130102

channel ORA_DISK_1: AUTOBACKUP found: c-1662593856-20130102-02

channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1662593856-20130102-02

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=C:\APP\JAYVA\ORADATA\ORACLE\CONTROL01.CTL

output file name=C:\APP\JAYVA\ORADATA\ORACLE\CONTROL02.CTL

Finished restore at 02-JAN-13

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

Once done with restoration of controlfile, we can check for all tablespaces. Size for 'JAY' it shows here 0.

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name ORACLE

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    800      SYSTEM               ***     C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF

2    500      SYSAUX               ***     C:\APP\JAYVA\ORADATA\ORACLE\SYSAUX01.DBF

3    755      UNDOTBS1             ***     C:\APP\JAYVA\ORADATA\ORACLE\UNDOTBS01.DBF

4    5        USERS                ***     C:\APP\JAYVA\ORADATA\ORACLE\USERS01.DBF

5    0        JAY                  ***     C:\APP\JAYVA\ORADATA\ORACLE\JAY01.DBF

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    20       TEMP                 32767       C:\APP\JAYVA\ORADATA\ORACLE\TEMP01.DBF

RMAN>

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/02/2013 14:53:29

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/02/2013 14:59:14

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF'

RMAN>

RMAN>

RMAN>  recover database;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/02/2013 15:02:19

RMAN-06094: datafile 5 must be restored

RMAN> restore tablespace jay;

Starting restore at 02-JAN-13

using channel ORA_DISK_1

creating datafile file number=5 name=C:\APP\JAYVA\ORADATA\ORACLE\JAY01.DBF

restore not done; all files read only, offline, or already restored

Finished restore at 02-JAN-13

RMAN> recover tablespace jay;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/02/2013 15:03:31

RMAN-06067: RECOVER DATABASE required with a backup or created control file

RMAN> recover database;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 105 is already on disk as file C:\APP\JAYVA\ORADATA\ORACLE\REDO03.LOG

archived log for thread 1 with sequence 106 is already on disk as file C:\APP\JAYVA\ORADATA\ORACLE\REDO01.LOG

archived log file name=C:\APP\JAYVA\ORADATA\ORACLE\REDO03.LOG thread=1 sequence=105

archived log file name=C:\APP\JAYVA\ORADATA\ORACLE\REDO01.LOG thread=1 sequence=106

media recovery complete, elapsed time: 00:00:05

Finished recover at 02-JAN-13

RMAN> alter database open resetlogs;

database opened

RMAN>

SQL> conn / as sysdba

Connected.

SQL> select count(1) from backup_test;

  COUNT(1)

----------

      1000

SQL>

Query to track session generating redo

set lin 180

set pagesize 2000

col INST_ID format 999

col PROGRAM format A35

col MODULE format A35

col USERNAME format A25

select s.inst_id, s.sid, serial#, program, module, username, value redo_size

from gv$session s, gv$sesstat ss, v$statname sn

where s.sid = ss.sid

and ss.statistic# = sn.statistic#

and sn.name = 'redo size'

and s.inst_id = ss.inst_id

order by redo_size ;

Manual (slient) 12c agent installation

-bash-3.2$ ./emcli login -username=sysman -password=****

Login successful

-bash-3.2$ ./emcli sync

Synchronized successfully

-bash-3.2$ ./emcli get_supported_platforms

Getting list of platforms ...

Check the logs at /pkgmnoms/oms/oracle/12.1.0.2/oms/bin/agent.log

About to access self-update code path to retrieve the platforms list..

Getting Platforms list  ...

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

Version = 12.1.0.1.0

Platform = Linux x86-64

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

Version = 12.1.0.1.0

Platform = HP-UX PA-RISC (64-bit)

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

Version = 12.1.0.1.0

Platform = HP-UX Itanium

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

Version = 12.1.0.1.0

Platform = Oracle Solaris on SPARC (64-bit)

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

Platforms list displayed successfully.

-bash-3.2$

-bash-3.2$

Once done with checking of agent, we will extract agent software to any location (/tmp in our case) and transfer the software to target machine where we need to install the agent.

-bash-3.2$ ./emcli get_agentimage -destination=/tmp -platform="Oracle Solaris on SPARC (64-bit)" -version="12.1.0.1.0"

Platform:Oracle Solaris on SPARC (64-bit)

Destination:/tmp

=== Partition Detail ===

Space free : 2 GB

Space required : 1 GB

Check the logs at /tmp/get_agentimage_2012-07-17_08-16-35-AM.log

Setting property ORACLE_HOME to:/pkgmnoms/oms/oracle/12.1.0.2/oms

calling pulloneoffs with arguments:/pkgmnoms/oms/oracle/12.1.0.2/oms/pkgmnoms/oms/oracle/12.1.0.2/updates/55/C4191F66D29C0B40E0436D2028188B2212.1.0.1.0solaris

Check this logs for more information: /pkgmnoms/oms/oracle/12.1.0.2/oms/sysman/prov/agentpush/logs

-bash-3.2$ cd /tmp

-bash-3.2$ ls -lart 12.1.0.1.0*.zip

-rw-r----- 1 oracle oinstall 286075811 Jul 17 08:16 12.1.0.1.0_AgentCore_23.zip

-bash-3.2$

Scp to target machine.

After software is copied to the AGENT_BASE location, it is needed to unzip.

At target machine:

had:/app/oracle/amddhome/agent] ls

12.1.0.1.0_AgentCore_23.zip

had:/app/oracle/amddhome/agent] ls -lart

total 558744

-rwxr-xr-x   1 oracle   dba      286075811 Jul 17 12:23 12.1.0.1.0_AgentCore_23.zip

drwxrwxrwx   2 oracle   dba           96 Jul 17 12:23 .

drwxr-xr-x   6 oracle   dba         1024 Jul 17 12:23 ..

had:/app/oracle/amddhome/agent] unzip 12.1.0.1.0_AgentCore_23.zip

Archive:  12.1.0.1.0_AgentCore_23.zip

  inflating: unzip

  inflating: agentDeploy.sh

  inflating: agentimage.properties

  inflating: agent.rsp

extracting: agentcoreimage.zip

extracting: 12.1.0.1.0_PluginsOneoffs_23.zip

had:/app/oracle/amddhome/agent]

To install the agent all needed is to agent base directory, OMS_HOST, any free port and agent registration password. In case password is not known it can be changed from OEM CONSOLE SETUP -> SECURITY -> REGISTRATION PASSWORD. Once done with all the information, script is needed to execute as below.

suzan:/app/oracle/amddhome/agent] ./agentDeploy.sh AGENT_BASE_DIR=/app/oracle/amddhome/agent OMS_HOST=devd1.come.com EM_UPLOAD_PORT=4902 AGENT_REGISTRATION_PASSWORD=oracle123

AGENT_BASE_DIR=/app/oracle/amddhome/agent

-e Validating the OMS_HOST & EM_UPLOAD_PORT

Executing command : /app/oracle/amddhome/agent/core/12.1.0.1.0/jdk/bin/java -classpath /app/oracle/amddhome/agent/core/12.1.0.1.0/jlib/agentInstaller.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller /app/oracle/amddhome/agent/core/12.1.0.1.0 /app/oracle/amddhome/agent /app/oracle/amddhome/agent -prereq

Validating oms host & port with url: http://pacdcorwdevd1.cable.comcast.com:4902/empbs/genwallet

Validating oms host & port with url: https://pacdcorwdevd1.cable.comcast.com:4902/empbs/genwallet

Return status:3

Unzipping the agentcoreimage.zip to /app/oracle/amddhome/agent ....

12.1.0.1.0_PluginsOneoffs_23.zip

Executing command : /app/oracle/amddhome/agent/unzip -o /app/oracle/amddhome/agent/12.1.0.1.0_PluginsOneoffs_23.zip -d /app/oracle/amddhome/agent

-e

Checking the ownership of agent base directory:/app/oracle/amddhome/agent

Checking for proper ownership on the agent base directory.

Checks whether the agent base directory is owned by the agent user oracle  and that its parent directory is owned by either the agent user or root.

....

Login name is : oracle & file owner is : oracle

............

Agent Base directory verification completed Successfully.

-e

Ownership check completed.

Executing command : /app/oracle/amddhome/agent/core/12.1.0.1.0/jdk/bin/java  -d64 -classpath /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/OraInstaller.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/xmlparserv2.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/srvm.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/emCfg.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/jlib/agentInstaller.jar:/app/oracle/amddhome/agent/core/12.1.0.1.0/oui/jlib/share.jar oracle.sysman.agent.installer.AgentInstaller /app/oracle/amddhome/agent/core/12.1.0.1.0 /app/oracle/amddhome/agent /app/oracle/amddhome/agent AGENT_BASE_DIR=/app/oracle/amddhome/agent AGENT_BASE_DIR=/app/oracle/amddhome/agent OMS_HOST=devd1.come.com EM_UPLOAD_PORT=4902 AGENT_REGISTRATION_PASSWORD=oracle123

Executing agent install prereqs...

Executing command: /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/bin/runInstaller -ignoreSysPrereqs -prereqchecker -silent -ignoreSysPrereqs -waitForCompletion  -prereqlogloc /app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/agentDeploy -entryPoint oracle.sysman.top.agent_Complete -detailedExitCodes PREREQ_CONFIG_LOCATION=/app/oracle/amddhome/agent/core/12.1.0.1.0/prereqs

Prereq Logs Location:/app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/agentDeploy/prereq.log

Agent install prereqs completed successfully

Cloning the agent home...

Executing command: /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/bin/runInstaller -ignoreSysPrereqs -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=/app/oracle/amddhome/agent/core/12.1.0.1.0   AGENT_BASE_DIR=/app/oracle/amddhome/agent AGENT_BASE_DIR=/app/oracle/amddhome/agent OMS_HOST=devd1.come.com EM_UPLOAD_PORT=4902 AGENT_REGISTRATION_PASSWORD=oracle123 -noconfig  ORACLE_HOME_NAME=agent12g2 -force AGENT_PORT=-1

Clone Action Logs Location:/oravl01/oracle/oraInventory/logs/cloneActions.log

Cloning of agent home completed successfully

Attaching sbin home...

Executing command: /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/bin/runInstaller -ignoreSysPrereqs -attachHome -waitForCompletion -nowait ORACLE_HOME=/app/oracle/amddhome/agent/sbin ORACLE_HOME_NAME=sbin12g2 -force

Attach Home Logs Location:/app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/agentDeploy/AttachHome.log

Attach home for sbin home completed successfully.

Updating home dependencies...

Executing command: /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/bin/runInstaller -ignoreSysPrereqs -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST="/app/oracle/amddhome/agent/sbin:/app/oracle/amddhome/agent/core/12.1.0.1.0" -invPtrLoc /app/oracle/amddhome/agent/core/12.1.0.1.0/oraInst.loc -force

Update Home Dependencies Location:/app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/agentDeploy/UpdateHomeDeps.log

Update home dependency completed successfully.

Performing the agent configuration...

Executing command: /app/oracle/amddhome/agent/core/12.1.0.1.0/oui/bin/runConfig.sh ORACLE_HOME=/app/oracle/amddhome/agent/core/12.1.0.1.0 RESPONSE_FILE=/app/oracle/amddhome/agent/core/12.1.0.1.0/agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml} RERUN=true

Configuration Log Location:/app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/cfgfw/CfmLogger.log

Agent Configuration completed successfully

The following configuration scripts need to be executed as the "root" user.

#!/bin/sh

#Root script to run

/app/oracle/amddhome/agent/core/12.1.0.1.0/root.sh

To execute the configuration scripts:

1. Open a terminal window

2. Log in as "root"

3. Run the scripts

Agent Deployment Successful.

Agent deployment log location:

/app/oracle/amddhome/agent/core/12.1.0.1.0/cfgtoollogs/agentDeploy/agentDeploy_.log

Agent deployment completed successfully.

suzan:/app/oracle/amddhome/agent]

Agent hostname should be what is pinable from OMS server. If needed we can use ORACLE_HOSTNAME vaiable.

suzan:/app/oracle/amddhome/agent] ./agentDeploy.sh AGENT_BASE_DIR=/app/oracle/amddhome/agent OMS_HOST=devd1.come.com EM_UPLOAD_PORT=4902 AGENT_REGISTRATION_PASSWORD=oracle123 ORACLE_HOSTNAME=suzan.sys.come.net

Oracle Enterprise Manager Database Control on Windows 7

Installing Oracle Enterprise Manager Database Control on localhost machine (Windows 7).

Prerequisites:

SYS and DBSNMP password must be known, listener port and ORACLE HOME should be known.

Database should not have SYSMAN, MGMT_VIEW scheman with MGMT_USER role.

Also drop all the public synonyms for which table owner is sysman.

drop user sysman cascade;

drop role MGMT_USER;

drop user MGMT_VIEW cascade;

select 'drop public synonym ' || SYNONYM_NAME || ';' from dba_synonyms where owner='PUBLIC' and table_owner='SYSMAN';

Once done with all below.

C:\Windows\system32>emca -config dbcontrol db -repos create

STARTED EMCA at Dec 13, 2012 9:16:28 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database SID: oracle

Listener port number: 1521

Listener ORACLE_HOME [ C:\app\JAYVA\product\11.2.0\dbhome_1 ]:

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:\app\JAYVA\product\11.2.0\dbhome_1

Local hostname ................ localhost

Listener ORACLE_HOME ................ C:\app\JAYVA\product\11.2.0\dbhome_1

Listener port number ................ 1521

Database SID ................ oracle

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

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

Do you wish to continue? [yes(Y)/no(N)]: Y

Dec 13, 2012 9:16:52 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at C:\app\JAYVA\cfgtoollogs\emca\oracle\emca_2012_12_13_21_16_28.log.

Dec 13, 2012 9:16:53 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Dec 13, 2012 9:23:32 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Dec 13, 2012 9:23:42 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO: Uploading configuration data to EM repository (this may take a while) ...

Dec 13, 2012 9:24:46 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Dec 13, 2012 9:24:59 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib

INFO: Software library configured successfully.

Dec 13, 2012 9:24:59 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Deploying Provisioning archives ...

Dec 13, 2012 9:25:34 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Provisioning archives deployed successfully.

Dec 13, 2012 9:25:34 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Dec 13, 2012 9:25:42 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Dec 13, 2012 9:25:42 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Dec 13, 2012 9:26:48 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Dec 13, 2012 9:26:49 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://localhost:5500/em <<<<<<<<<<<

Dec 13, 2012 9:26:51 PM 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:/app/JAYVA/product/11.2.0/dbhome_1/localhost_oracle/sysman/config/emkey.ora.  

Please 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 Dec 13, 2012 9:26:51 PM

C:\Windows\system32>

In case of any issue, please check log on location:

ORACLE_BASE\cfgtoollogs\emca\\ emca_repos_create_.log.

In case you get error for ORACLE_UNQNAME as below:

C:\Users\jayva>emctl status dbconsole

Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name.

C:\Users\jayva>

Check for file at location ORACLE_HOME\ oc4j\j2ee\ OC4J_DBConsole_localhost_oracle

Here localhost is the hostname & oracle is unqname. Might be differ in your environment.

ORA-01555 Snapshot Too Old

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"

  FROM v$datafile a,

       v$tablespace b,

       dba_tablespaces c

 WHERE c.contents = 'UNDO'

   AND c.status = 'ONLINE'

   AND b.name = c.tablespace_name

   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

      "UNDO_BLOCK_PER_SEC"

  FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"

 FROM v$parameter

WHERE name = 'db_block_size';

Optimal Undo Retention

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       ROUND((d.undo_size / (to_number(f.value) *

       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"

  FROM (

       SELECT SUM(a.bytes) undo_size

          FROM v$datafile a,

               v$tablespace b,

               dba_tablespaces c

         WHERE c.contents = 'UNDO'

           AND c.status = 'ONLINE'

           AND b.name = c.tablespace_name

           AND a.ts# = b.ts#

       ) d,

       v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

              undo_block_per_sec

         FROM v$undostat

       ) g

WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'

Optimal undo retention

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'

Recommended unto_rentention :-

select BEGIN_TIME,END_TIME , TUNED_UNDORETENTION from v$undostat;

Number of time snapshot too old error found :-

select begin_time,ssolderrcnt from v$undostat;

Maximum time of query in Undo, clue is to set undo retention higher to this value :-

select begin_time,maxquerylen from v$undostat;