PGA , SGA , MEMORY Advisors

MEMORY_Advisors
One of the issues that Database DBA face is oracle database memory set correctly or do I need to increase it.
Oracle 11g has cool solution.
Querying from table: v$memory_target_advice , tells if increasing memory size would provide any advantage, if so how much should you increase.
Here is the example:


Review the results in the screen shot. Currently database has a memory_target of 2048 MB (First line shows this).It can be compared with DB initialization parameter MEMORY_TARGET.
Line 2 shows that increasing memory size to 2560 MB (which is 25% more) did not add further benefit which can be seen in the column : EST_DB_TIME_FACTOR value 1. However, in line 3 if Memory size is 3072 (i.e. 50% more ) estimated time factor is 0.9602 that means 1 sec process can be done in 0.9602 sec this is an improvement.
Even if you increase memory by 75%, there is no additional benefit, but there is very minor benefit when memory is  double.
In this scenario increasing memory by 50% is adding benefit for the database.

PGA Advisor

Sql>select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

In the above figure, look at 5^th row
Value=1358954496 is the current orcldb.__pga_aggregate_target  value  (set in initorcldb.ora)

So, Subtract pga_target_for_estimate - estd_extra_bytes_rw for 5^th row , 1358954496-954314752

equals to 404639744 i.e eqals to 386 MB.

So, conclusion is that adding extra 386 MB will improve performance(less I/O would be needed).

SGA Advisor

Sql>select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL;

Minimizing DB_TIME is the overall objective of all tuning.

Look at the 3^rd row i.e value 1952 MB , this is the value of parameter orcldb.__sga_target being set

in initorcldb.ora.

So,It can be seen that if the SGA is raised from its current value of 1952 MB to 3904 MB then

DB_TIME would reduce.

Oracle Database Switchover and Failover steps

Switchover Steps
1) There are few steps we need to check before performing the switchover in dataguard environment 
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)Check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

2)Verify that it is possible to perform a switchover operation On the primary database

On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.

SWITCHOVER_STATUS 

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

 SESSIONS ACTIVE 

 -Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.

For example:

 SQL> alter database commit to switchover to standby with session shutdown;

(Note: that the clause "with session shutdown" also works with the switchover to primary command 

Ex: alter database commit to switchover to primary with session shutdown;)

SWITCHOVER_STATUS 

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

NOT ALLOWED         

-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases. 

SWITCHOVER_STATUS 

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

SESSIONS ACTIVE                 - 

-Indicates that there are active SQL sessions attached to the primary or                                               standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER_STATUS 

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

SWITCHOVER PENDING - 

-This is a standby database and the primary database switchover request has been received but not processed. 

SWITCHOVER_STATUS 

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

SWITCHOVER LATENT - 

-The switchover was in pending mode, but did not complete and went back to the primary database.

SWITCHOVER_STATUS 

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

TO PRIMARY                  

 - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

SWITCHOVER_STATUS 

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

TO STANDBY 

- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

SWITCHOVER_STATUS 

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

RECOVERY NEEDED 

- This is a standby database that has not received the switchover request.

During normal operations it is acceptable to see the following values for

SWITCHOVER_STATUS 

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

 SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)

 During normal operations  it is acceptable to see these values

SWITCHOVER_STATUS 

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

NOT ALLOWED (or) SESSIONS ACTIVE.  (on the standby database)

-During normal operations  it is acceptable to see these values 

3. Convert the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

4. Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             528484544 bytes
Database Buffers          285212672 bytes
Redo Buffers                2486272 bytes

SQL> alter database mount standby database;

Database altered.

5. Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_3=defer;

6. Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SWITCHOVER PENDING

(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.

7. Convert the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

8. Shutdown and startup the new primary:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             482347200 bytes
Database Buffers          331350016 bytes
Redo Buffers                2486272 bytes
Database mounted.
Database opened.

9. Enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable;

10. Start managed recover on the new standby database:

SQL> recover managed standby database disconnect;

Media recovery complete.

11) There are few steps we need to check after performing the switchover in dataguard environment 
a)If we are using the OEM check the status of  primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

FAILOVER Steps:

1. Initiate the failover on the standby database STAN:

SQL>connect /@STAN as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

2. Immediately after issuing command in step 2, shut down and restart the standby instance STAN:

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

STAN is now transitioned to the primary database role.Now your PHYSICAL STANDBY Database has become PRIMARY. To verify this change, again query the Database_role column of V$DATABASE. Now it will return “PRIMARY”.

