How to find out the locations of CRD files in Oracle

How to find out the locations of CRD files in Oracle

SQL> select distinct regexp_substr(name,'^.*/')from v$datafile;         

SQL> select distinct regexp_substr(member,'^.*/') from v$logfile;          

SQL> select distinct regexp_substr(name,'^.*/') from v$controlfile;                                                 

select name from v$datafile;

select name from v$controlfile;

select member from v$logfile;

How to check the connectivity between primary and standby database

How to check the connectivity between primary and standby database

From DR/STANDBY:

C:\Users\Administrator>sqlplus sys@PRODORA1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 16 09:22:14 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------

PRIMARY

SQL>

From MAIN/PRIMARY:

C:\Users\Administrator>sqlplus sys@PRODDR1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 16 09:22:14 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE

-----------------------------

PHYSICAL STANDBY

SQL>

How to save sql queries in sqlplus using save command

How to save sql queries in sqlplus using save command

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

--------------

        294025

SQL> save max

Created file max.sql

SQL>

SQL>

SQL> @max

MAX(SEQUENCE#)

--------------

        294025

How to remove junk characters in sqlplus command

How to remove junk characters in sqlplus command

!stty erase ^H

How to modify the file without opening it using vi?

How to modify the file without opening it using vi?

which sed

/bin/sed

TRICK ------> EXAMPLE

cat test.txt

this is a simple trick to modify a file without opening it................

sed -i 's/trick/example/g' test.txt

cat test.txt

this is a simple example to modify a file without opening it................

How to check database startup and shutdown details in Alertlog

How to check database startup and shutdown details in Alertlog

Db startup time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Starting ORACLE/{print buf,$0}'

Db shutdown time:

cat alert*log |awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /Shutting down instance/{print buf,$0}'

Oracle Networking Between Server & Client

Oracle Networking Between Server & Client

Server Side:

- set the IP address and host name on server.
- check ip using # ipconfig.
- make the listener.ora aby using $ netmgr.
- ping the client machine on server side.


Client Side:

- set the IP address and host name on client.
- check ip using # ipconfig
- make the listener.ora by using $ netmgr.
- ping the server macine on client side.
- make tnsnames.ora file on client side $ netca.


Client side database:

netmgr --> listener --> expand the listener --> delete the old listener --> add listener --> add address (hostname and ip) --> choose database services (sid and global sid)

netca --> naming methods configuration --> local naming --> next --> next --> local net service naming configuration --> add --> <service_name_server_side> --> <hostname> --> next --> perform a test --> system/manage --> next --> net service name --> click no and choose finish.

Trick to read a database alertlogfile in detail

Trick to read a database alertlogfile in detail

[oracle@node1 trace]$ ls -ltrh alert_QLAB.log
-rw-r----- 1 oracle oinstall 61K Feb 20 00:32 alert_QLAB.log
[oracle@node1 trace]$ du -sh alert_QLAB.log
68K     alert_QLAB.log
[oracle@node1 trace]$ cat alert_QLAB.log |wc -l
1496
[oracle@node1 trace]$
[oracle@node1 trace]$ tail -500 alert_QLAB.log > newalerbymkm.log
[oracle@node1 trace]$ du -sh newalerbymkm.log
20K     newalerbymkm.log
[oracle@node1 trace]$ cat newalerbymkm.log |wc -l
500
[oracle@node1 trace]$vi newalerbymkm.log

Simple explanation of xplan plan

Simple explanation of xplan plan

SQL> explain plan for select * from emp where deptno=20;

SQL> create index idx1 on emp(deptno);
Index created.

Installing Sample Schemas

Installing Sample Schemas

SQL> @?/rdbms/admin/scott.sql
SQL> @?/sqlplus/demo/demobld.sql
SQL> @?/rdbms/admin/utlsampl.sql

Multiple xplain plan

Multiple xplain plan

explain plan for select * from mqm101;
select * from table(dbms_xplan.display());

Need to use SET STATEMENT_ID

statement.1
explain plan SET STATEMENT_ID='EXPLAIN1'
for select * from mqm101;

check the explain plan table status:
select plan_id, statement_id from plan_table;

statement.2
explain plan SET STATEMENT_ID='EXPLAIN2'
for select * from mqm102;

check the explain plan table status:
select plan_id, statement_id from plan_table;

statement.3
explain plan SET STATEMENT_ID='EXPLAIN3'
for select * from mqm103;

check the explain plan table status:
SQL> select plan_id, statement_id from plan_table;

   PLAN_ID STATEMENT_ID
---------- ------------------------------
         5 EXPLAIN1
         5 EXPLAIN1
         6 EXPLAIN2
         6 EXPLAIN2
         7 EXPLAIN3
         7 EXPLAIN3

Now generate the execution plan:

select * from table(dbms_xplan.display());
or
select * from table(dbms_xplan.display('PLAN_TABLE','EXPLAIN1','TYPICAL',NULL ));

RMAN useful information

RMAN useful information

The RMAN commands "crosscheck archivelog all" and "delete noprompt expired archivelog all" are used to clear RMAN's lookups so that the next RMAN run of "backup archivelog" does not look for non-existent archivelogs. However, you select "v$archived_log" and it will still see all the archive logs.

The number of records in V$ARCHIVED_LOG is 467, the records_used, last_index & last_recid in V$CONTROLFILE_RECORD_SECTION are 467, the number seems to be the same as in V$ARCHIVED_LOG.

V$ARCHIVED_LOG entries will be maintained for as long as CONTROLFILE_RECORD_KEEP_TIME.

V$CONTROLFILE_RECORD_SECTION shows you the number and size of the entries.

Trick to remove and trim alertlog file in oracle

Trick to remove and trim alertlog file in oracle

[root@rac1 trace]# du -sh alert_ORCL1.log
148K    alert_ORCL1.log
[root@rac1 trace]# pwd
/u02/app/oracle/diag/rdbms/orcl1/ORCL1/trace
[root@rac1 trace]# cat /dev/null > alert_ORCL1.log
[root@rac1 trace]# du -sh alert_ORCL1.log
0       alert_ORCL1.log

Below is the another terminal where i already opened the alertlog:

[root@rac1 trace]# tail -f alert_ORCL1.log

Wed Feb 12 23:07:24 2020
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Wed Feb 12 23:12:48 2020
Resize operation completed for file# 3, old size 645120K, new size 655360K
tail: alert_ORCL1.log: file truncated
Wed Feb 12 23:16:17 2020
Thread 1 advanced to log sequence 22 (LGWR switch)
  Current log# 1 seq# 22 mem# 0: /u02/app/oracle/oradata/ORCL1/redo01.log
Wed Feb 12 23:16:17 2020
Archived Log entry 10 added for thread 1 sequence 21 ID 0x54684e49 dest 1:

If you remove the alertlogfile during the database up and running nothing will happen below are the commands after you removing the alertlog it will automatically created another except "alter system checkpoint" command.

alter system switch logfile;
alter database backup controlfile to trace;
alter database begin backup;

Note:

Before implementing on production, please test on development or UAT boxes.

Re-organization Activity

Re-organization Activity

Reorganization is very useful in order to reduce space used by blocks and it also helps in improving the performance of the Oracle Database. It also helps to reduce the fragmentation.

There are 3 ways to do re-organization activity:

1.Export/Import.
2.Alter table Move.
3.CTAS method.

Schema Refresh

Schema Refresh

Schema refresh are of two types, they are:

1. Refresh the database schema with production database.
2. Refresh the database schema from production to some other databsae like DEV/TEST.

Steps:
-Find the roles & privileges that are assigned to the schema.
-Capture source database schema objects count.
-Export database schema using EXP/EXPDP utililty.
-Recreate the schema with default tablespaces and allocate quota.
-Make required roles & privileges for the schema.
-Import the dump file using IMP/IMPDP into the target database.
-Recompile invalid objects.
-Gather schema statistics after the refresh.

How To Create Excel Reports In Database

set feed off markup html on spool on
spool example.xls
select * from tab;
spool off
set markup html off spool off