TNS:lost contact while login as sysdba

ORA-12547: TNS:lost contact

Identify the issue
truss -aefo sqlplus.trc sqlplus /nolog
connect / as sysdba

Cause I
shared memory or semaphores leftover from previous instances (abnormal termination)
ipcs -a
remove problematic shared memory segments & semaphores
ipcrm -m & ipcrm -s
delete $ORACLE_HOME/dbs/lk file


Cause II
The /proc file system is not mounted.

Cause III
ulimit settings for 'open files' and 'pipe size' is very low.

$ulimit -n 4096
$ulimit -p 30

Cause IV
The stack limit is set too low.

ulimit -s 32768

Cause V
Invalid LD_LIBRARY_PATH

Set proper LD_LIBRARY_PATH path

Cause VI
dbs folder missing from oracle home
mkdir dbs and then create password & init files

Cause VII
Invalid env variables

echo $ORACLE_HOME
echo $ORACLE_SID
echo $LD_LIBRARY_PATH
echo $PATH

Cause VIII
Wrong file permissions

cd $ORACLE_HOME/bin
ls -l oracle

chmod 6751 oracle

or relink all

Fixing the Mysql Replication when its broken:

Re-building the slave by taking the master dump
-----------------------------------------------

MASTER:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysqldump -uroot -p --events --ignore-table=mysql.events --all-databases > /var/backups/mysqldump.sql
UNLOCK TABLES;
Copy mysql dump file to server 2
(scp -p /var/backups/mysqldump.sql root@192.168.1.106:/tmp)

SLAVE:
STOP SLAVE;
mysql -uroot -p < /tmp/mysqldump.sql
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE IO_THREAD;
copy mysql dump from master
mysql -uroot -p < /tmp/mysqldump.sql
START SLAVE SQL_THREAD;
That should start the downloading of binary logs while you're doing the restore, so you don't miss out on any.


By restoring the binary Log
---------------------------

1) Take the Binary log necessary for the replication to work from "Show Slave Status"
2) Restore the binary logs from backup
MASTER:
a. Restore the logfiles in any location where you have space
b. Add the file names in mysql-bin.index for the master to know the binary log info
c. login to master database and see if binary logs are shown while you query "show binary logs", if you dont see the bin logs run "FLUSH LOGS" and now you should see the bin log files.
Once you see the binary log files in database
3) Stop / Reset the replication and start from the missing logfile
SLAVE:
stop slave;
reset slave;
show warnings;
change master to master_host ='10.99.3.21', master_user='replicator', master_password='pwd', master_log_file='mysql-bin.018736', master_log_pos=47904313;  -- These information can be retreived from slave server /mysqldata/mysql/mysql-master.info
start slave;
show slave status \G

How to change default tablespace for LogMiner tables in Oracle Streams

1) Create a new tablespace for Log miner

CREATE TABLESPACE LOGMNR_TBS DATAFILE ‘/oradata01/STREAMDB/logmnr_tbs01.dbf' size 100m;

2) Move the objects from SYSAUX (default tablespace) to new tablespace.

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/

3) If there are active log miner session, then we'll get following error

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE(‘LOGMNR_TBS');
END;
ORA-01356: active logminer sessions found
ORA-06512: at “SYS.DBMS_LOGMNR_D”, line 233
ORA-06512: at line 2

This error suggests that, there are active logminer sessions in the database.

There are two types of log mining sessions

1) Persistent (CAPTURE process)
2) Non-persistent

We need to stop the LogMiner sessions to change the tablespace.

– Stop Non-persistent

BEGIN
DBMS_LOGMNR.END_LOGMNR();
END;
/
PL/SQL procedure successfully completed.

NOTE: If no logminer sessions are active, we'll get ORA-01307: no LogMiner session is currently active

– Stop Persistent

BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE'); END;
/
PL/SQL procedure successfully completed.

4) Then change the LogMiner tablespace

BEGIN
DBMS_LOGMNR_D.SET_TABLESPACE('LOGMNR_TBS');
END;
/
PL/SQL procedure successfully completed.