SQL> Select Database_role from v$Database;

Enable Oracle database to send emails via SMTP server

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

Check SMTP access of database Linux server on mail server

- Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

1	$ telnet smtp_server 25

# If you see blank screen or “telnet: Unable to connect to remote host: Connection refused”,
# your DB server is not recognized by the SMTP server. Otherwise type the following commands:

helo mail from: my_email@my_company.com # you should see "Sender OK' rcpt to: my_email@my_company.com   # you should see "Recipient OK" data                               # Start mail input test email via SMTP and orcl DB [Enter] .                                  # mail should be sent [Enter] quit

Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example:
Please allow SMTP traffic at smtpgate.mycompany.com for the following new server:
Hostname: ..com
IP: 99.999.99.99
Function: Oracle production DB …
Outgoing email: my_app@my_company.com
Amount emails per month: around 50-100
After that your mail admins will add a sender IP address to a corporate SMTP mail server to allow the sender Linux server access the SMTP server. This will enable the SMTP server sending emails on behalf of the sender (your Oracle database Linux box).
Note that the mail server can check if the sender email exists. If not, you can send an email from a dummy email account.

Sending email from Oracle database via SMTP

create or replace procedure testmail (fromm varchar2,too varchar2,sub varchar2,body varchar2,port number) is objConnection utl_smtp.connection; vrData varchar2(32000); BEGIN objConnection := UTL_smtp.open_connection('smtp_server',port); UTL_smtp.helo(objConnection, 'smtp_server'); UTL_smtp.mail(objConnection, fromm); UTL_smtp.rcpt(objConnection, too); UTL_smtp.open_data(objConnection); /* ** Sending the header information */ UTL_smtp.write_data(objConnection, 'From: '||fromm || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'To: '||too || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'Subject: ' || sub || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection, 'Content-Type: ' || 'text/html;'); UTL_smtp.write_data(objConnection, 'Content-Transfer-Encoding: ' || '"8Bit"' || UTL_tcp.CRLF); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||'<span style="color: red; font-family: Courier New;">'||body||'</span>'); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.write_data(objConnection,UTL_tcp.CRLF||''); UTL_smtp.close_data(objConnection); UTL_smtp.quit(objConnection); EXCEPTION WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN UTL_smtp.quit(objConnection); dbms_output.put_line(sqlerrm); WHEN OTHERS THEN UTL_smtp.quit(objConnection); dbms_output.put_line(sqlerrm); END testmail; / -- this should send an email from a dummy account to your email address execute testmail ('dummy@my_company.com','my_email@my_company.com','test Subject','Test Body Text',25);

- In case of following ORA- error go to step 2 and 3:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 21
ORA-06512: at “SYS.UTL_SMTP”, line 97
ORA-06512: at “SYS.UTL_SMTP”, line 139
The “ORA-29278: SMTP transient error: 421 Service not available” error indicates that the problem
is not with the UTL_SMTP package but the fact that your database server’s network configuration does
not allow it to contact an external SMTP server.

- In case a following Oracle error, go to next step and enable ACL in Oracle 11g database:
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at “SYS.UTL_SMTP”, line 54
ORA-06512: at “SYS.UTL_SMTP”, line 138
ORA-06512: at “SYS.UTL_SMTP”, line 699
ORA-06512: at “me.TESTMAIL”, line 35
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Enable access control list (ACL) for your network hosts

The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show quickly 2 major steps that are required to enable ACL for a user. Read Oracle documentation for more details.

Create an ACL with one database user in Oracle database

BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl => 'smtp-gate-permissions.xml', description => 'Permissions for smtp gate', principal => 'SCOTT', is_grant => TRUE, privilege => 'connect' ); COMMIT; END; /

Assign an ACL to one or more network hosts

BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 'smtp-gate-permissions.xml', host => '<smtp_server>', lower_port => 25, upper_port => null); COMMIT; END; /

So that was my action plan for providing your applications an ability to send email via SMTP protocol from an Oracle database Linux server using a real example with UTL_SMTP package including ORA- error handling and ACL creation on Oracle 11g.

Automating ADDM report Generation

Some times we may have a requirement to generate addm report for peak hours every day. Below are the steps , by which we can automate addm reports generation.

1) Create a task in DB.

begin
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => 'ADDM2',
task_desc => 'Addm report generation schedule');
end;
/

2) Create this procedure in database. This procedure can generate awr report for 10:00 AM to 14:00 AM.

