My Writings

We have a generic RMAN script that backups up most of our database, however we noticed that on one day one of the servers archive logs was increasing in size and after some investigation we found the following issue.

The Problem

RMAN> print script full_disk_disk_backup;
printing stored global script: full_disk_disk_backup
{sql ‘alter system archive log current’;
sql ‘alter database backup controlfile to trace’;
allocate channel d1 device type disk;
set command id to ‘rmanbck’;
backup spfile;
change archivelog all crosscheck;
delete noprompt expired archivelog all;
backup as compressed backupset database plus archivelog not backed up;
backup current controlfile;
resync catalog;
release channel d1;
}

RMAN> show all;
starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO ‘DISK’;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/AXIOMPRY/flash_recovery_area/AXIOMPRYCTL_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/backup/AXIOMPRY/flash_recovery_area/SNAP_AXIOMPRY_ctl.db’;

RMAN> list backupset summary;
starting full resync of recovery catalog
full resync complete

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — – – ———– ————— ——- ——- ———- —
3457626 B A A DISK 24-AUG-10 1 1 YES TAG20100824T230154
3457627 B A A DISK 24-AUG-10 1 1 YES TAG20100824T230154
3457628 B A A DISK 25-AUG-10 1 1 YES TAG20100824T230154
3457629 B A A DISK 25-AUG-10 1 1 YES TAG20100824T230154
3457630 B A A DISK 25-AUG-10 1 1 YES TAG20100824T230154
3457631 B A A DISK 25-AUG-10 1 1 YES TAG20100824T230154
3458063 B A A DISK 25-AUG-10 1 1 YES TAG20100825T020122
3465852 B A A DISK 25-AUG-10 1 1 YES TAG20100825T230130
3465932 B A A DISK 25-AUG-10 1 1 YES TAG20100825T233814
3476405 B A A DISK 26-AUG-10 1 1 YES TAG20100826T230126
3476486 B A A DISK 26-AUG-10 1 1 YES TAG20100826T233852
3489274 B A A DISK 27-AUG-10 1 1 YES TAG20100827T230206
3489275 B F A DISK 27-AUG-10 1 1 YES TAG20100827T230854
3489352 B A A DISK 27-AUG-10 1 1 YES TAG20100827T233851
3489428 B F A DISK 27-AUG-10 1 1 NO TAG20100827T233905
3498634 B A A DISK 28-AUG-10 1 1 YES TAG20100828T230147
3498635 B F A DISK 28-AUG-10 1 1 YES TAG20100828T230434
3498704 B A A DISK 28-AUG-10 1 1 YES TAG20100828T233400
3498766 B F A DISK 28-AUG-10 1 1 NO TAG20100828T233406
3498780 B F A DISK 28-AUG-10 1 1 NO TAG20100828T233412
3506694 B A A DISK 29-AUG-10 1 1 YES TAG20100829T230135
3506695 B F A DISK 29-AUG-10 1 1 YES TAG20100829T230342
3506763 B A A DISK 29-AUG-10 1 1 YES TAG20100829T233257
3506825 B F A DISK 29-AUG-10 1 1 NO TAG20100829T233304
3506839 B F A DISK 29-AUG-10 1 1 NO TAG20100829T233310
3516494 B A A DISK 30-AUG-10 1 1 YES TAG20100830T230143
3516495 B F A DISK 30-AUG-10 1 1 YES TAG20100830T231802
3516598 B A A DISK 30-AUG-10 1 1 YES TAG20100830T234835
3516669 B F A DISK 30-AUG-10 1 1 NO TAG20100830T234855
3516683 B F A DISK 30-AUG-10 1 1 NO TAG20100830T234902
3527815 B A A DISK 31-AUG-10 1 1 YES TAG20100831T230129
3527816 B F A DISK 31-AUG-10 1 1 YES TAG20100831T231507
3527909 B A A DISK 31-AUG-10 1 1 YES TAG20100831T234505
3527980 B F A DISK 31-AUG-10 1 1 NO TAG20100831T234517
3527994 B F A DISK 31-AUG-10 1 1 NO TAG20100831T234524

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 3489275 27-AUG-10
Backup Piece 3489279 27-AUG-10 /backup/AXIOMPRY/flash_recovery_area/AXIOMPRY/AXIOMPRM/backupset/2010_08_27/o1_mf_nnndf_TAG20100827T230854_67hg77vs_.bkp
Backup Set 3489428 27-AUG-10
Backup Piece 3489430 27-AUG-10 /backup/AXIOMPRY/flash_recovery_area/AXIOMPRYCTL_c-1101749631-20100827-00
Backup Set 3498766 28-AUG-10
Backup Piece 3498768 28-AUG-10 /backup/AXIOMPRY/flash_recovery_area/AXIOMPRY/AXIOMPRM/backupset/2010_08_28/o1_mf_ncnnf_TAG20100828T233406_67l42hxr_.bkp

