Unix Script for renaming archivelog file with have different SID

Below useful unix script while performing Test Server Refresh.

cd /oracle/<TSID>/saparch
for i in 'ls|grep <SSID>';
do
file_name='echo $i |cut -dD -f2';
mv $i <TSID>${file_name};
done

Monitoring how much time physical standby database behind primary.

SQL> select * from V$DATAGUARD_STATS;

NAME                      VALUE              UNIT                           TIME_COMPUTED                  DATUM_TIME
------------------------- ------------------ ------------------------------ ------------------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply lag                 +00 12:05:02       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply finish time         +00 00:00:04.972   day(2) to second(3) interval   09/03/2012 18:32:37
estimated startup time    23                 second                         09/03/2012 18:32:37

SQL>

if you see above apply lag showing 12 hrs 05 min and 2 sec behind primary.

but when check archived log list @ primary.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1145
Next log sequence to archive   1148
Current log sequence           1148
SQL>

as per above current redo log sequence 1148

Now when i checked @ standby server last applied log sequence number.

SQL> select sequence# last_sequence#,archived,applied,status from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

LAST_SEQUENCE# ARC APPLIED   S
-------------- --- --------- -
          1147 YES YES       A

SQL>

its showing 1147

last archived log already applied @ standby, So how apply tag showing value of 12 hrs and 05 min and 2 sec behind primary ?

Check last archived log time at primary.
[root@testsap1 ~]# ls -la /oracle/EHP/oraarch/ | grep 1147
-rw-r-----  1 oraehp dba 43153408 Sep  3 06:22 EHParch1_1147_790785542.dbf
[root@testsap1 ~]#

Its difference of last archived time and current time. ( current time 18:27:03 last archive log (1147) @ 06:22 ).

Installed RPM Package 32bit / 64bit

Normally we execute below command to know package installed or not and you get result similar to below.
[root@testsap1 ~]# rpm -qa |grep libaio-0.3.106
libaio-0.3.106-5
libaio-0.3.106-5

Need to execute as below to know installed package are 32bit or 64bit ?
[root@testsap1 ~]# rpm -q --qf '%{NAME}-%{VERSION}(%{ARCH})\n' libaio-0.3.106
libaio-0.3.106(x86_64)
libaio-0.3.106(i386)
[root@testsap1 ~]#

Remote file / directory copy in linux

Copy temp.data1 files from remote computer testsap2 into /oracle/EHP/sapdata1 directory of local computer.
scp oraehp@testsap2:/oracle/EHP/sapdata1/temp.data1 /oracle/EHP/sapdata1/

copy directory sapdata1 from remote computer testsap2 to local computer under /oracle/EHP
cd /oracle/EHP
scp -r oraehp@testsap2:/oracle/EHP/sapdata1/ .

Mount/access Windows shares from Linux

[root@testsap2 mnt]# mount -t cifs //10.16.2.20/dbbackuplnx /mnt/share -o username=Administrator
Password:
[root@testsap2 mnt]# cd /mnt/share

Access Red Hat Enterprise Linux 5 with XManager Remote Control

1. Open the /etc/gdm/custom.conf file.
search for "[xdmcp]", add the Enable entry:
add new line with Enable=true
Save the file