create or replace procedure auto_addm as
start_time number;
end_time number;
BEGIN
DBMS_ADVISOR.RESET_TASK(task_name =>'ADDM1');
select (MAX(SNAP_ID)-1) into start_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_time from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'START_SNAPSHOT',
value => start_time);
DBMS_ADVISOR.set_task_parameter (
task_name => 'ADDM1',
parameter => 'END_SNAPSHOT',
value => end_time);
-- Execute the task.
DBMS_ADVISOR.execute_task(task_name =>'ADDM1');
END auto_addm;
/

3) Schedule the bleow script in crontab.

sqlplus -s '/as sysdba' << EOF
SET LONG 100000
SET PAGESIZE 50000
exec auto_addm;
spool addmrpt.txt
SELECT DBMS_ADVISOR.get_task_report('ADDM1') AS report FROM dual;
spool off;

exit;

EOF
cat addmrpt.txt |mailx -s "ADDMREPORT For TODAY" "MAIL-ID"

Automating AWR report Generation

=========AWR REPORT AUTOMIZATION===========


CREATE OR REPLACE PROCEDURE CreateAwrReports (begin_snap number,end_snap number, directory varchar2 ) as
v_Instance_number v$instance.instance_number%TYPE;
v_Instance_name v$instance.instance_name%TYPE;
v_dbid V$database.dbid%TYPE;
v_file UTL_FILE.file_type;
BEGIN
SELECT instance_number, instance_name into v_Instance_number,v_Instance_name FROM gv$instance ORDER BY 1;
SELECT dbid INTO v_dbid FROM v$database;
EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY AWR_DIR AS '''||directory||'''');
BEGIN
v_file := UTL_FILE.fopen('AWR_DIR', 'awr_' || v_Instance_name ||'_'|| v_Instance_number || '_' || begin_snap || '_' || (end_snap-1) || '.txt', 'w', 32767);
FOR c_AWRReport IN (
SELECT output FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT( v_dbid, v_Instance_number, begin_snap,end_snap-1))
) loop
UTL_FILE.PUT_LINE(v_file, c_AWRReport.output); end loop;
UTL_FILE.fclose(v_file);
END;
EXECUTE IMMEDIATE('DROP DIRECTORY AWR_DIR');
END;


create or replace procedure generate_awrreport as
begin_snap number;
end_snap number;
begin
select (MAX(SNAP_ID)-1) into begin_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '10';
select (MAX(SNAP_ID)-1) into end_snap from dba_hist_snapshot where to_char(BEGIN_INTERVAL_TIME,'HH24') = '14';
CreateAwrReports(begin_snap, end_snap,'/export/home/oracle/');
end;
/

How to schedule AWR report(awrrpt.sql) via crontab?

Hi All,


I came across a requirement to schedule a AWR snap report between 9 AM to 6 PM on certain days. 


The aim here was to automatically identify snap id for morning 9 AM and 6 PM and run the report from background and email the report.


Following is a sample script. In my test instance begin snap id is returning only 1 snap so I am good :)
If you get multiple snap id by given select statement please use min(snap_id) and max(snap_id) in begin and end respectively. I have commented the script to help understanding better.


Save the following as any shell script and schedule in cron.


myAWR.sh


## START


# Following mechanism will record begin and end snap_id by given time 9 and 18 hour of the day
# It also generate define statements which are required to run awrrpti.sql from background


sqlplus -s '/as sysdba' << EOF
set echo off
set head off
set feed off
spool /tmp/tmpScr.sql
select 'Define begin_snap= '|| snap_id from dba_hist_snapshot where 
EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=9 and
EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and 
EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and
EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');


SELECT 'Define end_snap= '|| snap_id from dba_hist_snapshot where 
EXTRACT(HOUR FROM BEGIN_INTERVAL_TIME)=18 and
EXTRACT(year FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'YYYY') and 
EXTRACT(month FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'MM') and
EXTRACT(day FROM BEGIN_INTERVAL_TIME) = TO_CHAR(sysdate,'DD');


select 'define  report_type  = ' || '''text''' from dual;
select 'define  report_name  = /tmp/awrrep.txt' from dual;
select 'define  inst_name    = ' || INSTANCE_NAME from v\$instance;
select 'define  db_name    = ' || name from v\$database;
select 'define  dbid = ' || dbid from v\$database;
select 'define inst_num = ' || INSTANCE_NUMBER from v\$instance;
select 'define  num_days     = 3' from dual;
select '@$ORACLE_HOME/rdbms/admin/awrrpti.sql' from dual;
exit
EOF


