Showing posts with label ORACLE. Show all posts
Showing posts with label ORACLE. Show all posts
Operating System and Database Migration
Pre Export Process In Application level :-
Pre Export Process In OS level :-
Pre Export Process In DB level :-
Steps to Start the DB export
Import process
Some of the problems faced with the above mentioned source and target system
“Cannot connect to DB”
Solution: Delete the following R3load, R3szchk, R3ldctl from the Instal directory.
Process
Solution: Edit the DBEXPORT.R3S file and delete the lines DBCOMPUTESTAT4MIG_XT*,
DBCOMPUTESTAT4MIG*, BRCONNECTEXPSTAT*
Solution: Use the java based package splitter to split the STR files for the export.
Solution: Check the size of the PSAPTEMP before export. It should be atleast 2 times the size of the largest index for R/3.
Solution: We have used the mix server of 512 and max server of 1024 during the import process
Solution: Try using the PSAPUNDO in place of PSAPROLL
from the use of migmon between the source and the target.
Post Installation jobs for BASIS area
Reason for moving to Oracle 9.2.0.7
9.2.0.4 being the first stable release of R/3 had many bugs which are listed in the SAP and Oracle sites were later removed in later subsequent releases.
This oracle will be upgraded to Oracle 10.2.0.2 and for the upgrade the source should be at oracle 9.2.0.7 before it can be upgraded to 10g.
As we go higher up in the in the Optimizer gets improved hence the execution plans are improved and this results is transactions executing faster
Using of the Package splitter
The java based package splitter can be used to split the STR files
- Check the Java JRE version (Min 1.4.1).
- Check the QCM table entries of SAP.
- Check for cancelled and update task
- Delete table entries from TATGPC, TATGPCA
- Check the incremental table conversion
- Cancel all released jobs
- Check for the operation modes
- Delete unnecessary spool data
- De-schedule all SAP jobs
- Delete all the batch jobs
- Release all repairs and correction and transports
- delete background input logs
- Delete job logs
- Delete the tRFC
- Check for the code pages installed
- Check the DDIC password for 000 client
Pre Export Process In OS level :-
- Deschedule all OS DB backups
- Shutdown all external interface
- The source should have minimum SP3
Pre Export Process In DB level :-
- Update DB statistics
- Change the DB to no archive log mode
Steps to Start the DB export
- Enter the system as adm
- Create to directory in the source system eg, Export, Install
- Check for the PSAPTEMP, the size should be 2 times the largest index
- Start the R3setup.bat program from the kernel CD (use the 45B_EXT kernel cd for the migration as the target system is AIX 5.3, always check the PAM before using the kernel cd.)
- Edit the DBEXPORT.R3S file and skip the update of statistics as it is already done and update statistics of R3SETUP is high time consuming. It can even run for days for a DB size more than 1 TB
- Edit the DBEXPORT.R3S to stop for splitter
- Start the export
- The export stops at the given breakpoint for the splitter
- Split the STR files generated
- Repeat the export process
Import process
- Check the Java version ( Minimum JRE version 1.4.1 )
- Check all necessary drives and filesystems. ( #mkdir /oracle/client/92x_64, # cd /oracle/stage, #mkdir 920_64 )
- Create to directory eg, Install, Import
- From install directory run instool.sh of Ext. kernel cd for AIX.
- set environment.
- #csh
- #setenv LIBPATH /sapmnt//exe:/oracle//920_64/lib.
- Install the central instances. R3SETUP –f CENTRAL.R3S
- In the next installation process choose “Exit”
- Install Oracle software as per WAS640 guide with ora user.
- After the oracle installation is done repeat the installation of the R3SETUP
- The R3SETUP stops for Migration key check phase
- Create the tablespaces of the required sizes
- Restart the database in no archive log mode
- Start the import process
- Complete the post installation steps from the migration guide of 45 B
Some of the problems faced with the above mentioned source and target system
- During the export phase after the STR files are created the export stops with an error saying
“Cannot connect to DB”
Solution: Delete the following R3load, R3szchk, R3ldctl from the Instal directory.
- Use update of DB statistics by sapdba or brtools. Avoid using the update of statistics by the export
Process
Solution: Edit the DBEXPORT.R3S file and delete the lines DBCOMPUTESTAT4MIG_XT*,
DBCOMPUTESTAT4MIG*, BRCONNECTEXPSTAT*
- Export takes a huge time
Solution: Use the java based package splitter to split the STR files for the export.
- Export fails complaining the PSAPTEMP size is small.
Solution: Check the size of the PSAPTEMP before export. It should be atleast 2 times the size of the largest index for R/3.
- During the import process check the aio settings in the OS level.
Solution: We have used the mix server of 512 and max server of 1024 during the import process
- 6. The import process fails due to lack of space to extend the extent at PSAPROLL
Solution: Try using the PSAPUNDO in place of PSAPROLL
- 7. The total system downtime could be reduced if MIGMON was used. The firewall rule prevented
from the use of migmon between the source and the target.
Post Installation jobs for BASIS area
- Replace the disp+work of the kernel.
- Delete the table entries as mentioned in the Heterogeneous system copy guide of 45B
- Edit the RFC destination address of other connected system.
- Change the printer definition of the target. Test all the printers are working or we need to make changes in the settings.
- Set up the TMS
- Check for the ALE and IDOCS flow after the new system is up
- Schedule all the basic BASIS background jobs.
- Take a full offline backup
Reason for moving to Oracle 9.2.0.7
9.2.0.4 being the first stable release of R/3 had many bugs which are listed in the SAP and Oracle sites were later removed in later subsequent releases.
This oracle will be upgraded to Oracle 10.2.0.2 and for the upgrade the source should be at oracle 9.2.0.7 before it can be upgraded to 10g.
As we go higher up in the in the Optimizer gets improved hence the execution plans are improved and this results is transactions executing faster
Using of the Package splitter
The java based package splitter can be used to split the STR files
- Open new terminal preparation for splitter tool.
- Edit packet_splitter_end.properties file
- Use splitter guide for clarification.
- setenv JAVA_HOME /usr/opt/java141.
- using adm user, start split process.
BASIS BASIC
,
MSSQL
,
ORACLE
DB Refresh Steps in SAP
PROCEDURE FOR DB REFRESH:
1) Take full offline backup of database using BRTOOLS/DB13.
Brbackup –u –p init<sid>.sap –d disk –t offline –m all
2) Generate the control file
SQL> alter database backup controlfile to trace;
Get the trace file from user trace directory and modify the file as following
- Rename the file to control.sql.
- Remove all the content above the line STARTUP NOMOUNT and below CHARACTER SET URF8;
- Replace NORESETLOGS with RESETLOGS.
- Replace ARCHIVELOG with NOARCHIVELOG.
- Replace S_sid with T_sid.
3) Change the S_sid with T_sid in b***.afd backup log file.
4) Change the S_sid with T_sid in the first file of backup folder and rename to target sid.
5) Restore the database using the command
Brrestore –u 0b b***.afd -d disk –k yes -m all
6) Login to database start the database(startup nomount).
7) Run @contro.sql file.
SQL>select status from v$instance; it is in mounted state.
SQL>alter database open resetlogs; now it goes to open status.
SQL> select username from dba_users;
8) Now copy ORADBUSR.SQL file from installation folder copy to sap backup directory and run.
SQL> @ORADBUSR.SQL
Enter value for 1: schema owner ID SAPSR3
2: file system NT
3: host name
4: SAP SID
Now again check the users
SQL>select username from dba_users;
Now you can see target system users only. Remove the source system users.
9) Drop user
OPS$HOSTNAME\SIDADM cascade.
10) Now provide the permission to the target system users.
SQL> grant connect sapdba to “OPS$Hostname/SIDADM”;SAPSERVICESID;
SQL>grant dba to “OPS$Hostname/SIDADM”;SAPSERVICESID;
11) Now start the SAP system and is working or not.
Setting ORACLE DB into noarchivelog mode
Steps when the database state is down:
1. Login to sqlplus
-sqlplus /nolog
-connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
-startup mount;
3. Check the log mode status of the database
-select log_mode from v$database;
4. Setting it to noarchivelog mode
-alter database noarchivelog;
5. Setting db open for user operation
-alter database open;
6. Reverting back to archivelog mode
-alter database archivelog;
1. Login to sqlplus
-sqlplus /nolog
-connect / as sysdba
2. The db must be mounted EXCLUSIVE and not open for operation
-startup mount;
3. Check the log mode status of the database
-select log_mode from v$database;
4. Setting it to noarchivelog mode
-alter database noarchivelog;
5. Setting db open for user operation
-alter database open;
6. Reverting back to archivelog mode
-alter database archivelog;
how to check character set in Oracle
How to check character set in Oracle
One of the requirements for samePage is that the character set should be UTF-8.
You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8.
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
One of the requirements for samePage is that the character set should be UTF-8.
You can either check with your DBA or run the following SQL to determine whether your database character set is UTF-8.
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
It should return the value AL32UTF8.
Another option is to run the following SQL :
SELECT * FROM NLS_DATABASE_PARAMETERS
The value against NLS_CHARACTERSET should be UTF8.
How to enable ARCHIVELOG MODE in Oracle
shutdown database
SQL> shutdown immediate;
startup database in mount mode
SQL> startup mount;
Change DB log mode
SQL> alter database archivelog;
Open DB for normal use
SQL> alter database open;
check archivelog status
SQL> archive log list
Default Archive log destination.
1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
2. If your not using FRA then all files created in $ORACLE_HOME/database
We can check database archivelog mode through below query
SQL> select log_mode from v$database;
or
SQL> archive log list
SQL> shutdown immediate;
startup database in mount mode
SQL> startup mount;
Change DB log mode
SQL> alter database archivelog;
Open DB for normal use
SQL> alter database open;
check archivelog status
SQL> archive log list
Default Archive log destination.
1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
2. If your not using FRA then all files created in $ORACLE_HOME/database
We can check database archivelog mode through below query
SQL> select log_mode from v$database;
or
SQL> archive log list
Standby database start tips for Oracle database
# sqlplus '/as sysdba'
Cheking ps -ef|grep ora -- that oracle processes has been started
On source db run :
SQL>alter system switch logfile;
Cheking Alert log file for successfully starting Oeracle.
Starting standby database with activation
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup mount
SQL> shutdown immediate
SQL> startup
| SQL> startup nomount |
| SQL> alter database mount standby database; |
Cheking ps -ef|grep ora -- that oracle processes has been started
On source db run :
SQL>alter system switch logfile;
Starting standby database with activation
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup mount
SQL> shutdown immediate
SQL> startup
How to add a tempfile in Oracle
To add a tempfile into the mounted opened Oracle db:
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata1/temp_1/temp.data1' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata2/temp_2/temp.data2' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata3/temp_3/temp.data3' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata4/temp_4/temp.data4' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata1/temp_1/temp.data1' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata2/temp_2/temp.data2' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata3/temp_3/temp.data3' size 32000M reuse;
SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'/oracle/SID/sapdata4/temp_4/temp.data4' size 32000M reuse;
How to list all schemas from oracle database
In Oracle the entity "user" and entity "schema" are same if provided user has created at least one object in it.
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
Login in sqlplus as sys
To list all schemas:
SQL> SELECT username FROM all_users ORDER BY username;
SQL> Select username from dba_users;
Will give you list of all users but all users may not have created objects in it. So we can not call them as schema.
SQL> Select distinct owner from dba_objects;
Will give you the list of schemas available.
Using some conditions
SQL> SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER' ;
or
select distinct(owner) from dba_objects where owner in ( SELECT username FROM all_users where username like '%_OWNER' OR username like '%_USER');
Oracle select - how to check all tables from schema
Select how to check all tables from schema with table sizes.
SELECT /*+ PARALLEL(dba_segments,16) */ owner,
segment_name,
segment_type,
tablespace_name,
round(SUM (bytes) / POWER (2, 20), 1) size_gb
FROM dba_segments
WHERE owner IN ('<SCHEMA OWNER NAME>')GROUP BY owner, segment_name, segment_type, tablespace_name
order by segment_name;segment_name,
segment_type,
tablespace_name,
round(SUM (bytes) / POWER (2, 20), 1) size_gb
FROM dba_segments
WHERE owner IN ('<SCHEMA OWNER NAME>')GROUP BY owner, segment_name, segment_type, tablespace_name
Applying redolog files in Oracle
procedure for applying redolog files in Oracle db.
sqlplus '/as sysdba'
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
Choose files for applying after last file enter CANCEL
ALTER DATABASE OPEN RESETLOGS;
Checking that resetlogs were applyed correctly
SELECT * FROM "any_small_table";
SHUTDOWN IMMEDIATE;
STARTUP;
How to set date time output in sqlplus
Sometimes it is useful to check report runtime via sqlplus when you have a date timestamp . It can be set by set time on command.thru sqlplus command prompt .
SQL> set time on
11:33:30 SQL> set sqlprompt >
SQL> set time on
11:30:01 SQL>
11:33:30 SQL>
also, you may omit SQL prompt perfix entry
11:33:30 SQL> set sqlprompt >
11:33:37 >
11:33:38 >
HOW to start,Stop, Start and bounce an Oracle Database
Connect as SYS DBA or SYSOPER privilege
Stop:
SQL> shutdown
Options
-NORMAL
-IMMEDIATE
-TRANSACTIONAL
-ABORT
START:
SQL> startup
Options
– NOMOUNT
-MOUNT
-OPEN
BOUNCE:
SQL>startup force
It is a combination of shutdown abort + startup open (But beware of this command if there are other connections to database – they will be forcefully closed)
Stop:
SQL> shutdown
Options
-NORMAL
-IMMEDIATE
-TRANSACTIONAL
-ABORT
START:
SQL> startup
Options
– NOMOUNT
-MOUNT
-OPEN
BOUNCE:
SQL>startup force
It is a combination of shutdown abort + startup open (But beware of this command if there are other connections to database – they will be forcefully closed)
Difference Between Oracle Mount, No Mount and Open Stage?
NOMOUNT: This stage is only used when first creating a database or when it is necessary to recreate a database's control files. Startup includes the following tasks.
- Read the spfileSID.ora or spfile.ora or initSID.ora.
- Allocate the SGA(System Global Area).
- Startup the background processes.
MOUNT: This stage is used for specific maintenance operations. The database is mounted, but not open. You can use this option if you need to:
- Rename datafiles.
- Enable/disable redo log archiving options.
- Perform full database recovery.
- When a database is mounted it is associated with the instance that was started during NOMOUNT stage.
- locates and opens the control files specified in the parameter file.
- reads the control file to obtain the names/status of datafiles and redo log files, but it does not check to verify the existence of these files.
OPEN: This stage is used for normal database operations. Any valid user can connect to the database. Opening the database includes opening datafiles and redo log files.
If any of these files are missing, Oracle will return an error. If errors occurred during the previous database shutdown,
the SMON background process will initiate instance recovery
The startup process is Nomount -> Mount -> Open.
How to reset SAP* password from oracle database?
Logon to your database with orasid as user id and run this sqldelete from sapSID.usr02
where bname='SAP*' and mandt='XXX';commit;
Where mandt is the client.
Now you can login to the client using sap* and password pass
How to Unlock/Change Password For Oracle User?
When you want to lock/unlock an oracle user, my suggestion is to user the sql command “alter user” like:
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;
In case you need to change the password of an oracle user the recomandation from SAP is to change the password with brconnect. The right command is:
brconnect [-u system/<system_password>] –c –f chpass –u <user_name> –p <new_password>
Where:
- <system_password> is the password of the SYSTEM database user. You can use another user with DBA privileges.
- <user_name> is the database user for which the password should be changed (for example, SAP<SAPSID>).
- <new_password> is the new password for the user.
How to determine total memory assigned for SGA and PGA in ORACLE
By using SQLPlus or TCODE: DBACOCKPIT to determine the total memory assigned for
System Global Area (SGA) and Program Global Area (PGA)
Steps for SGA:
Option 1: By using SQLPlus,
Execute SQL Command: show sga (sample show ~4GB for SGA)
Option 2: TCODE: DBACOCKPIT
Tcode: DBACOCKPIT -> Performance -> Statistical information -> SGA Monitor -> SGA tab
Steps for PGA:
Tcode: DBACOCKPIT -> Performance -> Statistical information -> PGA Monitor
Steps for SGA:
Option 1: By using SQLPlus,
Execute SQL Command: show sga (sample show ~4GB for SGA)
Option 2: TCODE: DBACOCKPIT
Tcode: DBACOCKPIT -> Performance -> Statistical information -> SGA Monitor -> SGA tab
Steps for PGA:
Tcode: DBACOCKPIT -> Performance -> Statistical information -> PGA Monitor
-> PGA Status tab (Parameter: Total PGA allocated)
How to shutdown and startup Oracle manually
There might be time you want to shutdown / startup only the Oracle DB.
The default "STOPSAP" script by SAP will shutdown SAP and follow with the Oracle.
"STARTSAP" scripts will startup the Oracle first then follow by starting-up the SAP.
2) Startup command
That is the simple steps to restart Oracle DB ..
How to Generate Oracle Workload Repository Report for Database Analysis
Looking for some Oracle database performance reporting? The "Workload
Repository Report" is one of the report that contains important information for the database
performance analysis.
The steps cover below are using HP-UX environment:
1) Login to the system with root permission and goto "/oracle/PRD/112_64/rdbms/admin"
The steps cover below are using HP-UX environment:
1) Login to the system with root permission and goto "/oracle/PRD/112_64/rdbms/admin"
2) Connect to "sqlplus" for reports generation
- #su -oraprd
- #cd /oracle/PRD/112_64/rdbms/admin/
- #Sqlplus / as sysdba
- #SQL> @awrrpt.sql
3) Follow the screen instruction. Ex: Slelect the output file format (html),
enter the days value for the reporting etc.
4) Follow by entering the relevant snap id range
5) Wait until the report been generated.
6) Here you go the sample "awrrpt.sql" report
Subscribe to:
Posts
(
Atom
)
