Failed to shutdown DBConsole Gracefully

Environment:
Oracle:
Oracle 10g Release 2  10.2.0.5
OS:
Linux
Summary:
Fix Oracle EM problem:Failed to shutdown DBConsole Gracefully

Today on a Linux Oracle machine without Oracle Enterprise Manger, I would like to install Oracle EM.

Use command

emca -config dbcontrol db -repos create

It reports an error

- An instance of Oracle Enterprise Manager 10g Database Control is already running.

It is strange that I have never create or start an EM process.

So I try to stop the existing EM process

SQL> emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
http://oracletest:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control …
— Failed to shutdown DBConsole Gracefully —
failed.

It fails, and also fails when I want to start EM

SQL> emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.5.0
Copyright (c) 1996, 2006 Oracle Corporation.  All rights reserved.
http://oracletest:1158/em/console/aboutApplication
– An instance of Oracle Enterprise Manager 10g Database Control is already running.

A metalink doc tells us, it may happens when /etc/hosts file does not have following rows

127.0.0.1    localhost.localdomain  localhost

The  metalink doc also tells us to check file emctl.pid

Under $ORACLE_HOME/$HOST_$ORACLE_SID, for example in my host under /oracle/app/oracle/product/10.2.0/oracletest_mes

/oracle/app/oracle/product/10.2.0/oracletest_mes> cat emctl.pid

36599

Use ps –ef to check 36599 process, it is really not a oracle EM dbconsole process,so that’s why emctl stop dbconsole can not stop its instance.

Delete emctl.pid, emctl stop dbconsole succeed.

But when recreate dbconsole, it fails again and show error that the port 3938 has been used.

Check $ORACLE_HOME/install/portlist.ini

Enterprise Manager Console HTTP Port (mes) = 1158

Enterprise Manager Agent Port (mes) = 3938

It tells us EM agent use 3938 port, that means EM has 2 processes, one is for dbconsole, and one is for emagent. So you know how to do next, kill emagent process and recreate dbconsole. And this time, everything is OK.

Drop the EMCA repository,sysman schema and objects

[oracle@linux200 bin]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Jun 19, 2013 4:11:49 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Jun 19, 2013 4:12:28 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/ORCL/emca_2013-06-19_04-11-48-PM.log.
Jun 19, 2013 4:12:29 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jun 19, 2013 4:12:29 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...

ethod 2: Drop the repository schema and object (no quiesce):

Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:

SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;

CREATE STANDBY DATABASE USING RMAN

[oracle@linux1 database]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Mar 2 19:10:41 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

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

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE

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

ORCL      orcl                           PRIMARY          READ WRITE

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR

NOARCHIVELOG NO

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           3

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/flash_recovery _area

db_recovery_file_dest_size           big integer 2G

change archivelog_dest:-

SQL> !mkdir -p /u01/app/oracle/oradata/chicago/arch

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/chicago/arch' scope=both;

System altered.

enable archivelog with new destination:

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     1

Current log sequence           3

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/oradata/chicago/arch

Oldest online log sequence     2

Next log sequence to archive   4

Current log sequence           4

SQL> !ls -al /u01/app/oracle/oradata/chicago/arch

total 23704

drwxr-xr-x 2 oracle oinstall     4096 Mar  2 19:32 .

drwxr-xr-x 3 oracle oinstall     4096 Mar  2 19:22 ..

-rw-r----- 1 oracle oinstall 24233984 Mar  2 19:32 1_3_809031038.dbf

SQL> select force_logging from v$database;

FOR

---

YES

SQL> select max(bytes),count(1) from v$log;

MAX(BYTES)   COUNT(1)

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

  52428800          3

adding standby logfiles at same location as of archivelog:

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog01.log' size 52M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog02.log' size 52M;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/chicago/sbylog03.log' size 52M;

Database altered.

SQL> select group#,type,member from v$logfile where type='STANDBY';

    GROUP# TYPE

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

MEMBER

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

         4 STANDBY

/u01/app/oracle/oradata/chicago/sbylog01.log

         5 STANDBY

/u01/app/oracle/oradata/chicago/sbylog02.log

         6 STANDBY

/u01/app/oracle/oradata/chicago/sbylog03.log

To check the status of standby logfiles created

SQL> select group#,dbid,thread#,sequence#,status from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#

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

STATUS

----------

         4 UNASSIGNED                                        0          0

UNASSIGNED

         5 UNASSIGNED                                        0          0

UNASSIGNED

         6 UNASSIGNED                                        0          0

UNASSIGNED

setting DG_CONFIG:

SQL> show parameter log_archive_config

NAME                                 TYPE        VALUE

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

log_archive_config                   string

SQL> alter system set log_archive_config='DG_CONFIG=(chicago,boston)' scope=spfile;

