Skip to content

Archive for

Aug 31

1 Comment

RMAN-06025: no backup of archived log for thread 1 with sequence

When i tried to test the validate archivelog files using rman , It shows following errors.

Database in mount stage.

RMAN> restore archivelog all validate;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2010 22:07:24
RMAN-06026: some targets not found – aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 171 and starting SCN of 3709282 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 170 and starting SCN of 3679505 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 169 and starting SCN of 3652387 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 168 and starting SCN of 3625416 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 167 and starting SCN of 3604154 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 166 and starting SCN of 3589298 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 165 and starting SCN of 3555509 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 164 and starting SCN of 3529461 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 163 and starting SCN of 3507588 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 162 and starting SCN of 3492156 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 161 and starting SCN of 3465574 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 160 and starting SCN of 3435239 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 159 and starting SCN of 3405625 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 158 and starting SCN of 3377158 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 157 and starting SCN of 3348248 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 156 and starting SCN of 3326494 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 155 and starting SCN of 3317149 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 154 and starting SCN of 3278618 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 153 and starting SCN of 3250224 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 152 and starting SCN of 3216117 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 151 and starting SCN of 3192026 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 150 and starting SCN of 3182856 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 149 and starting SCN of 3151576 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 148 and starting SCN of 3130636 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 147 and starting SCN of 3110194 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 146 and starting SCN of 3088359 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 145 and starting SCN of 3047655 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 144 and starting SCN of 3019436 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 143 and starting SCN of 2986647 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 142 and starting SCN of 2964338 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 141 and starting SCN of 2951686 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 140 and starting SCN of 2921236 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 139 and starting SCN of 2898469 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 138 and starting SCN of 2882299 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 137 and starting SCN of 2846145 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 136 and starting SCN of 2823076 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 135 and starting SCN of 2797568 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 134 and starting SCN of 2776014 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 133 and starting SCN of 2754783 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 132 and starting SCN of 2722946 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 131 and starting SCN of 2691062 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 130 and starting SCN of 2670482 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 129 and starting SCN of 2658786 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 128 and starting SCN of 2626592 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 127 and starting SCN of 2605511 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 126 and starting SCN of 2592171 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 125 and starting SCN of 2571042 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 124 and starting SCN of 2553751 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 123 and starting SCN of 2515571 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 122 and starting SCN of 2486471 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 121 and starting SCN of 2451401 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 120 and starting SCN of 2418851 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 119 and starting SCN of 2382607 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 118 and starting SCN of 2356092 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 117 and starting SCN of 2321831 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 116 and starting SCN of 2301728 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 115 and starting SCN of 2278153 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 114 and starting SCN of 2252731 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 113 and starting SCN of 2234269 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 112 and starting SCN of 2211440 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 111 and starting SCN of 2179088 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 110 and starting SCN of 2153325 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 109 and starting SCN of 2127317 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 108 and starting SCN of 2100895 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 107 and starting SCN of 2074132 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 106 and starting SCN of 2040703 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 105 and starting SCN of 2013057 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 104 and starting SCN of 1996708 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 103 and starting SCN of 1965702 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 102 and starting SCN of 1937541 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 101 and starting SCN of 1908149 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 100 and starting SCN of 1882257 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 99 and starting SCN of 1857140 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 98 and starting SCN of 1818717 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 97 and starting SCN of 1793468 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 96 and starting SCN of 1762243 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 95 and starting SCN of 1730809 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 94 and starting SCN of 1696341 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 93 and starting SCN of 1669601 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 92 and starting SCN of 1644524 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 91 and starting SCN of 1622311 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 90 and starting SCN of 1597648 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 89 and starting SCN of 1562727 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 88 and starting SCN of 1525719 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 87 and starting SCN of 1490745 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 86 and starting SCN of 1462723 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 85 and starting SCN of 1432580 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 84 and starting SCN of 1404044 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 83 and starting SCN of 1376784 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 82 and starting SCN of 1348841 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 81 and starting SCN of 1323473 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 80 and starting SCN of 1293085 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 79 and starting SCN of 1263759 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 78 and starting SCN of 1236889 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 77 and starting SCN of 1211496 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 76 and starting SCN of 1184992 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 75 and starting SCN of 1148510 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 74 and starting SCN of 1117984 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 73 and starting SCN of 1088051 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 72 and starting SCN of 1057677 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 71 and starting SCN of 1031110 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 70 and starting SCN of 1003274 found to restore
RMAN-06025: no backup of archived log for thread 1 with sequence 69 and starting SCN of 971649 found to restore

So that I checked the log sequence staus using sql plus.

SQL> select sequence#,status from v$archived_log;

 SEQUENCE# S
———- –
        69 D
        70 D
        71 D
        72 D
        73 D
        74 D
        75 D
        76 D
        77 D
        78 D
        79 D

 SEQUENCE# S
———- –
        80 D
        81 D
        82 D
        83 D
        84 D
        85 D
        86 D
        87 D
        88 D
        89 D
        90 D

 SEQUENCE# S
