Friday, June 8, 2012

Installing Oracle Grid Infrastructure - Summary

This is one of a few posts surrounding the installation of a single instance ASM database using grid 11.2.0.2 and database 11.2.0.2
The installations were carried out on the new Oracle Linux 6.2

Be aware that software versions up until 11.2.0.2 and BELOW are not certified to run 11.2.0.2 database and grid.
Therefore, there are funnies – but with a few changes here and there, they can be circumnavigated.

More to come…

Thursday, February 2, 2012

ORA-00600 in PL/SQL after patch 11.2.0.3

I recently patched a standard edition 11g UAT database from version 11.2.0.2 to 11.2.0.3

The following week I had a look at the alert log. I was greeted with ORA-00600 messages.


ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], [], [], [], [], []

ORA-06544: PL/SQL: internal error, arguments: [78402], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [78402]

Later on that day the users complained about their sessions dying when executing a certain package call from the web front end.

 

On metalink I found a bug note resembling the error which effects versions of Oracle < 11.2, starting at version 10.2.0.3.

Bug 6068126  

I opened up an SR with Oracle, shortly after they responded with a reference to 
Bug 13612575 (comes with a test case to reproduce the error)

It seems as if the bug was reintroduced in 11.2.0.3, though it comes in a different form, the conditions of its occurrence remain.
Both of these bugs surround the poor handling of REF CURSORs. Read the solutions in either of the bug notes to work around the problem.

The workaround suggested by the note was implement, and since then the alert log has been clear of errors.


Cheers

Wednesday, January 11, 2012

Flashback Version and Flashback Transaction Query

Flashback technology offers us the chance to traverse the undo tablespace for detailed version information of data changes within the database.

Flashback version is particularly useful for investigating complex applications covering a labyrinth of subroutines and conditions.
Below is a basic example of its use.


--PREP WORK

drop table ray.employee;
create table ray.employee (
  id       number,
  name     varchar2(60),
  position varchar2(20)
  );


insert into ray.employee VALUES (1,'TOPPER HARLEY','TOP GUN');
commit;

set lines 1000



select * from ray.employee;
        ID NAME                                                         POSITION

---------- ------------------------------------------------------------ --------------------
         1 TOPPER HARLEY                                                TOP GUN


update ray.employee set name = 'SPUD WESTERN';

commit;

update ray.employee set name = 'JOHN MCWHEELY';


commit;


update ray.employee set name = 'DUKE SPENCER', position='PILOT';


commit;


delete from ray.employee;


commit;




--View the record versions

set lines 1000
COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select TO_CHAR(VERSIONS_STARTTIME,'DD-MON-YY HH24:MI:SS') "start", 

       TO_CHAR(VERSIONS_ENDTIME,'DD-MON-YY HH24:MI:SS') "end", 
       versions_operation, 
       versions_xid,
       t.*
  from ray.employee versions between scn minvalue and maxvalue t 
 where id = 1;

start                end                  VE VERSIONS_XID       ID NAME                 POSITION

-------------------- -------------------- -- ---------------- ---- -------------------- --------------------
12-JAN-12 09:16:22                        D  0900180032010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:22   U  080013002F010000    1 DUKE SPENCER         PILOT
12-JAN-12 09:16:13   12-JAN-12 09:16:13   U  07001C0038010000    1 JOHN MCWHEELY        TOP GUN
12-JAN-12 09:15:58   12-JAN-12 09:16:13   U  0600160035010000    1 SPUD WESTERN         TOP GUN
                     12-JAN-12 09:15:58                          1 TOPPER HARLEY        TOP GUN

--Above, the start time and scn of the record version are given to use as well as the data of that version. We also see the type of transaction which generated the version (U/D). We can look as far back as the undo tablespace size and retention period allow us to.

--Using FLASHBACK QUERY with VERSION 
--For more transaction related information, we can use FLASHBACK_TRANSACTION_QUERY view to see details about the transaction.
The privilege 
FLASHBACK ANY TABLE must be given to the person doing the selecting.




COLUMN start FORMAT a20
COLUMN end FORMAT a20
COLUMN VERSIONS_STARTSCN FORMAT 999999999999
COLUMN VERSIONS_ENDSCN FORMAT 999999999999
COLUMN VERSIONS_XID FORMAT 999999999999
COLUMN VERSIONS_OPERATION FORMAT a2
COLUMN LOGON_USER FORMAT a10
COLUMN UNDO_SQL FORMAT a10
COLUMN TABLE_NAME FORMAT a10
COLUMN TABLE_OWNER FORMAT a10
COLUMN XID FORMAT a20
COLUMN ROWID FORMAT a20
COLUMN OPERATION FORMAT a20
COLUMN id FORMAT 999
COLUMN name FORMAT a20
COLUMN position FORMAT a20

select * 

  from flashback_transaction_query where xid in (
select VERSIONS_XID
  from ray.employee versions between scn minvalue and maxvalue r 
 where versions_operation = 'U');