System altered.

setting log archive dest for primary database for all logfiles and all roles:

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/chicago/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=spfile;

System altered.

setting log archive transport from primary destination to standby database visa service name called boston :

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=spfile;

System altered.

enable the archive dests

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;

System altered.

setting db_convert  to change the location stdby when a datafile is added on primary location use this if you are using different path from primary:

SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;

addding below will add redologfiles on standby with changed location:

SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/orclstdby/' scope=spfile;

System altered.

making the file management auto this will add datafiles added on primary to standby:

SQL> alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile;

System altered.

SQL> alter system set FAL_SERVER=boston scope=spfile;

System altered.

SQL> alter system set FAL_CLIENT=chicago scope=spfile;

System altered.

restart the db to take these parameters effect:

add listener config on standby as primary already has its settings:

[oracle@linux2 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = linux2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

SID_LIST_LISTENER =

(SID_LIST =

 (SID_DESC =

  (GLOBAL_DBNAME = boston)

   (ORACLE_HOME = /u01/app/oracle/11.1.0/db_1)

   (SID_NAME = boston)

  )

 )

tnsnames.ora on both should have values like:

# tnsnames.ora Network Configuration File: /u01/app/oracle/11.1.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

BOSTON =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = boston)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )

create a initboston.ora file with db_name=primarydb (orcl)

create directory structure on standby, same as primary:

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/admin/boston/adump

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/oradata/boston/arch

[oracle@linux2 oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area/boston

check the database connectivity between both the databases through the sqlplus:

SQL> conn sys/oracle@chicago as sysdba

SQL> conn sys/oracle@boston as sysdba

[oracle@linux2 ~]$ rman target sys/oracle@chicago auxiliary sys/oracle@boston

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Mar 3 10:57:18 2013

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

connected to target database: ORCL (DBID=1336811768)

connected to auxiliary database (not started)

RMAN> run {

 allocate channel p1 type disk;

 allocate channel p2 type disk;

 allocate auxiliary channel s1 type disk;

 duplicate target database for standby from active database

 spfile

 parameter_value_convert'chicago','boston'

set db_unique_name='boston'

 set db_file_name_convert='/orcl/','/boston/'

 set log_file_name_convert='/orcl/','/boston/'

 set control_files='/u01/app/oracle/oradata/boston/control01.ctl','/u01/app/oracle/oradata/boston/control02.ctl'

 set log_archive_max_processes='5'

 set fal_client='boston'

 set fal_server='chicago'

 set standby_file_management='AUTO'

 set log_archive_config='dg_config=(chicago,boston)'

 set log_archive_dest_1='service=chicago ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=chicago'

 ;

 }

created physical db

SQL> select name,db_unique_name,open_mode,database_role from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE  DATABASE_ROLE

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

ORCL      boston                         MOUNTED    PHYSICAL STANDBY

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     6

Next log sequence to archive   0

Current log sequence           8

SQL> !ps -ef|grep mrp

oracle    3973  3965  0 12:05 pts/1    00:00:00 /bin/bash -c ps -ef|grep mrp

[oracle@linux2 trace]$ pwd

/u01/app/oracle/diag/rdbms/boston/boston/t

[oracle@linux2 trace]$ vi alert_boston.log

[oracle@linux2 trace]$ tail -f alert_boston.log

start applying the archive logs :

sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

open database in read only mode

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Database mounted.

SQL> alter database open read only;

start applying the logs again back to database (if you forget to add disconnect from session the mrp process with stop applying after you close session )

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Error 1017 received logging on to the standby

If you are working in a standby environment this is a quite common error that you will see in the alert log. There are various situations when you will see this error.Although you have a latest copy of password file copied in both PRIMARY and STANDBY DB, there is a chance that you will see this error. This is a generic error that comes to oracles's mind and warns you about it.

Fetching gap sequence in thread 1, gap sequence 44212-44255
Error 1017 received logging on to the standby

------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191

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

FAL[client, USER]: Error 16191 connecting to ORCL for fetching gap sequence
Archived Log entry 5 added for thread 1 sequence 44257 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:39 2013
Archived Log entry 6 added for thread 1 sequence 44260 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:41 2013
Archived Log entry 7 added for thread 1 sequence 44259 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:42 2013
RFS[5]: Opened log for thread 1 sequence 44261 dbid -1517523940 branch 839361298
Tue Jan 19 12:03:43 2013
Error 1017 received logging on to the standby

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

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191

SOLUTION :

1. Copy the latest copy of PASSWORD file from PRIMARY instance to STANDBY instance and replace it with the old one. 

Usually password file is in format of  ORAPWDINSTANCENAME

example : ORAPWDORCL (where ORCL is my instance name).

2. Check if the PASSWORD FILE is set to either EXCLUSIVE mode or SHARED.

3. Test if you can connect to both PRIM and STANDBY as SYSDBA using password.

Note :

IF you don't know the password for SYS, you can recreate password file using this link.

How to create new password file.

Create ORAPWD ORACLE PASSWORD FILE

REMOTE_PASSWORD_LOGIN parameter is set in init.ora parameter remote_login_passwordfile.  This parameter must be set to either SHARED or EXCLUSIVE. When set to SHARED, the password file can be used by multiple databases, yet only the SYS user is recognized.  When set to EXCLUSIVE, the file can be used by only one database, yet multiple users can exist in the file.  The parameter setting can be confirmed by:

SQL> show parameter password

NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      EXCLUSIVE


To Create the password file.  This is done by executing the following command

$ orapwd file=filename  password=password entries=max_users

The file name is the name of the file that will hold the orapwd password information.  The file location will default to the current directory unless the full path is specified.  The contents are encrypted and are unreadable. The password required is the one for the SYS user of the database.

The max_users is the number of database users that can be granted SYSDBA or SYSOPER.  This parameter should be set to a higher value than the number of anticipated users to prevent having to delete and recreate the password file.

USING ORAPWD:
The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]
Command arguments are summarized in the following table.

