Right way to drop the AWR is not catnoawr.sql. It’s documented 436386.1.
Incase ifyou have dropped awr one way to bring it back is catsvrm.sql. Flush the shared pool before that.
Dropping the AWR using catnoawr might have deleted row from WRM$_DATABASE_INSTANCE, restarting the instance will populate the data in this table.
Pls test this in test environment.
convert this post to pdf. In sqlt.zip there is a script called coe_xfr_sql_profile.sql which will help in generating the sql_profile from other_xml of v$sql_plan and output in executable format. This helps in moving the plan across the environment and also before creating profile helps you in editing the hints.
SQL> @/tmp/coe_xfr_sql_profile.sql Parameter 1: SQL_ID (required) Enter value for 1: bcmu3by7zdb03 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 2415307779 Values passed: ~~~~~~~~~~~~~ SQL_IDÂ Â Â Â Â Â Â Â : "bcmu3by7zdb03" PLAN_HASH_VALUE: "2415307779" Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options
convert this post to pdf. New diagonistic tool is introduced in 11g. Using which we can gather all the information relevant to an issue and upload to oracle or investigate by yourself.
adrci is executable in $ORACLE_HOME/bin location
$ adrci ADRCI: Release 11.1.0.7.0 - Production on Mon Jul 26 04:08:33 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved. ADR base = "/opt/apps/oracle" adrci> adrci> show home ADR Homes: diag/rdbms/db1 diag/rdbms/db2 diag/rdbms/db3 diag/rdbms/db4 diag/tnslsnr/hostname/listener_bor3rep adrci> set homepath diag/rdbms/db1 adrci> show problem ==> this will report the problem recently poped up in the db. ************************************************************************* PROBLEM_ID          PROBLEM_KEY                                                LAST_INCIDENT       LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 3                   ORA 7445 [_memset()+120]                                   32093               2010-07-25 18:01:06.962092 -04:00 4                   ORA 7445 [_doprnt()+44]                                    32077               2010-07-25 18:01:03.642031 -04:00 2                   ORA 4030                                                   32270               2010-07-25 18:01:00.837604 -04:00 1                   ORA 7445 [ioc_pin_shared_executable_object()+952]          28842               2010-06-05 22:39:34.757161 -04:00 4 rows fetched If you want to diagonise 4030 error, then adrci> ips pack problem 2 in /tmp Generated package 3 in file /tmp/ORA4030_20100726041042_COM_1.zip, mode complete
Upload the trace file to oracle or use it for investigation. Has all the information relevant to 4030 error.
convert this post to pdf. When the primary archive location fills, there is an option of writing to alternate archivelog location. These parameter needs to be set.
log_archive_dest_1='LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' log_archive_dest_2='LOCATION=/other_destination_for_archiving' log_archive_dest_state_1='enable' log_archive_dest_state_2='alternate'
You could use the following to revert back after failover:
alter system set log_archive_dest_state_1 = enable; alter system set log_archive_dest_state_2 = alternate;
But make note of below restriction when setting this.Metalink#369120.1
” The REOPEN attribute takes precedence over the ALTERNATE attribute. The alternate destination is
used only if one of the following is true:
- The NOREOPEN attribute is specified.
- A value of zero (0) is specified for the REOPEN attribute.
- A nonzero REOPEN attribute and a nonzero MAX_FAILURE count have been exceeded. ”
It’s preferable to set NOREOPEN to the default location and enable this option.
convert this post to pdf.