———- –
        91 D
        92 D
        93 D
        94 D
        95 D
        96 D
        97 D
        98 D
        99 D
       100 D
       101 D

 SEQUENCE# S
———- –
       102 D
       103 D
       104 D
       105 D
       106 D
       107 D
       108 D
       109 D
       110 D
       111 D
       112 D

 SEQUENCE# S
———- –
       113 D
       114 D
       115 D
       116 D
       117 D
       118 D
       119 D
       120 D
       121 D
       122 D
       123 D

 SEQUENCE# S
———- –
       124 D
       125 D
       126 D
       127 D
       128 D
       129 D
       130 D
       131 D
       132 D
       133 D
       134 D

 SEQUENCE# S
———- –
       135 D
       136 D
       137 D
       138 D
       139 D
       140 D
       141 D
       142 D
       143 D
       144 D
       145 D

 SEQUENCE# S
———- –
       146 D
       147 D
       148 D
       149 D
       150 D
       151 D
       152 D
       153 D
       154 D
       155 D
       156 D

 SEQUENCE# S
———- –
       157 D
       158 D
       159 D
       160 D
       161 D
       162 D
       163 D
       164 D
       165 D
       166 D
       167 D

 SEQUENCE# S
———- –
       168 D
       169 D
       170 D
       171 D
       172 D
       173 D
       174 D
       175 D
       176 D
       177 D
       178 D

 SEQUENCE# S
———- –
       179 D
       180 D
       181 D
       182 D
       183 D
       184 D
       185 D
       186 D
       187 D
       188 D
       189 D

 SEQUENCE# S
———- –
       190 D
       191 D
       192 D
       193 D
       194 D
       195 D
       196 D
       197 D
       198 D
       199 D
       200 D

 SEQUENCE# S
———- –
       201 D
       202 D
       203 D
       204 D
       205 D
       206 D
       207 D
       208 D
       209 D
       210 D
       211 D

 SEQUENCE# S
———- –
       212 D
       213 D
       214 D
       215 D
       216 D
       217 D
       218 D
       219 D
       220 A
       221 A
       222 A

 SEQUENCE# S
———- –
       223 A
       224 A
       225 A
       226 A
       227 A
       228 A

160 rows selected.

SQL>

It shows the log sequence was deleted from 69 to 219.It may be deleted like manually by OS or crosschecked when those log sequence expired .

You can see here

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name AZAR
=====================================================================

Key     Thrd Seq     S Low Time
——- —- ——- – ———
152     1    220     A 25-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_25\O1_MF_1_220_67BS362G_.ARC

153     1    221     A 25-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_27\O1_MF_1_221_67J390NN_.ARC

154     1    222     A 27-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_28\O1_MF_1_222_67LFPL34_.ARC

155     1    223     A 28-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_223_67MF6M67_.ARC

156     1    224     A 29-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_224_67O2495F_.ARC

157     1    225     A 29-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_225_67OKFOTO_.ARC

158     1    226     A 29-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_30\O1_MF_1_226_67QP6OFQ_.ARC

159     1    227     A 30-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_227_67TK110L_.ARC

160     1    228     A 31-AUG-10
        Name: C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_228_67TK9NMW_.ARC

The above command showed which log sequence was backedup.So you can use to restore from those logs.

RMAN> restore  validate check logical archivelog from sequence 220;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_25\O1_MF_1_220_67BS362G_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_27\O1_MF_1_221_67J390NN_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_28\O1_MF_1_222_67LFPL34_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_223_67MF6M67_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_224_67O2495F_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_225_67OKFOTO_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_30\O1_MF_1_226_67QP6OFQ_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_227_67TK110L_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_228_67TK9NMW_.ARC
Finished restore at 31-AUG-10

The validation was succeeded.

But when I trying to use the first command as database open stage,It doesnot show those deleted files validation.

RMAN> restore  archivelog all validate;

Starting restore at 31-AUG-10
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_25\O1_MF_1_220_67BS362G_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_27\O1_MF_1_221_67J390NN_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_28\O1_MF_1_222_67LFPL34_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_223_67MF6M67_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_224_67O2495F_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_29\O1_MF_1_225_67OKFOTO_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_30\O1_MF_1_226_67QP6OFQ_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_227_67TK110L_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\ARCHIVELOG\2010_08_31\O1_MF_1_228_67TK9NMW_.ARC
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\BACKUPSET\2010_08_25\O1_MF_ANNNN_TAG20100825T213530_67BRVM5X_.BKP
channel ORA_DISK_1: piece handle=C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\BACKUPSET\2010_08_25\O1_MF_ANNNN_TAG20100825T213530_67BRVM5X_.BKP tag=TAG20100825T21
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting validation of archived log backup set
channel ORA_DISK_1: reading from backup piece C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\BACKUPSET\2010_08_31\O1_MF_ANNNN_TAG20100831T210420_67TK18GF_.BKP
channel ORA_DISK_1: piece handle=C:\APP\AZAR\FLASH_RECOVERY_AREA\AZAR\BACKUPSET\2010_08_31\O1_MF_ANNNN_TAG20100831T210420_67TK18GF_.BKP tag=TAG20100831T21
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:31
Finished restore at 31-AUG-10