PARAMETER DESCRIPTION :

FILE: Name to assign to the password file. See your operating system                                                             documentation for name requirements. You must supply a                                                                        complete path. If you supply only a file name, the file is written to                                                          the current directory.

ENTRIES(Optional): Maximum number of entries (user accounts) to permit in the file.

FORCE(Optional):         If y, permits overwriting an existing password file.

IGNORECASE(Optional): If y, passwords are treated as case-insensitive.

NOSYSDBA(Optional): For Data Vault installations. See the Data Vault installation guide                                                           for your platform for more information.
There are no spaces permitted around the equal-to (=) character.


The command prompts for the SYS password and stores the password in the created password file.


EXAMPLE

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.


Create a new password file:
orapwd file=orapwSID password=oracle entries=5

If the password file already exists:
orapwd file=orapwSID password=oracle entries=5 FORCE=Y

Backup and restore of Database before upgrade using RMAN

Make sure you always take Database backup before any upgrades and patches.It is always good idea to do this. In the below example I am showing how to take a backup of full database to some location and how to restore it back from backup in-case of any failures.

backup database before upgrade:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL C1 TYPE DISK;
BACKUP DATABASE FORMAT '/u02/backup/backup_%U' TAG "before_upgrade";
BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/controlfilebkp.ctl';
}



Restoring database from backup in-case of upgrade fail:

RMAN> restore controlfile from '/u02/backup/controlfilebkp.ctl';

Starting restore at 18-JUL-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 18-JUL-12

copy controlfile to location control02, control03 :

[oracle@linux5 test]$ cp control01* control02.ctl
[oracle@linux5 test]$ cp control01* control03.ctl
[oracle@linux5 test]$ pwd
/u01/app/oracle/oradata/test
[oracle@linux5 test]$


RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-JUL-12
Starting implicit crosscheck backup at 18-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 18-JUL-12

Starting implicit crosscheck copy at 18-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JUL-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/backup_06of1qf9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/backup_06of1qf9_1_1 tag=BEFORE_UPGRADE
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-JUL-12


RMAN> recover database;

Starting recover at 18-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/oradata/test/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JUL-12


Now login as sysdba and reset the logs before you can start using the database:

sql>sqlplus /"As sysdba

SQL> alter database open resetlogs;

ORA-01113: file 13 needs media recovery

oracle@Linux01:[/u01/app/oracle/datafiles/orcl] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 21 10:09:56 2012

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


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

SQL> ALTER DATABASE
DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'
ONLINE  2 ;

*
ERROR at line 3:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected


SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'


SQL> recover datafile '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf';
Media recovery complete.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;

Database altered.

SQL>

Recover data using Flashback Query

How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.

We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed. 

Before proceed ensure that,

•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.

•UNDO_MANAGEMENT is set to AUTO.

•In your UNDO TABLESPACE you have enough space.

With an example I will demonstrate the whole procedure.

1)I have created a table named test_flash_table with column name and salary.

SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.

SQL> insert into test_flash_table values('ABCD',10);
1 row created.

SQL> commit;
Commit complete.

The table contains one row.

2)I erroneously updated column salary of Arju and commited data.

SQL> update test_flash_table set salary=20 where name='ABCD';
1 row updated.

SQL> commit;
Commit complete.

3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.

SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);

NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
ABCD 10 29-APR-12 12.34.03.452330 AM -04:00 869222

4)Now update the data based on the SCN.

SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='ABCD') where name='ABCD';
1 row updated.

SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
ABCD 10