As we can see from the list above, we have obsolete backups from the 27 August, however we still have retained archivelogs from before the obsolete period.

We can confirm the sequences are not obsolete and since this database has a logical standby the archivelogs have been applied.

SQL> col thr form 99
col dest form 99
col seq form 9999999
col name form a40
col MB form 99,999
col “1st_time” form a21
col next_time form a21
col completed form a25
col bk form 999SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL> select
2 dest_id dest,
3 thread# thr, sequence# seq ,status, applied, backup_count bk, to_char(first_time,’dd-mm-yyyy hh24:mi:ss’) “1st_time”
4 , to_char(next_time,’dd-mm-yyyy hh24:mi:ss’) next_time,creator , name
5 from v$archived_log
6 where sequence# between 28405 and 28414
7 order by first_time;

DEST THR SEQ S APP BK 1st_time NEXT_TIME CREATOR NAME
—- — ——– – — —- ——————— ——————— ——-
1 1 28405 D YES 0 08-08-2010 21:00:48 08-08-2010 22:07:01 ARCH
1 1 28406 D YES 0 08-08-2010 22:07:01 08-08-2010 23:01:10 ARCH
1 1 28407 D YES 0 08-08-2010 23:01:10 08-08-2010 23:01:24 ARCH

DEST THR SEQ S APP BK 1st_time NEXT_TIME CREATOR NAME
—- — ——– – — —- ——————— ——————— ——-
1 1 28408 D YES 0 08-08-2010 23:01:24 08-08-2010 23:31:11 ARCH
1 1 28409 D YES 0 08-08-2010 23:31:11 09-08-2010 05:02:21 ARCH
1 1 28410 D YES 0 09-08-2010 05:02:21 09-08-2010 07:43:02 ARCH

DEST THR SEQ S APP BK 1st_time NEXT_TIME CREATOR NAME
—- — ——– – — —- ——————— ——————— ——-
1 1 28411 A YES 0 09-08-2010 07:43:02 09-08-2010 08:49:09 ARCH
/archive/AXIOMPRY/log/1_28411_662147521.dbf

1 1 28412 A YES 0 09-08-2010 08:49:09 09-08-2010 09:31:35 ARCH
/archive/AXIOMPRY/log/1_28412_662147521.dbf

1 1 28413 A YES 0 09-08-2010 09:31:35 09-08-2010 10:04:41 ARCH

DEST THR SEQ S APP BK 1st_time NEXT_TIME CREATOR NAME
—- — ——– – — —- ——————— ——————— ——-
/archive/AXIOMPRY/log/1_28413_662147521.dbf

1 1 28414 A YES 0 09-08-2010 10:04:41 09-08-2010 10:45:15 ARCH
/archive/AXIOMPRY/log/1_28414_662147521.dbf

10 rows selected.

Solution
At the moment, why the archive logs are held onto is a mystery. However to keep the system from filling up, we can remove archivelog and backups that have exceeded the retention period.

RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE – 5′;
RMAN> DELETE NOPROMPT BACKUP COMPLETED BEFORE ‘SYSDATE – 5′;

Recently I upgraded a database from 10.2.0.3 to 11.1.0.7 however after testing, the customer found that their application wasn’t performing well under the new version. They had a number of other tasks on so they asked me to downgrade them back to the 10.2.0.3 version. These are the steps I took to successfully take them back to their original version.

This applies to Oracle Server – Enterprise Edition – Version: 11.1.0.6 to 11.1.0.6 – Release: 11.1 to 11.1

You can downgrade both major releases and patchset releases, based on the original version from which the database was upgraded. Major release downgrades are supported back to 10.2 and 10.1. you can upgrade from any patch release of 10.1 or 10.2, but you should install the latest patch release in the Oracle home you intend to use after downgrading.

Downgrade to 9i or lower is only possible using export/import.

You can only downgrade to the release from which you upgraded.

For example, if you upgraded from Oracle Database 10g release 1 (10.1) to Oracle Database 11g release 1 (11.1), then you could not subsequently downgrade to Oracle Database 10g release 2 (10.2); you could only downgrade to Oracle Database 10g release 1 (10.1).