# Following part runs the script composed earlier with snap ids and define statements
sqlplus -s '/as sysdba' << EOF
@/tmp/tmpScr.sql
exit
EOF

## END

Crontab Example (Linux)

## Running AWR everyday 7:00 PM

00 19 * * * /njadav/myAWR.sh | mailx -s "AWR Today" myemail@mydomain.com 2>&1

Note:

To further customize, please check parameters of $ORACLE_HOME/rdbms/admin/awrrpti.sql by opening it.

Following sample ..

define  inst_num     = 1;

define  num_days     = 3;

define  inst_name    = 'Instance';

define  db_name      = 'Database';

define  dbid         = 4;

define  begin_snap   = 10;

define  end_snap     = 11;

define  report_type  = 'text';

define  report_name  = /tmp/swrf_report_10_11.txt

Drop Corrupted Undo Tablespace with active Rollback Segments

Drop Corrupted Undo Tablespace with active Rollback Segments

1, Make sure the database was last cleanly shut down.

sqlplus /nolog
SQL>connect sys/change@crm as sysdba
SQL> shutdown immediate

2, mount database in RESTRICT mode, using pfile.

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initCRM_18.ora
ORACLE instance started. Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.

3, Try to offline drop the bad datafile.

SQL> ALTER DATABASE DATAFILE 'K:\ORADATA\CRM\UNDOTBS2_02.DBF' OFFLINE DROP;

*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

or this SQL:

DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES ;
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU11$’ found, terminate dropping
tablespace

4, Use this query to see how many rollback segments were corrupted:

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————–
_SYSSMU11$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19$ NEEDS RECOVERY UNDOTBS2
_SYSSMU20$ NEEDS RECOVERY UNDOTBS2

5, Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16$','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

Make sure you uncomment “undo_management=AUTO”, and specify you want to use UNDOTBS1 as undo tablespace.

#undo_management=AUTO
undo_tablespace=UNDOTBS1

6, Start the database again:

SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initcrm_18.ora


7. Drop bad rollback segments


SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
…

SQL> drop rollback segment "_SYSSMU20$";
Rollback segment dropped.

8, Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME STATUS TABLESPACE_NAME
—————————— —————- —————
SYSTEM ONLINE SYSTEM
_SYSSMU2$ ONLINE UNDOTBS1
_SYSSMU3$ ONLINE UNDOTBS1
_SYSSMU4$ ONLINE UNDOTBS1
_SYSSMU5$ ONLINE UNDOTBS1
_SYSSMU6$ ONLINE UNDOTBS1
_SYSSMU7$ ONLINE UNDOTBS1
_SYSSMU8$ ONLINE UNDOTBS1
_SYSSMU9$ ONLINE UNDOTBS1
_SYSSMU10$ ONLINE UNDOTBS1
_SYSSMU21$ ONLINE UNDOTBS1

9. Now drop bad undo TABLESPACE UNDOTBS2;

SQL> drop TABLESPACE UNDOTBS2;

10, Recreate the undo rollback tablespace with all its rollback segments

SQL>CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'K:\oradata\CRM\UNDOTBS01.DBF' SIZE 2000M reuse AUTOEXTEND ON ;


11, Change undo tablespace

ALTER SYSTEM SET undo_tablespace = UNDOTBS1 ;

12. Remove the following line from pfile

_corrupted_rollback_segments =('_SYSSMU11$','_SYSSMU12$','_SYSSMU13$','_SYSSMU14$','_SYSSMU15$','_SYSSMU16 $','_SYSSMU17$','_SYSSMU18$','_SYSSMU19$','_SYSSMU20$')

and uncomment “undo_management=AUTO”

undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

13, Shutdown database

SQL>shutdown immediate;

14, Edit initCRM_18.ora, make sure you change ‘undo_tablespace=UNDOTBS2? to “undo_tablespace=UNDOTBS1?, then start oracle database:


sqlplus /nolog
SQL>connect sys/change@crm as sysdba
SQL> STARTUP RESTRICT MOUNT pfile=C:\Oracle\job\crmstandby\initcrm_18.ora
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

15, Create Undo tablespace:

SQL>CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'K:\oradata\CRM\UNDOTBS02.DBF' SIZE 2000M reuse AUTOEXTEND ON ;
SQL>DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

16, Startup database with spfile

SQL> startup;
ORACLE instance started.
Total System Global Area 1620126452 bytes
Fixed Size 457460 bytes
Variable Size 545259520 bytes
Database Buffers 1073741824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.