Database Software

Database : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
OS: Solaris
Replication type: Dataguard (with out broker)
Problem:
Due to power fail on production we switched our secondary to primary for our safety purpose we enabled flashback point on present production(old standby), Now hope we will face problem while switching back to production please help and guide what will be the best practice in this situation and due to the flashback on squnce is not changing how to come out of this problem please help and guide

MAX(SEQUENCE#)

--------------------------------------------------------------------------------
9221

========#######================

Last login: Wed Sep 5 01:51:32 from 10.245.13.6
$ su - oracle
Password:
mesg: cannot change mode
$ ps -ef|grep pmon
oratst 2978 1 0 Sep 08 ? 233:10 ora_pmon_amantst
oracle 3039 1 0 Sep 08 ? 209:09 ora_pmon_airman
oracle 17582 17574 0 05:41:15 pts/1 0:00 grep pmon

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 5 05:56:21 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oradata/airman/archive
Oldest online log sequence 9220
Next log sequence to archive 0
Current log sequence 9222
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 5 05:57:08 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter dump

NAME TYPE VALUE



--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/admin/airman/b
dump
core_dump_dest string /u01/app/oracle/admin/airman/c
dump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/admin/airman/u
dump
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$ cd /u01/app/oracle/admin/airman/bdump
$ ls -lrt alert
alert: No such file or directory
$ *
ksh: airman_arc0_11192.trc: cannot execute
$ ls -lrt alert*
-rw-r----- 1 oracle oinstall 5323818 Sep 4 20:54 alert_airman.log
$ mkdir flashback
$ chmod 755 flashback

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 5 06:10:06 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------------------------------------------------------------------------
9221

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE

--------------------------------------------------------------------------------
----------------
--------------------------------------------------------------------------------
--------------------
--------------------------------------------------------------------------------
----------
PROTECTION_MODE

--------------------------------------------------------------------------------
AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4176335025 MOUNTED
MAXIMUM PERFORMANCE

SQL> select NAME,DATABASE_ROLE,GUARD_STATUS from v$database;

NAME DATABASE_ROLE GUARD_S



--------------------------------------------------------------------------------
----------------
--------------------------------------------------------------------------------
AIRMAN PHYSICAL STANDBY NONE

SQL> select SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
--------------------
SESSIONS ACTIVE 4176335025 MOUNTED MAXIMUM PERFORMANCE

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

--------------------------------------------------------------------------------
----------
AIRMAN MOUNTED

SQL> show parameter log_archive_dest_

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/u01/oradata/airman/a
rchive VALID_FOR=(ALL_LOGFILES
,ALL_ROLES) db_unique_name=air
man_sj
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=airman_kj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE,PRIMA
RY_ROLE) db_unique_name=airman
_kj
log_archive_dest_3 string
log_archive_dest_4 string

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL>
SQL> alter system set log_archive_dest_2=defer;
alter system set log_archive_dest_2=defer
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_2 cannot be parsed

SQL> alter system set log_archive_dest_2=defer scope=memory;
alter system set log_archive_dest_2=defer scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_2 cannot be parsed


SQL> alter system set log_archive_dest_2=defer scope=both;
alter system set log_archive_dest_2=defer scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup


SQL> show parameter spfile

NAME TYPE VALUE



--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
spfile string

$ sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 5 06:26:40 2012

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE



--------------------------------------------------------------------------------
----------
AIRMAN MOUNTED

SQL> create spfile from pfile;

File created.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter dump

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/admin/airman/b
dump
core_dump_dest string /u01/app/oracle/admin/airman/c
dump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/admin/airman/u
dump
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2029008 bytes
Variable Size 150997552 bytes
Database Buffers 54525952 bytes
Redo Buffers 2162688 bytes
SQL> show parameter spfile

NAME TYPE VALUE



--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
spfile string /u01/app/oracle/product/10.2.0
/Db_1/dbs/spfileairman.ora
SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> show parameter log_archive_dest_

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_1 string LOCATION=/u01/oradata/airman/a
rchive VALID_FOR=(ALL_LOGFILES
,ALL_ROLES) db_unique_name=air
man_sj
log_archive_dest_10 string
log_archive_dest_2 string SERVICE=airman_kj LGWR ASYNC V
ALID_FOR=(ONLINE_LOGFILE,PRIMA
RY_ROLE) db_unique_name=airman
_kj
log_archive_dest_3 string
log_archive_dest_4 string

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL> alter system set log_archive_dest_2=defer scope=both;
alter system set log_archive_dest_2=defer scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE

SQL> alter system set log_archive_dest_2=defer scope=memory;
alter system set log_archive_dest_2=defer scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16024: parameter LOG_ARCHIVE_DEST_2 cannot be parsed


SQL> alter system set log_archive_dest_state_2=defer scope=both;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE



--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
log_archive_dest_state_2 string DEFER
SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> alter system set db_recovery_file_dest='/u01/oradata/flashback';
alter system set db_recovery_file_dest='/u01/oradata/flashback'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest_size=14G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/oradata/flashback';

System altered.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE



--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
db_recovery_file_dest string /u01/oradata/flashback
db_recovery_file_dest_size big integer 14G
SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
db_flashback_retention_target integer 1440
SQL> alter system set db_flashback_retention_target=2880;

System altered.

SQL> create pfile from spfile;

File created.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> create restore point before_open_standby guarantee flashback database;

Restore point created.

SQL> select scn, storage_size, time, name from v$restore_point;

SCN STORAGE_SIZE

--------------------------------------------------------------------------------
------------
TIME

--------------------------------------------------------------------------------
NAME

--------------------------------------------------------------------------------
1536320779 8192000
05-SEP-12 06.37.50.000000000 AM
BEFORE_OPEN_STANDBY

SQL> set linesize 200
SQL> col scn,storage_size,time,name format a25
SQL> select scn,storage_size,time,name from v$restore_point;

SCN STORAGE_SIZE TIME



--------------------------------------------------------------------------------
------------
--------------------------------------------------------------------------------
NAME

--------------------------------------------------------------------------------
1536320779 8192000 05-SEP-12 06.37.50.000000000 AM
BEFORE_OPEN_STANDBY

SQL> alter database activate standby database;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE



--------------------------------------------------------------------------------
PRIMARY

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2029008 bytes
Variable Size 150997552 bytes
Database Buffers 54525952 bytes
Redo Buffers 2162688 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME OPEN_MODE



--------------------------------------------------------------------------------
----------
AIRMAN READ WRITE

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------------------------------------------------------------------------
9221