Release 10.1.0 :  Downgrade possible to 10.1.0.5.0 and above

If the release number of your Oracle10g Release 1 (10.1) database is lower than 10.1.0.5, then you should install the latest patch for Oracle Database 10g release 1 (10.1) prior to downgrading.

Release 10.2.0 : Downgrade possible to 10.2.0.3.0 and above

If the release number of your Oracle 10g Release 2 (10.2) database is lower than 10.2.0.3, then you should install the latest patch release for 10.2 prior to downgrading.

( NOTE : If you have Messaging Gateway or Workspace Manager in your database, then be aware that neither of them are part of Oracle Database patch sets prior to release 10.1.0.6 and release 10.2.0.4. Therefore, you must separately apply all relevant patches to a release 10.2.0.3 or 10.1.0.5 Oracle home before downgrading.)

1) Check for Incompatibilities
———————————
If the compatibility level of your Oracle Database 11g release 1 (11.1) database is 11.1.0 or higher, then you are not able to downgrade. Your compatibility level is determined by the setting of the COMPATIBLE initialization parameter. Check your COMPATIBLE initialization parameter setting by issuing the following SQL statement:

SQL> SELECT name, value, description FROM v$parameter WHERE name='compatible';

If you are downgrading to 10.2, then the COMPATIBLE initialization parameter must be set to 10.2.0 or lower.

If you are downgrading to 10.1, then the COMPATIBLE initialization parameter must be set to 10.1.0.

2) Perform Complete Backups <as a precautionary measure> :
——————————————————————-
Before attempting the downgrade, perform full backups of the Database files.
There is no need for software backup here, as we are not performing any changes to the software.

3) Downgrade the database
——————————
Complete the following steps to downgrade your Oracle Database 11g release 1 (11.1) database to a major release or a relevant patchset upgrade:

1) If you have enabled Oracle Database Vault on your database, then you must disable it before downgrading the database.

If you are downgrading a clustered RAC database, then shut down the instance completely and change the CLUSTER_DATABASE initialization parameter to false. After the downgrade, you must set this parameter back to true.

2 ) Log in to the system as the owner of the Oracle Database 11g release 1 (11.1) Oracle home directory.

3 ) This is an optional step only if the Enterprise Manager Database Control is already configured for the database
Stop the Database Control, as follows:

i. Set the ORACLE_SID environment variable to the <SID> :

$ export ORACLE_SID=<sid> <==== In UNIX

ii. Execute the following command:

$ $ORACLE_HOME/bin/emctl stop dbconsole

If the database being downgraded is a Real Application Clusters (RAC) database,this step should be performed for all the instances.

4) If you are downgrading an Oracle RAC database to 10g Release 1 (10.1), then you must remove extra voting disks before shutting down the Oracle Clusterware stack.

i. To see the number of voting disks used and to list voting disk paths, run the following command:

$ Oracle_Clusterware_Home/bin/crsctl query css votedisk

ii. Remove each additional voting disk you find in the previous step by running the following command, where path is a voting disk path discovered in the previous step:

$ Oracle_Clusterware_Home/bin/crsctl delete css votedisk path

5) At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

$ cd $ORACLE_HOME/rdbms/admin

Here the Oracle Home is the 11G oracle home.

6) Start SQL*Plus.

$ sqlplus /nolog

7) Connect to the database instance as a user with SYSDBA privileges.

SQL> connect "/  as sysdba"

Start up the instance in DOWNGRADE mode:

SQL> STARTUP DOWNGRADE

You might be required to use the PFILE option to specify the location of your initialization parameter file.

9) Drop the SYSMAN schema:

SQL> DROP USER sysman CASCADE;

Note : You cannot downgrade the database without dropping the sysman schema. If you try to downgrade the database without dropping sysman you will get the following errors.

*ERROR at line 1:ORA-20000: Direct downgrade of EM Database Control not supported.ORA-06512: at line 3

When you upgrade the database you should take a backup of the EM repository so that when you downgrade the database, the repository can be restored. You can use ‘emdwgrd’ utility from 11g oracle home to save the Database Control files and data. Refer the Upgrade Guide for more details.

Ensure that all the components and objects are valid and in 11G version.

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version

from  dba_registry order by comp_name;

SQL> select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;

10) Set the system to spool results to a log file for later verification of success

SQL> SPOOL downgrade.log

11) Run catdwgrd.sql:

SQL> @catdwgrd.sql

Note:

• You must use the version of the script included with Oracle Database 11g Release 1 (11.1).
• You must run the script in the Oracle Database 11g Release 1 (11.1) environment.
• The script downgrades all Oracle Database components in the database to the major release or 11g patch release from which you originally upgraded.

If you encounter any problems when you run the script, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the script.

If the downgrade for a component fails, then an ORA-39709 error is displayed and the SQL*Plus session terminates without downgrading the Oracle Database data dictionary. All components must be successfully downgraded before the Oracle Database data dictionary is downgraded. You must identify and fix the problem before rerunning the catdwgrd.sql script.

12) Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Check the spooled output file for any errors. Correct any problems you find in this file and rerun the downgrade script if necessary.

13) Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

14) Exit SQL*Plus and shutdown the listener.
>lsnrctl stop

15) If your operating system is Linux or UNIX, then change the following environment variables to point to the directories of the release to which you are downgrading:

• ORACLE_HOME
• PATH

16) Restore the configuration files (for example, parameter files, password files, and so on) of the release to which you are downgrading.

If this is an Oracle RAC database, execute the following command to return the database to single instance mode:

SET CLUSTER_DATABASE=FALSE

17) At a system prompt, change to the ORACLE_HOME/rdbms/admin directory of the previous release.

You need to apply the patch 6118038 on 10.2.0.3 oracle home before starting the database in upgrade mode. If you do not apply this patch and go ahead an run the script catrelod.sql it will error out with the error ORA-00600:[koputilcvto2n]. To resolve this issue do the following

Download and apply the patch 6118038 on 10.2.0.3 oracle home.

18) Start SQL*Plus.

19) Connect to the database instance as a user with SYSDBA privileges.

SQL> connect "/ as sysdba"

20) Start up the instance:

SQL> STARTUP UPGRADE

21) Set the system to spool results to a log file for later verification of success:

SQL> SPOOL reload.log

22) Run catrelod.sql:

SQL> @catrelod.sql

23) If you are downgrading to release 10.1.0.5 and you have XDB in your database, run the following script after running catrelod.sql:

24) Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

25) Run the following SQL statements to check that the database is properly downloaded

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

SQL> select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;

Some components may show INVALID status after reload.

26) Shut down and restart the instance for normal operation:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Run the utlrp.sql script from $ORACLE_HOME/rdbms/admin :

SQL> @utlrp.sql

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on. Check again with the SQL statements.

It’s so cold at home!

more images are available at http://www.mrmarkyoung.com

We woke up to the sound of rain and it’s only the second day of rain we have had since in Phuket. We needed to get some final shopping done before coming back home, so back to Jungceylon and then we wandered back along the markets in the rain.

The hotel gave us a late check out which gave us more time to pack and get ready to leave. After checking out, we all met for lunch at Oasis and enjoyed our last official Thai meal together.

We farewelled Sue and Janette and waited for our pickup which was 20 minutes earlier than booked and we had to sit at the airport for a few hours before our flight. We arrived in Bangkok and walked to the gate to wait for our next flight. Our flight to Melbourne wasn’t that busy and Bron and I moved to a seat where we had ample leg room.

The flight left 1/2 hour late, but we managed to catch up the time and arrived in Melbourne on schedule. A long drive home due to road works on the Tulla freeway and an early night. Another great holiday with great people.  Bron and I will travel to Bangkok in October.

Another early-ish start and we were all off on our tour to Pang Nga Bay. We took a speed boat out, which was a great way to to do it as it was so much quicker than the big slow boats.

We arrived at James Bond Island first and had an hour to look around the island, at the “rock” and the cave. Then we took off again and made our way to Koh Panyi, a muslim village on stilts.

We had a fantastic lunch at the restaurant there over the water and then had time to explore through t he village and look at there stalls – everything the same, same, not different. It was great to see how these people live.

Back on the the boat and to Hong Island, where we met our “canoes”. There was two of us to one canoe, with our “paddler”. They took us through the caves and into a beautful, tranquil inlet, or, as they called it “honeymoon room”, and pointed out many rock formations along the way. We all had a good laugh with our boys, especially Sue and Jeanette with “Zuzu” who wanted to have “honeymoon” with them!!! Next, and last stop, was Naka Island, where we hired deck chairs and relaxed on the beach with a couple of beers and a lovely swim, before our short trip back to the pier. A very enjoyable, and full, day was had by all.

For our last dinner we head up the hill towards Kamala to “Pan Yaah” restaurant, overlooking the whole of  Patong Beach. We had a lovely meal. Greg had the biggest prawns you could ever see (as big as a lobster).

