Tablespace PSAPSR3 is 100% used

[former_member182034]

hi Dear,

the PSAPSR3 tablespace is used 100% full as you can see following

BR0280I BRSPACE time stamp: 2010-09-19 23.17.59
BR0659I List menu 259 + you can select one or more entries
-------------------------------------------------------------------------------
List of database tablespaces

Pos.  Tablespace     Type  Status    ExtMan.  SegMan.  Backup  Files/AuExt.
      Total[KB]   Used[%]    Free[KB]  MaxSize[KB]  ExtSize[KB]  FreeExt.    Lar
gest[KB]

  1 - PSAPSR3        DATA  ONLINE    LOCAL    AUTO      NO        14/14
     143360000     92.85    10246784    143360000            0        13     406
3232:4061184:2113472:960:960
  2 - PSAPSR3700     DATA  ONLINE    LOCAL    AUTO      NO        13/13
      67072000     99.49      345088    133120000     66048000        14     684
0320+:5263360+:5222400+:4997120+:4956160+
  3 - PSAPSR3USR     DATA  ONLINE    LOCAL    AUTO      NO         1/1
         30720     22.50       23808     10240000     10209280         1    1020
9280+:23808:0:0:0
  4 - PSAPTEMP       TEMP  ONLINE    LOCAL    MANUAL    NO         1/1
       6481920      0.00     6481920     10240000      3758080         0     375
8080+:0:0:0:0
  5 - PSAPUNDO       UNDO  ONLINE    LOCAL    MANUAL    NO         1/1
       9072640      0.00     9072576     10240000      1167360       515     406
3232:1167360+:925632:589824:573440
  6 - SYSAUX         DATA  ONLINE    LOCAL    AUTO      NO         1/1
        983040     95.24       46784     10240000      9256960       118     925
6960+:7104:3072:2048:1088
  7 - SYSTEM         DATA  ONLINE    LOCAL    MANUAL    NO         1/1
        870400     99.16        7296     10240000      9369600         3     936
9600+:7104:128:64:0

how can i increase the tablespace with brtools or sap tcode?

is it better to increase the tablespace or add a new?

Regards,

majamil

[Former Member · Accepted solution]

Hi,

Using BRTools you can extend the tablespace by adding datafile very easily. Login to the system at OS level with SID<ADM> or ora<SID> and proceed as follows.

Brtools
'2' - Space Management
'1' - Extend tablespace
'c' - continue
'c' - continue
'1' - Extend tablespace
Select the tablespace which u want to extend by entering the corresponding number (In ur case '1' - PSAPSR3)
'3' -  New file to be added (file) (Enter the location for the new datafile)
'5' - Size of the new file in MB (size) (Enter the size of the new datafile)
'6' - File autoextend mode (autoextend) (Leave it as 'Yes' if u want the new datafile to be autoextendibla)
'c' - continue
'c' - continue

After doing this, a new datafile will be added to the tablespace PSAPSR3.

Hope this helps.

Regards,

Varadharajan M

[former_member709110]

Hi,

Increasing the size of the tablespace is the right approach . You can add a datafile or resize existing datafiles.

Regards,

Neel

[former_member182034]

Dear,

FYI....

the previous value of tablesapce PSAPSR3 was following

Pos. Tablespace Files/AuExt. Total[KB]   Used[%]  Free[KB] MaxSize[KB]

1 - PSAPSR3 13/13    133120000           99.99 8896         133120000
2 - PSAPSR3700 13/13 67072000 99.49 345088 133120000
3 - PSAPSR3USR 1/1 30720 22.50 23808 10240000
4 - PSAPTEMP 1/1 6481920 0.00 6481920 10240000
5 - PSAPUNDO 1/1 9072640 0.00 9072576 10240000
6 - SYSAUX 1/1 983040 95.14 47808 10240000
7 - SYSTEM 1/1 870400 99.16 7296 10240000

i have put these value during tablespace extention mean with 10 GB