Aug 30

3 Comments

Can I increase SGA target size greater than SGA max size ?

Answer is NO. Can I increase SGA_MAX_SIZE greater than memory_target ?.....NO.Now we can see some examples. However you can clear more about from oracle document. Here I just mentioned some usefull url about this related.

SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:

  • Buffer cache (DB_CACHE_SIZE)
  • Shared pool (SHARED_POOL_SIZE)
  • Large pool (LARGE_POOL_SIZE)
  • Java pool (JAVA_POOL_SIZE)
  • Streams pool (STREAMS_POOL_SIZE)

If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

C:\Users\mazar>set oracle_sid=own

C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 30 12:33:38 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys/sys@own as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 0

This below view to see the shared pool or buffer pool size.
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
——————————– ———- —
Fixed SGA Size                      1375792 No
Redo Buffers                        5804032 No
Buffer Cache Size                 339738624 Yes
Shared Pool Size                  146800640 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  535662592 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
——————————– ———- —
Free SGA Memory Available          33554432

12 rows selected.

SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M

  

Here I want to increase my sga target size 500m. The system will be altered.Because SGA_MAX_SIZE is 512m. Suppose to be if you’re increase more than sga_max_size, it will throw error. See below,SQL> alter system set sga_target=500m;

System altered.

SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 500M
SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M
SQL> show parameter sga_target;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 4M
SQL> alter system set sga_target=600m;
alter system set sga_target=600m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

C:\Users\mazar>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 30 12:43:47 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: sys/sys as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             167772624 bytes
Database Buffers          360710144 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;

NAME                                  BYTES RES
——————————– ———- —
Fixed SGA Size                      1375792 No
Redo Buffers                        5804032 No
Buffer Cache Size                 360710144 Yes
Shared Pool Size                  146800640 Yes
Large Pool Size                     4194304 Yes
Java Pool Size                      4194304 Yes
Streams Pool Size                         0 Yes
Shared IO Pool Size                       0 Yes
Granule Size                        4194304 No
Maximum SGA Size                  535662592 No
Startup overhead in Shared Pool    58720256 No

NAME                                  BYTES RES
——————————– ———- —
Free SGA Memory Available          12582912

12 rows selected.

Here I tried to increase my sga_max_size , but it show error because memory target is low compared to increase your sga max size values.

SQL> alter system set sga_max_size=1512m scope=spfile;

System altered.

SQL> show parameter sga_max_size;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 512M
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 1585446912 (1512mb) cannot be set to more than MEMORY_TARGET 8388
60800. (800mb)
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 9 Serial number: 5

So I created pfile and modified in pfile.
SQL> create pfile=’c:\initown.ora’ from spfile;

File created.

SQL> startup pfile=’C:\azar\initown.ora’;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1373996 bytes
Variable Size             176163028 bytes
Database Buffers          352321536 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

If you want to increase your SGA_MAX_SIZE, you should increase your  memory target size..

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#BGBGJFHE

Very interestng quesition here about this post related.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:30011178429375

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516

Aug 24

ORA-27101: shared memory realm does not exist

Yesterday evening,  When i tried to increase my SGA size in Oracle 9i on 32 bit Windows server 2003, It shows ORA-27101: shared memory realm does not exist and Out of memory.I am aware of this error.

I tried to fix this error like I just added 3gb switch on boot.ini file and then I just shutdown the server and up again. the database working properly. where you can find this boot.ini file on windows server 2003.

Note : windows 2000 not applicable.

After added 3gb switch, I can increase SGA max size manually and other components of SGA size also.

What is 3gb switch and How to set up?

http://technet.microsoft.com/en-us/library/bb124810(EXCHG.65).aspx

Aug 22

11 Comments

Video Tutorial – ASM database configuration using DBCA

Hi, This video described about How to configure ASM on your newly creating database, Here I have created five disks and one disk groups.

Watch this Video :

Aug 18

Book Review : Oracle 11g R1/R2 Real Application Cluster Handbook

Recently I was reviewed one of the outstanding book, that book name is ” Oracle 11g R1/R2 Real Application Cluster HandBook”.

This book clearly explained about Oracle High avilabilty system and explained about Business Continous Process , What necessary we need for  disaster recovery and Downtime of database duration and what is the loss of if database is down and Step by Step Installation guide.

Here I want to mentioned , you can easily make your Oracle RAC setup using this step by step guide without help others and then this book additionaly cleared about ASM and Troublshooting RAC and RAC management and then this book also explained about RMAN backup and Recovery feature and more.

I hope this book is very usefull for who are willing to start Oracle RAC carrier and then I highly Recommand to buy this book and Enjoy it.

Here just a click  If you want to buy this book,

https://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-handbook/book