5) Confirm the objects are moved to new tablespace

SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'LOGMNR_TBS';


6) Start capture process

BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(CAPTURE_NAME=>'STREAM_CAPTURE');
END;
/
PL/SQL procedure successfully completed.

Issue with running catupgrd.sql while Database upgrade from 10.2.0.4 to 11.2.0.3:

Issue with running catupgrd.sql while Database upgrade from 10.2.0.4 to 11.2.0.3:

Getting below error while running catupgrd.sql scripts while DB upgrade.

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                *
ERROR at line 1:
ORA-01722: invalid number

Cause:
------------
The Pre-Upgrade Script is not creating the registry$database table & inserting the Platform DST Patch Information.The error may also appear if the the registry$database table exists,  but does not contain the required information.

Solution:
-----------------

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit

SQL> create table registry$database_b as select * from registry$database;

Table created.

select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit

SQL> INSERT into registry$database
  2                      (platform_id, platform_name, edition, tz_version)
  3                 VALUES ((select platform_id from v$database),
  4                         (select platform_name from v$database),
  5                          NULL,
  6                         (select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit
         13 Linux x86 64-bit                                             4

SQL> commit;

Commit complete.

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME        EDITION                        TZ_VERSION
----------- -------------------- ------------------------------ ----------
         13 Linux x86 64-bit                                             4

Ref Doc: catupgrd.sql fails With ORA-01722 Invalid Number after running the Pre-Upgrade Script [ID 1466464.1]

Changing the DEFAULT DATE FORMAT in Oracle Database

Changing the DEFAULT DATE FORMAT in Oracle Database.

Changing NLS_DATE_FORMAT parameter to specific value in session/system will be overlooked by the NLS_LANG parameter, so to have the NLS_DATE_FORMAT permanently changed in the DB, we need to create logon trigger as below.

create or replace trigger CHANGE_DATE_FORMAT
after logon
ON DATABASE
begin
   execute immediate
  'alter session set nls_date_format = ''DD/MM/YYYY'' ';
end;
/

Rman Backup Report in HTML Format

Running Rman backup is one of our regular DBA job, but to check if the backups are running fine on daily basis, below script will generate a report and send mails with the report in HTML format. 

SQL Script:

-----------------------Start of rman_status.sql----------------------------------

--Script Location :/app/oracle/scripts/rman_report/rman_status.sh

set pagesize 0

set line 120

set heading off

set feedback off

set verify off

spool /app/oracle/scripts/rman_report/rman_out.txt

select db_name||'|'||INPUT_TYPE||'|'||STATUS||'|'||to_char(START_TIME,'mm/dd/yy hh24:mi')||'|'|| to_char(END_TIME,'mm/dd/yy hh24:mi')||'|'||round((end_time-start_time)*24*60)||'|'||round((INPUT_BYTES/1024/1024/1024),2)||'|'||round((OUTPUT_BYTES/1024/1024/1024),2)||'|'

from rman.rc_RMAN_BACKUP_JOB_DETAILS where input_type in ('DB INCR','DB FULL') and (db_name,start_time) in (select db_name,max(START_TIME) from rman.rc_RMAN_BACKUP_JOB_DETAILS where input_type in ('DB INCR','DB FULL') group by db_name)order by status desc,start_time;

spool off

-----------------------End of rman_status.sql----------------------------------

Shell Script:

----------------------Start of rman_report.sh -------------------------------

#Script Location :/app/oracle/scripts/rman_report/rman_report.sh

export ORACLE_HOME=/app/oracle/product/11.2.0.3.1

export ORACLE_SID=RMANCAT

export PATH=$ORACLE_HOME/bin:$PATH

cd /app/oracle/scripts/rman_report

$ORACLE_HOME/bin/sqlplus "/as sysdba" << EOF

@/app/oracle/scripts/rman_report/rman_status.sql

exit

EOF

rm /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo " " >> /app/oracle/scripts/rman_report/rman_report.html

echo "

" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "" >> /app/oracle/scripts/rman_report/rman_report.html

echo "RMAN Backup Summary   DB_NAMEBACKUP_TYPE STATUS

START_TIME END_TIME   NET_TIME(MIN) DB_SIZE(GB)   BKP_PIECE(GB) " >> /app/oracle/scripts/rman_report/rman_report.html

cat /app/oracle/scripts/rman_report/rman_out.txt|awk  -F '|' '{ if ($3 == "COMPLETED" || $3 == "RUNNING") print " "$1""$2"  "$3"  "$4"  "$5" "$6" "$7" "$8"   "; else print " "$1""$2" "$3" "$4" "$5" "$6" "$7" "$8" " }' >> /app/oracle/scripts/rman_report/rman_report.html

outputFile="/app/oracle/scripts/rman_report/rman_report.html"

(

echo "From: "

echo "To: "

echo "MIME-Version: 1.0"

echo "Subject: RMAN Backup Summary Report"

echo "Content-Type: text/html"

cat $outputFile

) | sendmail -t

This script is proven working in AIX Server.

----------------------End of rman_report.sh -------------------------------

Formatted CRSCTL output for oracle 11gR2

Formatted CRSCTL output for oracle 11gR2:

crsctl status res |grep -v "^$"|awk -F "=" 'BEGIN {print " "} {printf("%s",NR%4 ? $2"|" : $2"\n")}'|sed -e 's/ *, /,/g' -e 's/, /,/g'|  awk -F "|" 'BEGIN { printf "%-40s%-35s%-20s%-50s\n","Resource Name","Resource Type","Target ","State" }{ split ($3,trg,",") split ($4,st,",")}{for (i in trg) {printf "%-40s%-35s%-20s%-50s\n",$1,$2,trg[i],st[i]}}'

ORA-01031 on CREATE MATERIALIZED VIEW

ORA-01031 on CREATE MATERIALIZED VIEW

I had a weird problem  today,

 I tried to create an Materialized view on an application user from sys user and faced ORA-01031 error.

When issuing a CREATE MATERIALIZED VIEW statement for a different schema (as DBA), one might encounter the following error:

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual

                                                         *

ERROR at line 1:

ORA-01031: insufficient privileges

For our setup let’s assume we have two users:

·         User SYS (has the DBA privilege and is used to setup the DB server)

·         User RSHANKAR (schema owner)

We want to use the SYS user to create the objects for the schema RSHANKAR. This is a standard approach to seperate the deployment user from the actual schema owner. So let’s create these two users and grant the privileges (DBA for the deployment user, CONNECT and RESOURCE for the schema owner):

SQL> CREATE USER RSHANKAR IDENTIFIED BY test123;

User created.

SQL> GRANT RESOURCE, CONNECT TO rshankar;

Grant succeeded.

SQL> ALTER USER rshankar QUOTA 100M ON users;

User altered.

SQL> GRANT CREATE MATERIALIZED VIEW TO rshankar;

Grant succeeded.

So far, so good. The user SYS  has all the privileges that come with the DBA role and the user RSHANKAR has a limited set of privileges due to the RESOURCE and CONNECT roles. In addition to those roles, we want to grant CREATE MATERIALIZED VIEW to our schema owner.

Now let’s see what happens when we try to issue the statement as the deployment user (SYS):

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual

                                                         *

ERROR at line 1:

ORA-01031: insufficient privileges

SYS have the DBA privileges, so the problem must be that RSHANKAR does not have the correct privileges! So let’s try with the user RSHANKAR:

SQL> connect rshankar

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM dual;

Materialized view created.

Now the confusion is complete. Why can I create the materialized view while connected as RSHANKAR, but not create the same view when connected as SYS? The user SYS has higher privileges than the user RSHANKAR, so that should not happen! It turns out that this is expected behaviour (MOS Doc 749112.1).

To solve the mystery, grant the CREATE TABLE privilege directly to the schema owner:

SQL> GRANT CREATE TABLE TO rshankar;

Grant succeeded.

Now the CREATE MATERIALIZED VIEW statement works as expected:

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

Materialized view created.

This solve my problem J