Options for extension of tablespace PSAPSR3 (1. file)

 1 * Last added file name (lastfile) ....... [G:\ORACLE\PRD\SAPDATA2\SR3_13\SR3.
DATA13]
 2 * Last added file size in MB (lastsize) . [10000]
 3 - New file to be added (file) ........... [G:\oracle\PRD\sapdata2\sr3_14\sr3.
data14]
 4 # Raw disk / link target (rawlink) ...... []
 5 - Size of the new file in MB (size) ..... [10000]   // default vlaue
 6 - File autoextend mode (autoextend) ..... [yes]
 7 ? Maximum file size in MB (maxsize) ..... []  default setting
 7 - Maximum file size in MB (maxsize) ..... [10000] //  extended size (10GB)
 8 - File increment size in MB (incrsize) .. [20]
 9 - SQL command (command) ................. [alter tablespace PSAPSR3 add dataf
ile 'G:\oracle\PRD\sapdata2\sr3_14\sr3.data14' size 10000M autoextend on next 20l
M maxsize 10000M]

now current tablespace PSAPSR3 is

Pos.  Tablespace     Type  Status    ExtMan.  SegMan.  Backup  Files/AuExt.
      Total[KB]   Used[%]    Free[KB]  MaxSize[KB]  ExtSize[KB]  FreeExt.    Lar
gest[KB]

  1 - PSAPSR3        DATA  ONLINE    LOCAL    AUTO      NO        14/14
     143360000     92.85    10247808    143360000            0        13     406

above procedure is ok mean i increased the 10GB space for PSAPSR3 if not then guide me.

which size will be recommended for extending the Tablespaces PSAPSR3 and others ???.

Regards,

[Former Member]

Hi,

Yes, you have extended the tablespace. It is better to have the datafiles of smaller size. Now you cannot alter this value, so in future while adding a datafile, create files of size ~ 4GB.

Mark this thread as answered if it has resolved ur concerns.

Regards,

Varadharajan M

[former_member182034]

ok...thanks for this information...

Dear,

basically.. I have a DR server and i applied archive from PRD server to DR on daily basis. But as you know that i have added/extended the tablespace on PRD server while new tablespaces does not exist on DR server. mean when i want to apply archives then new folders do not exist ( sr3_ 14,15,16,17,18 ) and other files on DR server.

SQL> recover database using backup controlfile;

ORA-00279: change 332436125 generated at 09/19/2010 13:27:12 needed for

1

ORA-00289: suggestion : F:\ORACLE\PRD\ORAARCH\PRDARCHARC31934_0657865393

ORA-00280: change 332436125 for thread 1 is in sequence #31934

ORA-00278: log file 'F:\ORACLE\PRD\ORAARCH\PRDARCHARC31933_0657865393.00

longer needed for this recovery

ORA-00283: recovery session canceled due to errors

ORA-01244: unnamed datafile(s) added to control file by media recovery

ORA-01110: data file 31: 'G:\ORACLE\PRD\SAPDATA2\SR3_14\SR3.DATA14'

ORA-01112: media recovery not started

SQL> recover database using backup controlfile;

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 31 is unknown - rename to correct file

ORA-01110: data file 31: 'E:\ORACLE\PRD\102\DATABASE\UNNAMED00031'

ORA-01157: cannot identify/lock data file 31 - see DBWR trace file

ORA-01111: name for data file 31 is unknown - rename to correct file

ORA-01110: data file 31: 'E:\ORACLE\PRD\102\DATABASE\UNNAMED00031'

how can i apply new tablespace from PRD to DR server?

[Former Member]

Hello,

have a look on this link and first check settings for DB parameter STANDBY_FILE_MANAGEMENT on your standby db server (spfile) wheather it is AUTO or MANUAL and then following the manual from the link point 8.3.1

regard

peter

[http://download.oracle.com/docs/cd/B12037_01/server.101/b10823/manage_ps.htm#1010429]

[Former Member]

Hello,

As mentioned earlier mark this thread as answered as ur issue got fixed.

Open a new thread for new issues.

-Varadhu...

[former_member182034]

thanks Guest,

my problems resolved with the help of mentioned link..

i just generated trace file and re-create on DR and after that archives are applying successfully...

once again thanks Guest and Vardu for this support.