XID                   START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION            TABLE_NAME TABLE_OWNE ROW_ID              UNDO_SQL

-------------------- ---------- --------- ---------- --------- ---------- ------------ -------------------- ---------- ---------- ------------------- ----------
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
0600160035010000        1354577 12-JAN-12    1354589 12-JAN-12 SYS                   2 BEGIN
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
07001C0038010000        1354589 12-JAN-12    1354597 12-JAN-12 SYS                   2 BEGIN
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   1 UNKNOWN              EMPLOYEE
080013002F010000        1354597 12-JAN-12    1354600 12-JAN-12 SYS                   2 BEGIN

Saturday, January 7, 2012

Database Recovery #6 - Duplication/Cloning from RMAN backup

The steps below show how to perform a BACKUP BASED database duplication (not an active
duplication) The TARGET database is the source database you draw the backup pieces from, the AUXILIARY database is where you will restore to and recover up until.

A backup must be available and must be accessible from the auxiliary database via Oracle .Net
The server on which RMAN is being operated MUST have access the backup pieces. If the AUXILIARY db is going to reside on a different machine, the machine must have the backup location mapped from the machine on which the TARGET resides.

This guide was used with a TARGET instance in archivelog mode.
Its not necessary to have both instance using the same app tree but the TARGET and AUXILIARY must share the exact same application tree setup (versions, patches).
Compare with both trees using $ORACLE_HOME/OPatch/opatch lsinventory


--First backup the target database
. oraenv 
orcl
rman target=/
backup database plus archivelog delete input;


--Pick a time, SCN or archivelog number to duplicate AUXILIARY database up until. Do this by looking 
--at the catalog of your TARGET DATABASE to determine what is possible.
list backup;
list archivelog all;


--You want to make sure the TIME,SCN or SEQ you pick coincides with the backed up archivelogs you have available in the TARGET instance. RMAN will tell you flat out if you
--pick a value which is not possible to recover to.
--In the guide below, I took a backup a few hours earlier and backed up archivelog instead.


--PREPARE THE AUXILIARY DATABASE environemnt.
--Add new database to /etc/oratab : I use the SID - DUP
vi /etc/oratab

dup:/u01/app/oracle/product/11.1.0/db_1


--Add the new database to the listener.ora file
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dup)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = dup)
    )
  )


--Add the new database to the tnsnames.ora file
DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SID = dup)
      (SERVER = DEDICATED)
    )
  )

--Give the AUXILIARY db an init.ora file by duplication the target database init file and changing the values.

--Change the control_files parameter
--Change the core_dump_dest name
--Change the db_name
--Change the dispatchers name
--Add the convert file name parameters 
--Change the log_archive_dest_1 location
----DB_FILE_NAME_CONVERT
----LOG_FILE_NAME_CONVERT


[oracle@vmorcl ~]$ cp $ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initdup.ora
[oracle@vmorcl ~]$ vi $ORACLE_HOME/dbs/initdup.ora
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0'
*.control_files='/u01/app/oracle/oradata/dup/control01.ctl','/u01/app/oracle/oradata/dup/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/dup/cdump'
*.db_block_size=8192
*.db_cache_size=128M
*.db_domain='localdomain'
*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_name='dup'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/dup/'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=10000000000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_dest_1='LOCATION=/redologs/stage2'
*.log_buffer=10485760
*.memory_max_target=512M
*.memory_target=512M
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.plsql_warnings='DISABLE:ALL'
*.processes=150
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=2304K
*.sga_max_size=372M# internally adjusted
*.sga_target=372M
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTS'


--Create the datafile folders
mkdir /u01/app/oracle/oradata/dup/


--Create the diagnostic folders
mkdir /u01/app/oracle/admin/dup/dup
mkdir /u01/app/oracle/admin/dup/adump
mkdir /u01/app/oracle/admin/dup/bdump
mkdir /u01/app/oracle/admin/dup/cdump


--Reload the listener.
lsnrctl reload


--Duplicate password file
cp /u01/app/oracle/product/11.1.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.1.0/db_1/dbs/orapwdup



--Startup the duplicate database with nomount
. oraenv
dup
sqlplus /nolog
conn / as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initdup.ora';
exit;


--Connect to the target and auxiliary databases
rman target=sys/oracle@orcl auxiliary=sys/oracle@dup


--Duplicate the database
--You must have a full backup (cold or warm), and you must specify an until sequence,time or scn. 
--Below I use up until past the last sequence number in the archivelog backup
run {
  DUPLICATE TARGET DATABASE TO dup;
  SET UNTIL SEQUENCE 1;
}


--Provided all went well, your instance should be up and running. 

New Version of Putty

Your favourite terminal application – putty – has been upgraded. It seems to use the same registry as your regular putty, so there is no need to reconfigure. Just run it and use your saved configurations.

Transparency and automatic reconnect after coming out of standby are some of the new features.

Have a look at:

http://puttytray.goeswhere.com/

A taste of the action:

Thursday, December 22, 2011

Database Recovery #5 - RMAN Recover everything

--Restoring a database from scratch.
--These steps remove the entire instance, and the restore and recover from backup pieces outside the FRA. Please be careful
with the remove script.


--Prep database cleanout
sqlplus /nolog
conn / as sysdba
!rm /tmp/drop_orcl.sql
spool /tmp/drop_orcl.sql
select '!rm '||value from v$parameter where name = 'spfile';
select '!rm '||replace(value,'spfile','init') from v$parameter where name = 'spfile';
select '!rm '||name from v$controlfile;
select '!rm '||file_name from dba_data_files;
select '!rm '||member from v$logfile;
select '!rm -fr '||value||'/ORCL' from v$parameter where name = 'db_recovery_file_dest';
spool off


exit


--Take a backup and note the DBID of the database you are going to use to restore the instance.
[oracle@vmorcl backup]$ rman target /


Recovery Manager: Release 11.2.0.1.0 - Production on Thu Dec 22 20:29:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1296288227)

--Set controlfile autobackup ON
RMAN> configure controlfile autobackup on;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>


--Clear out backups and backup the database, make sure SPFILE is backed up
delete noprompt backup;
backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--List and copy the backup pieces from the recovery area to another location
list backup;


host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6ygq1p_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_nnndf_MASTER_DB_7h6ygrg9_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_12_22/o1_mf_annnn_MASTER_LOG_7h6yh7t6_.bkp /u01/backup/';
host 'cp /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_22/o1_mf_s_770589705_7h6yh9gp_.bkp /u01/backup/';


--Check the backups were moved
host 'ls -rtl /u01/backup';


exit


--Once files are in the location, clear out the instance using the spool commands from the prep, and kill the instance.
--MAKE SURE YOU HAVE THE DBID BEFORE FIRING THIS OFF!
sqlplus / as sysdba @/tmp/drop_orcl.sql


--This should throw an error if the instance has be removed.create table sys.test as select * from v$instance;


--shutdown
shutdown abort;
exit;


--Right, the database is GONE. Time to restore and recover.--set DBID
rman target=/
set DBID=1296288227
--You have no spfile or initfile, the only way to startup is using the default init.ora file supplied by oracle

startup nomount force;


--Restore the spfile from the autobackup piece.run {
restore spfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}
shutdown abort;



--Startup using the restored spfile 
startup nomount;


--Restore the control file from the same autobackup piece
run {
restore controlfile from '/u01/backup/o1_mf_s_770589705_7h6yh9gp_.bkp';
}


--Mount the database with the new found control files.
alter database mount;


--Add the rest of the backup pieces to the catalog
catalog start with '/u01/backup/';


--Restore the database to its former glory.
--Get the last sequence number.


list backup of archivelog all;


--Restore the database. Set the UNTIL SEQUENCE clause to the highest sequence + 1


run {
    set until sequence 3;
    restore database;
    recover database;
    }


--Be a hero - open the database with reset logs since we used a backup control file.


alter database open resetlogs;


--Once the database is  open, it is important to take a NEW backup!


backup database tag="MASTER_DB" plus archivelog tag="MASTER_LOG" delete input;


--Done. To handle the password file, either create a new one, or copy the one from the original hosts app tree.

Tuesday, December 20, 2011

Database Recovery #4 - RMAN Recover control file without FLASH_RECOVERY_AREA

--Open rman
rman target=/
--Ensure autobackup is set to on

configure controlfile autobackup on;


--Take note of the DBID, delete backups, make a new backup of the datbase and logs. 
backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;


--Ensure you have a copy of the controlfile
list backup of controlfile;

exit;


--Move autobackup off to the a new location OUT OF THE instance's FLASH_RECOVERY_AREA
--%F is not just a filename format. It means FOLDERS as well. /FRA/{%F} = /FRA/{ORCL/AUTOBACKUP/YYYYMMDD}
mv /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_12_20/o1_mf_s_770381286_7h0ly6sk_.bkp /u01/backup/ORCL/autobackup/2011_12_20


--Connect to instance and remove the control files from the
database

sqlplus /nolog
conn / as sysdba


spool /tmp/delete_controlfile.sql
select '!rm '||name from v$controlfile;
spool off


--Drop controlfiles
@/tmp/delete_controlfile.sql


--Shutdown abort
shutdown abort;
exit;


--Reconnect to RMAN to recover the database
rman target /


--Startup database in NOMOUNT state and set the DBID (DBID is used when no FRA is available)
startup nomount;
set dbid=1296288227


--Restore the controlfile from the relocated autobackup piece from the new FRA location
restore controlfile from autobackup db_recovery_file_dest='/u01/backup';


--Mount, recover and open resetlogs the instance. 
alter database mount;
recover database;
alter database open resetlogs;

--Delete backups from previous incarnation

delete noprompt backup;

--Take a new backup

backup database tag='MASTER_DB' plus archivelog tag='MASTER_LOGS' delete input;