Home » Posts tagged 'Oracle' (Page 3)

Tag Archives: Oracle

Re-create my Oracle control file

From my previous posting I had mention that I need to recreate my Oracle database control file since I need to increase my MAXDATAFILES parameter.

Yesterday (sunday) I had a chance to manage my control file since I have time to shutdown my server. I log on my AIX server and logon to Oracle SQL Plus.

ora[sid]>sqlplus /nolog

Connect to my database.

SQL>conn /  as sysdba;

Then dump my current control file to a text file.

SQL>alter database backup controlfile to trace as ‘/oracle/[SID]/controlfile.txt’;

Then I copied that file (controlfile.txt) to my local notebook and edit it using my favourite text editor.

Here are list of my old control file :


Check your MAXDATAFILES parameter on Oracle

A few weeks ago, I need to manage my number of datafiles on Oracle server parameter file (spfile). On Oracle spfile, by using BRTOOLS or BRSPACE, you can check your DB_FILES parameter.

ora[sid]>brspace -f dbparam

On my system, currently DBFILES = 768. I need to increase my DB_FILES so it can manage larger number of datafiles but unfortunately I can’t do it right now since my MAXDATAFILES was set to 784. So I can only increase my number of datafiles from 768 to 748.

You can check MAXDATAFILES number on your Oracle control file. Follow this instruction to get your MAXDATAFILES parameter info :

  • Login to your DB server using root
  • Change to ora[sid] user. root#su – ora[sid] [ENTER]
  • Go to sqlplus. ora[sid]>sqlplus /nolog [ENTER]
  • Connect to your database instance. SQL>conn / as sysdba [ENTER]
  • Run command to extract control file to trace file. SQL>alter database backup controlfile to trace [ENTER]
  • Get information about where your trace file is stored. SQL>show parameter user_dump_dest
  • Get your trace file (usually it has .trc extension) and open it using your favourite text editor.
  • Find MAXDATAFILES info inside it.

Here is my trace file sample :

—-more lines—–
MAXDATAFILES 784   ——-> this is the information I mentioned.
) SIZE 50M,
—-more lines—–

If I want to increase MAXDATAFILES, then I need to re-create my control file. I’ll update more about re-creating control file on my next posting.

Maximum Oracle Datafiles is exceeding

We are currently running our SAP R/3 4.7 system on Oracle 9i database. Oracle is the exact version. We haven’t change any parameter on our control file.

The main problem is we need to set our number of datafiles on Oracle

Currently we have DB_FILES set to 768 on Oracle parameter file. We need to set this to higher number (may be around 1024). I ask this question to SAP SDN forum and Stefan said I need to check MAXDATAFILES on our control file. So I need to check my control file.

I log on to my database system and try to dump our control file to trace file using this SQL command :

SQL> alter database backup controlfile to trace;
Database altered.

I check my dump trace location.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE

———————————— ———– ——————————

user_dump_dest                       string      /oracle/[SID]/saptrace/usertrace

Than I try to get my files and read it using my text editor.

This is some lines on my trace file.

# only if the current version of all online logs are available.









From MAXDATAFILES line, I get information about my maximum datafiles I can set, 784. So I cannot set my DB_FILES parameter to 1024 yet. I need to adjust and re-create my control file.

I need to shutdown, backup and re-create it. I’ll explained on mye next posting.

SAP Logon Mechanism

Two days ago, I have a yahoo message from my friends about her SAP Production server which un-accidentally have been changed its SAP database owner schema. She uses SAP ECC 5 version and using Oracle Database 10g. After several hours being headache looking for solution, the problem was solved. So the main problem is that SAP logon mechanism using 2 place to save its user and password. SAP save it on Oracle dictionary and SAPUSER table. One of it password hadn’t changed yet.

Here is I have copied some articles from saptechies.com :

1. What are the default database users in the SAP environment?

The following database users exist in the SAP environment:

SAPR3 / SAP<sid> / SAP<xyz> / SAPSR3

The SAPR3 / SAP<sid> / SAP<xyz> / SAPSR3 user is the owner of all R/3 objects. The work processes log on to the database with this user. The SAPR3 user was always used for older R/3 releases. However, since several R/3 systems may be present in the same database within MCOD with current releases, you can replace the SAPR3 user with a system-specific SAP<sid> user here. You can define this during the installation. To avoid confusion during homogeneous system copies, as of 4.7 SR1 you can use an SID-independent username, SAP<xyz>, with “SAP” followed by any three characters (see Note 617444). In the meantime, the system proposes the username SAPSR3 by default – irrespective of the SID that the system actually uses.

SAP Basis & Virtualization Services

Hi guys,

I am offering Remote SAP Basis Support and Service to your company. No matter your company have dedicated SAP Basis or not, we can help you. Your company could hire me as Remote SAP Basis.

I also have virtualization support on VMware, Proxmox VE, Linux, Oracle, and DB2. Usually, I support my client via TeamViewer connection.

You can contact me through

Email : devratt@yahoo.com
Phone/SMS/WA/Telegram : +62-81336451502

Table Reorganization using BRSPACE via temporary tablespace

On my previous tutorial, you have already learned how to create new tablespace. I done it for this purpose. As you see, I already created PSAPIDS_REORG tablespace.

You can check it via DB02 tcode. Now, I want to reorg MSEG table. Right now, MSEG table is located on PSAPIDS tablespace. I check it by double click-in PSAPIDS from above screen. and write down MSEG on Object Name field like this.

Press the green check (v).

Here is information about MSEG table I get.

Now, open your MS-DOS prompt (coz I am using Windows based IDES server) and using [sid]adm user.

Type this command : >brspace -f tbreorg -t MSEG -m PSAPIDS_REORG

This command will reorg MSEG table and move it to PSAPIDS_REORG table.

Confirmation required to start reorganization process.

Table reorganization status starting.

Reorganization process has completed successfully.

To make sure that MSEG table has been moved to PSAPIDS_REORG tablespace, check again using DB02 tcode and check on PSAPIDS_REORG tablespace.

Now, you can see that MSEG table has been moved to PSAPIDS_REORG. You can start reorganize othe table. For your safety, use it on offline mode (no user access SAP server).

Create New Tablespace on SAP R3 server using BRTOOLS

By default, SAP R3 4.7 has 6 tablespace. You can check them using tcode  DB02. I already show you how to extend tablespace that already exist.

Now I want to show step by step adding new tablespace. I am going to use BRTOOLS, to add new tablespace. Open your MS-DOS prompt using user [sid]adm. Use ora[sid] user for your UNIX box.

To add new tablespace, choose option number 2 Space Management. Then ENTER.

Choose option number 2 to Create Tablespace. Then ENTER.

Enter option number 3 to propose tablespace name. Tablespace name must have prefix PSAPIDS (for my IDES box). Depend on your box. Here, I named it PSAPIDS_REORG (Coz I want to use it later for reorganization purpose).

After that, enter C then ENTER.

Tablespace creation is going to execute. You do not net to shutdown your SAP R3 nor Oracle DB to do this. You can do this online.

Additional information you need to supply such as datafile size, increment size, etc. Then type c to continue.

Tablespace creation is running. Wait for couple minutes.

Tablespace PSAPIDS_REORG has been created successfully. Type C to continue.

Back to Space Management menu. Next step, check the result using DB02 tcode.

You can see that tablespace PSAPIDS_REORG has been added in tablespace list.

Congratulations ! Now, you can continue your coffee break time :D…