A tutorial on the pattern recognition in the data series
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/misc/sql_pattern/sql_pattern.html
Thursday, 31 October 2013
Friday, 11 October 2013
DB upgrade shortly
This is a very generic and concise description of the upgrade operation an Oracle RDBMS environment to higher version.
That's all assuming everything went well ;-).
- run the Pre-Upgrade Tool - one may think this is an optional step, yet it seems it is not. Once I tried to run the whole operation without an earlier call to it and here You may look at what may go wrong and how to deal with it.
sh > sqlplus / as sysdba -- the Pre-Upgrade Tool -- one have to call it with the ORACLE_HOME set to current binaries -- (in contrast to the new binaries i.e. after upgrade) -- there is no need to copy it to the current home, yet one have to provide a full path -- of course one may expect a change in the name according to the version -- for example utlu121i for the version 12c sql> SPOOL preupg.log sql> @
/rdbms/admin/utlu112i sql> SPOOL OFF - change the ORACLE_HOME to the new home (as it is really inadvisable to install the new binaries in-place) and run the catalog upgrade
-- usually the docs say one should go to ORACLE_HOME/rdbms/admin -- before running the catalog upgrade and this is then specified without a path -- I used to run it as shown below sh > sqlplus / as sysdba sql> STARTUP UPGRADE sql> SPOOL db_upgrade.log sql> @?/rdbms/admin/catupgrd -- these steps below are actually not necessary or even impossible due to -- the shutdown included in catupgrd.sql sql> SPOOL OFF sql> SHUTDOWN IMMEDIATE
- now a normal start, some additional script and check if all seems to go well
sh > sqlplus / as sysdba sql> STARTUP sql> SPOOL postupg.log -- check if the upgrade went well sql> @?/rdbms/admin/utlu112s -- upgrade actions, which do not need UPGRADE mode sql> @?/rdbms/admin/catuppst.sql sql> SPOOL OFF -- compilation sql> @?/rdbms/admin/utlrp
That's all assuming everything went well ;-).
Monday, 7 October 2013
Breaking through redo corruption
Today we faced a not very welcome message - ORA-00333: redo log read error block 56010 count 8064. The database is in the NOARCHIVELOG mode and we do not perform backups ;-).
Of course I would not smile so eagerly if the problem would be serious - I must admit at once that there was a bright side of the situation - this is a test environment and rather easily able to reproduce, so take it into consideration, while looking what I did to this database.
At the beginning I must state that the traffic on this database was mainly First of all I started to clear logfiles (ALTER DATABASE CLEAR LOGFILE GROUP x;), but quickly run out of progress - the corrupted redo log was the CURRENT one i.e. the one I can not clear.
As we have no backup, the list of options is quite short - the second (and one of the last resorts is to use some hidden parameters to disable the recovery).
The two parameters I was interested in are _allow_resetlogs_corruption and _allow_read_only_corruption. It seems to me the first one is related more to redologs, while the second to datafiles. At the moment I assume one may try to use them together - the first one to get rid of the corrupted online redo, and the second one to open a database despite of corrupted datafiles.
As I found quite many articles on the subject (but mainly by googling and not on the MOS pages), I firstly realize it is enough to set _allow_resetlogs_corruption to TRUE in order to go forward. However when I did this, I hit the ORA-01139.
So I run recovery (RECOVER DATABASE UNTIL CANCEL), and it required archivelog with the 518 sequence (which is the sequence the corrupted online logfile was opened with) - suprisingly it went well, so I tried to open the database with the RESETLOGS option and it failed. Then I set both the _allow_resetlogs_corruption and _allow_read_only_corruption parameters to TRUE and start the database again. This time the instance terminated with the ORA-00704 bootstrap errors. As You already find out I failed to open it successfully - the next try bring the ORA-600 errors, and at this moment I gave up.
And finally a victory! I tried once again. This time I simply mounted the database and call the database recovery (with both parameters reset) - it failed with an info that I have to open the database, so I call the OPEN operation and it opened.
Of course I would not smile so eagerly if the problem would be serious - I must admit at once that there was a bright side of the situation - this is a test environment and rather easily able to reproduce, so take it into consideration, while looking what I did to this database.
At the beginning I must state that the traffic on this database was mainly First of all I started to clear logfiles (ALTER DATABASE CLEAR LOGFILE GROUP x;), but quickly run out of progress - the corrupted redo log was the CURRENT one i.e. the one I can not clear.
As we have no backup, the list of options is quite short - the second (and one of the last resorts is to use some hidden parameters to disable the recovery).
The two parameters I was interested in are _allow_resetlogs_corruption and _allow_read_only_corruption. It seems to me the first one is related more to redologs, while the second to datafiles. At the moment I assume one may try to use them together - the first one to get rid of the corrupted online redo, and the second one to open a database despite of corrupted datafiles.
As I found quite many articles on the subject (but mainly by googling and not on the MOS pages), I firstly realize it is enough to set _allow_resetlogs_corruption to TRUE in order to go forward. However when I did this, I hit the ORA-01139.
So I run recovery (RECOVER DATABASE UNTIL CANCEL), and it required archivelog with the 518 sequence (which is the sequence the corrupted online logfile was opened with) - suprisingly it went well, so I tried to open the database with the RESETLOGS option and it failed. Then I set both the _allow_resetlogs_corruption and _allow_read_only_corruption parameters to TRUE and start the database again. This time the instance terminated with the ORA-00704 bootstrap errors. As You already find out I failed to open it successfully - the next try bring the ORA-600 errors, and at this moment I gave up.
And finally a victory! I tried once again. This time I simply mounted the database and call the database recovery (with both parameters reset) - it failed with an info that I have to open the database, so I call the OPEN operation and it opened.
Wednesday, 2 October 2013
Why one can not connect to SYS with db link?
You may find the helpful article under this link (can-i-connect-a-database-link-as-sysdba).
If anything wrong with the link, try the article 119885 on the MOS.
If anything wrong with the link, try the article 119885 on the MOS.
Friday, 27 September 2013
Cloning 11gR2
In general it seems to be a simple operation.
First one needs to copy ORACLE_HOME content to the new location (local or remote). The documentation suggests the zip utility, but I think rsync is also a right tool (for a local copy switches -av, for a remote -avz).
Second call runInstaller
First one needs to copy ORACLE_HOME content to the new location (local or remote). The documentation suggests the zip utility, but I think rsync is also a right tool (for a local copy switches -av, for a remote -avz).
Second call runInstaller
$ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME="[the new home]" ORACLE_HOME_NAME="[the new home id]" ORACLE_BASE="/oracle"The docs do not mention the ORACLE_BASE specification, yet this is inevitable as without it the cloning process exits with information about lack of ORACLE_BASE value.
Wednesday, 25 September 2013
Utilities for examining redologs and data files (external link)
Utilities for examining redologs' and data files' content by David Litchfield (at the moment in Beta)
Subscribe to:
Posts (Atom)