Then we got the tuk tuk back to Bangla Road and all made our way spearately back down the beach road (with quite a few stops along the way at the shops) to Porn’s bar for a final drink together.

Bron and Ibooked ourselves in a Discovery Diving trip to Phi Phi Island. We started the day by being collected from the Hotel at 7.30 and we made our way down to the pier at Chalong Bay.

No time wasted, the boat head off on our three hour cruise (a three hour cruise!!!!). We arrived at the first small island and readied ourselves into our wetsuits, vests and tanks, fins and goggles.

The adrenalin pumping, we followed Roger, our instructor, into the water and then there was no turning back. Bron struggled to get co-ordinated and found the first submerge a bit tough, but we succeeded on the second attempt and soon found ourselves amongst the most beautiful corals, sea life and fish imaginable.

The water was not fantastically clear, but the abundance of aquatic life made up for it. The 40 minutes passed so quick and before we knew it dive number 1 was all over. Back on the boat and a great Thai lunch and then we made our way to the second dive site. Again it was a scurry into our gear and in we go. The second dive was also fantastic and we even swam through an underwater cave – awesome! Once again, 40 minutes and it was all over. Back on the boat and headed back (3 hours again) to the dock. A fantastic day, and a great experience.

We didn’t get back to the hotel until around 7pm, and as the others had already left for dinner, we went to the Ban Yin Dee restaurant, oveerlooking Patong Beach. We had a lovely dinner and cocktails, but we were both spent so we headed home, had lovely massage and then to bed.

This morning I decided to get up and try and take some pictures. I haven’t taken many for some time and thought it might be a good opportunity.  Bron and I walked down to the river and watched a number of local fishermen going out for the day.

Above: Fishermen takening their boat out for a day of fishing.

Above: Fishermen get ready for a day of fishing.

After breakfast, the rest of the gang sat by the pool, while Bron and I went to the Le Meredian for our Suba Diving introduction. At first, Bron had trouble with the breathing, but after a few trips around the pool, she got the hang of it and nailed it.

The afternoon was spent relaxing with a bit of shopping and laying by the pool.  Dinner at The Small View Point Restaurant which is on the coastal road from Kata to Rawai.

Another interesting day in Patong! We went to Phuket Town to explore, however the hotel bus only went to the shopping centre which we had already been to, so we decided to catch a taxi to the city centre. Once arriving there, we found not much around, and we were approached by a local taxi driver who wanted to take us around the island for THB40 which I felt was too cheap and maybe there was some catch.

We left Geoff and Rosie with the taxi driver and they went off with him. Bron and myself just walked around for a bit and caught a taxi back to the shopping centre were we caught the bus back to the hotel.

We ate lunch at one of our favourite restaurants at the back of O Top and back to the hotel to arrange our diving in Phi Phi Island for Wednesday.

A swim in the pool followed by drinks at the bar, after we went to Sue and Jeanette’s room for further drinks and after that we wanted down to one of the bars, followed by dinner at Loma on the beach.

Another early night for some!

Another magnificent day in Paradise and another sunny day. After breakfast, we spent the morning by the pool followed by lunch at Oasis next door. Sue recommend we hire some Tuk-Tuk’s and travel south along the island and we can do a beach hop. We first went to Karon beach, we went through the market, but it was so hot and we hit the beach and all went for a swim.

The sand and water was so much cleaner than Patong. From there we travelled further south down to Kata beach. We didn’t swim there, however the girls were content on haggling with the beach vendors and they purchased many items from them. After that, we all enjoyed a pancake from one of the local vendors.

We travelled up the steep hill to the After Beach Bar where we sipped on cocktails and waited for the sunset. This spot is breath-taking, and we all agreed well worth another visit, maybe for dinner.  The waitress were the worst lady boy’s we have seen!

We then jumped back in our tuk-tuk and headed back to Patong, through the streets of Kata and Karon, which is also very pretty by night. Back at the “home” a few of us headed for a quiet dinner and then an early night all round.

Phuket hasn’t changed much since we were hear last year. We have done the usual things, shop, massage, swim, eat and drink, but not in that order. We have been back to see most of the people  we have met.

Greg had a big night with Danny from Lekey’s bar and hasn’t been out of bed in two days.

Today (day 5) was the first day we have seen rain and it rained nearly all day.

Prices for Phuket are as follows:

Sunglasses THB150
T-Shirts THB150
Singlets THB130
Mens Boxers THB80
Mens Shorts THB250-350
Baseball Cap THB250
Jeans THB700