2.Reboot the server (I don't know if this is necessary, I have grown used to the Windows way of doing things)

SAP IDES Server

http://www.ides.consolut.eu/sap/bc/gui/sap/its/zuser_req

http://www.ides.consolut.eu/

... conveniently and free of charge with consolut

consolut provides an IDES environment of this type at www.ides.consolut.eu. During your free-of-charge online tour through the SAP ERP world, you will experience typical business processes in the consumer goods, machinery and service sectors.

As we would like to offer you as many functions as possible, access authorizations are very comprehensive. In the interest of all users, we appeal to your fairness and ask you not to make excessive use of these authorizations.
How to obtain an user profile for the SAP IDES system ?

Please use the following URL in order to request user information:
Request a free IDES User http://www.ides.consolut.eu/user_request

(Privacy information)

We will create a user profile for you and send you your password as soon as possible. Please use a mail address where you can actually be reached as it is sometimes necessary to lock out a user for licence reasons. We can then send a new password to your email address.
Start WebAccess http://www.ides.consolut.eu/
Reset Password / Unlock User http://www.ides.consolut.eu/user

Hi,

On first day post diwali holidays, come across below problem with my development server.

Getting below error message when executed startsap ALL
/usr/sap/DEV/SYS/exe/run/startdb: Terminating with error code 12


When check startdb.log.
Its showing database started successfully but is there is a issue with R3trans which failed to connect with database.

IPLDEVQLS:devadm 23> tail -f startdb.log
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

------------------------------ Sat Oct 29 10:22:46 IST 2011
Connect to the database to verify, that the database is now open

R3trans check finished with return code: 12
*** ERROR: Startup of database failed
Notify Database Administrator.
/usr/sap/DEV/SYS/exe/run/startdb: Terminating with error code 12

Why R3trans -d failed hence check trans.log,
Now got the reason.

4 ETW000 [dev trc ,00000] Starting user session: OCISessionBegin(con_hdl=0, usr=SAPSR3/, svchp=0x60000000010d1968, srvhp=0x60000000010d51b8, usrhp
=0x6000000001163ee0)
4 ETW000 272 0.150937
4 ETW000 [dev trc ,00000] OCISessionBegin(OCI_DEFAULT) failed with -1=OCI_ERROR 2936 0.153873
4 ETW000 [dev trc ,00000] OCISessionBegin(OCI_DEFAULT) failed with SQL error 28001: 179 0.154052
4 ETW000 [dev trc ,00000] ORA-28001: the password has expired
4 ETW000 35 0.154087
4 ETW000 [dev trc ,00000] server_detach(con_hdl=0,stale=1,svrhp=0x60000000010d51b8) 21 0.154108
4 ETW000 [dev trc ,00000] Detaching from DB Server (con_hdl=0,svchp=0x60000000010d1968,srvhp=0x60000000010d51b8)
4 ETW000 33 0.154141
4 ETW000 [dev trc ,00000] Deallocating server context handle 0x60000000010d51b8 from con_hdl=0
4 ETW000 1118 0.155259
4 ETW000 [dbsloci. ,00000] *** ERROR => CONNECT failed with sql error '28001' 36 0.155295
4 ETW000 [dev trc ,00000] set_ocica() -> SQL error code 28001 20 0.155315
4 ETW000 [dev trc ,00000] -->oci_get_errmsg (con_hdl=0, rc=28001) 21 0.155336
4 ETW000 [dev trc ,00000] OCIErrorGet -> SQL error code: 28001 112 0.155448
4 ETW000 [dev trc ,00000] ORA-28001: the password has expired
4 ETW000 37 0.155485
4 ETW000 [dblink ,00433] ***LOG BY2=>sql error 28001 performing CON [dblink#8 @ 433] 186 0.155671
4 ETW000 [dblink ,00433] ***LOG BY0=>ORA-28001: the password has expired [dblink#8 @ 433] 22 0.155693
2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DEV'"

So root cause is password of SAPSR3 user is expired.

To resolve connect with sys user and set the password same as old.
SQL> conn sys as sysdba
Enter password:
Connected.

SQL> conn SAPSR3
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for SAPSR3
New password:
Retype new password:
Password changed
Connected.
SQL> exit

R3trans -d and its get success.
IPLDEVQLS:devadm 28> R3trans -d
This is R3trans version 6.14 (release 700 - 19.01.11 - 11:44:00).
unicode enabled version
R3trans finished (0000).

So can start SAP Instance.
IPLDEVQLS:devadm 29> startsap ALL

Checking DEV Database
------------------------------
ABAP Database is running

Starting SAP-Collector Daemon
------------------------------
11:14:03 29.10.2011 LOG: Effective User Id is root
***********************************************************************
* This is Saposcol Version COLL 20.95 700 - V2.7.1 2008-08-11 HP-UX IA64
* Usage: saposcol -l: Start OS Collector
* saposcol -k: Stop OS Collector
* saposcol -d: OS Collector Dialog Mode
* saposcol -s: OS Collector Status
* The OS Collector (PID 9370) is already running .....
************************************************************************
saposcol already running

Starting SAP Instance DVEBMGS00
------------------------------
Startup-Log is written to /home/devadm/startsap_DVEBMGS00.log
Instance Service on host IPLDEVQL started
Instance on host IPLDEVQL started

IPLDEVQLS:devadm 30>


Conclusion :
As of Oracle Database 11g Release 2 (11.2), the default limit for PASSWORD_LIFE_TIME for the DEFAULT profile is 180 days. Prior to Oracle Database 11g Release 2 (11.2), the default was UNLIMITED.

Permanent solution refer SAP Note 1519872 - SAP Database User Profile SAPUPROF

How to find the which objects in which tablespace OR list of objects in tablespace.

select owner, table_name, tablespace_name
from dba_tables
where table_name='EMP';


select owner, table_name, tablespace_name
from dba_tables
where tablespace_name='PSAPSR3USR';

How to resolve HARMFUL_STATISTICS ERROR message appeared during DATABASE CHECK job.

In my database check job come across below error message.

19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.DDXTF
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.DDXTT
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (table) SAPSR3.TRBAT
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.DDXTF~0
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.DDXTT~0
19.07.2011 04:02:45 BR0970W Database administration alert - level: ERROR, type: HARMFUL_STATISTICS, object: (index) SAPSR3.TRBAT~0


To resolve neeed to delete harmful statistics through BRTOOLS.

tstmig:orapip 1> brtools
BR0651I BRTOOLS 7.20 (12)

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.02
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

1 = Instance management
2 - Space management
3 - Segment management
4 - Backup and database copy
5 - Restore and recovery
6 - Check and verification
7 - Database statistics
8 - Additional functions
9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
7
BR0280I BRTOOLS time stamp: 2011-07-19 11.37.17
BR0663I Your choice: '7'

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.17
BR0656I Choice menu 13 - please make a selection
-------------------------------------------------------------------------------
Processing database statistics

1 = Update database statistics
2 - Collect missing
4 - Manage database statistics
statistics
3 - Delete harmful statistics
5 - Collect dictionary statistics
6 - Collect system statistics
7 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2011-07-19 11.37.20
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.20
BR0657I Input menu 46 - please enter/check input values
-------------------------------------------------------------------------------
BRCONNECT main options for deletion of harmful statistics

1 - BRCONNECT profile (profile) ....... [initPIP.sap]
2 - Database user/password (user) ..... [/]
3 ~ Database owner for delete (owner) . []
4 ~ Ignore DBSTATC table (ignore) ..... []
5 ~ Exclude from delete (exclude) ..... []
6 ~ Tables for delete (table) ......... [harmful]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2011-07-19 11.37.53
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRTOOLS time stamp: 2011-07-19 11.37.53
BR0657I Input menu 47 - please enter/check input values
-------------------------------------------------------------------------------
Additional BRCONNECT options for deletion of harmful statistics

1 - Confirmation mode (confirm) ...... [yes]
2 - Query mode (query) ............... [no]
3 ~ Force options (force) ............ []
4 - Extended output (output) ......... [no]
5 - Message language (language) ...... [E]
6 - BRCONNECT command line (command) . [-p initPIP.sap -l E -f stats -d -t "harmful"]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2011-07-19 11.38.02
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0291I BRCONNECT will be started with options '-p initPIP.sap -l E -f stats -d -t "harmful"'

BR0280I BRTOOLS time stamp: 2011-07-19 11.38.02
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRTOOLS time stamp: 2011-07-19 11.38.11
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

###############################################################################

BR0801I BRCONNECT 7.20 (12)
BR0805I Start of BRCONNECT processing: cegiznmy.dst 2011-07-19 11.38.12
BR0484I BRCONNECT log file: /oracle/PIP/sapcheck/cegiznmy.dst

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.17
BR0813I Schema owner found in database PIP: SAPSR3*

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.32
BR0814I Number of tables in schema of owner SAPSR3: 71043
BR0836I Number of tables treated as info cubes for owner SAPSR3: 93

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.55
BR0815I Number of indexes in schema of owner SAPSR3: 84920

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0818I Number of tables found in DBSTATC for owner SAPSR3: 391

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0807I Name of database instance: PIP
BR0808I BRCONNECT action ID: cegiznmy
BR0809I BRCONNECT function ID: dst
BR0810I BRCONNECT function: stats
BR0812I Database objects for processing: HARMFUL
BR0852I Number of tables to delete statistics: 3
Owner SAPSR3: 3
DDXTF DDXTT TRBAT
BR0856I Number of indexes to delete statistics: 0
BR0860I Delete option set - statistics will be deleted

BR0280I BRCONNECT time stamp: 2011-07-19 11.38.56
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRCONNECT:
c
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.15
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0280I BRCONNECT time stamp: 2011-07-19 11.39.15
BR0872I Deleting table and index statistics...
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.DDXTF
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.DDXTT
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0874I Statistics deleted for table SAPSR3.TRBAT

BR0280I BRCONNECT time stamp: 2011-07-19 11.39.16
BR0873I Statistics deleted for 3/0 tables/indexes

BR0806I End of BRCONNECT processing: cegiznmy.dst 2011-07-19 11.39.16
BR0280I BRCONNECT time stamp: 2011-07-19 11.39.17
BR0802I BRCONNECT completed successfully

###############################################################################

BR0292I Execution of BRCONNECT finished with return code 0

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.17
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRTOOLS:
s
BR0280I BRTOOLS time stamp: 2011-07-19 11.39.34
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2011-07-19 11.39.36
BR0257I Your reply: 'y'
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-07-19 11.39.36
BR0654I BRTOOLS terminated with errors
tstmig:orapip 2> exit
tstmig:orapip 3> logout