GET TIME of SQL in SQL PROMPT and PUTTY

GET TIME of SQL in SQL PROMPT and PUTTY


if you want to get the sql execution time use this method


set timing on;

select username from dba_users;
select * from tab;

Find when the table was created

SELECT created  FROM dba_objects WHERE object_name='ASP_USER' AND owner='PORTAL' AND object_type = 'TABLE';

select table_name, to_char(create_date, 'yyyy-mm-dd') from all_tables where table_name='ASP_USER' group by table_name;



 

CREATE and ALTER INDEXES

create and alter INDEXES with synonyms and examples


########################### SYNTAXES #######################
CREATE [UNIQUE] INDEX index_name  ON table_name (column1, column2, ... column_n)  [ COMPUTE STATISTICS ] TABLESPACE QP_INDEX;
CREATE [UNIQUE] INDEX index_name  ON table_name (function1, function2, . function_n)  [ COMPUTE STATISTICS ] TABLESPACE QP_INDEX;----Function means conditions

ALTER INDEX index_name  RENAME TO new_index_name TABLESPACE QP_INDEX;-------------Rename syntax
ALTER INDEX index_name  REBUILD COMPUTE STATISTICS TABLESPACE QP_INDEX;------- Rebuild statistics/index

DROP INDEX index_name;--------------------drop index sysntax

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]  ON tbl_name (index_col_name,...)  [index_option] ...
index_col_name:
    col_name [(length)] [ASC | DESC]
index_type:
    USING {BTREE | HASH}
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name



################# EXAMPLES #######################
CREATE INDEX supplier_idx  ON supplier (supplier_name) TABLESPACE QP_INDEX;
CREATE INDEX supplier_idx  ON supplier (supplier_name, city)  COMPUTE STATISTICS TABLESPACE QP_INDEX;
CREATE INDEX supplier_idx  ON supplier (UPPER(supplier_name)) TABLESPACE QP_INDEX;-----------------------------------function example
ALTER INDEX supplier_idx  RENAME TO supplier_index_name TABLESPACE QP_INDEX;---------------------------------------rename example
ALTER INDEX supplier_idx  REBUILD COMPUTE STATISTICS TABLESPACE QP_INDEX;--------------------------------------rebild example
DROP INDEX supplier_idx;------------------------------------------------------------------------------- drop idex example




 

SYNC ARCHIVES if HUGE archives get not in SYNC with PRIMARY

SYNC ARCHIVES if HUGE archives get not in SYNC on STANDBY with PRIMARY

SB> Startup mount;
--------------archive log list


check last applied archive log
--------------select max(sequence#) from v$archived_log;

check cuuecnt SCN
--------------select current_SCN from v$database;

try to recover without backup
--------------Recover standby database;

PB> select sequence#, name from v$archived_log where sequence# > 9052;


PB> RMAN target /
RUN {
ALLOCATE CHANNEL C1 TYPE DISK FORMAT '/home/oracle/BACKUP/FOR_STANDBY_%U';
ALLOCATE CHANNEL C2 TYPE DISK FORMAT '/home/oracle/BACKUP/FOR_STANDBY_%U';
ALLOCATE CHANNEL C3 TYPE DISK FORMAT '/home/oracle/BACKUP/FOR_STANDBY_%U';
ALLOCATE CHANNEL C4 TYPE DISK FORMAT '/home/oracle/BACKUP/FOR_STANDBY_%U';
backup incremental from scn 3192439481 database tag 'FORSTANDBY' format '/home/oracle/SYNC_back_%d_%t_%s_%p';
RELEASE CHANNEL C1;
RELEASE CHANNEL C2;
RELEASE CHANNEL C3;
RELEASE CHANNEL C4;
}


RMAN >backup current controlfile for standby format '/home/oracle/FORSTDBYCTRL.bck';
PB> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby_control.ctl';


scp the backup files from PRIMARY to STANDBY (same location in PRIMARY)

PB> scp oracle@qpdb201:/home/oracle/backup_control_file oracle@qpdb101:/home/oracle/backup_control_file

PB> scp oracle@qpdb201:/home/oracle/backup_file oracle@qpdb101:/home/oracle/backup_file

SB> SHUT immediate
SB> startup nomount
SB> rman target /

RMAN> restore stabdby controlfile from '/home/oracle/FORSTDBYCTRL.bck';
RMAN> alter database mount;
RMAN> catalog start with '/home/oracle/';
RMAN> catalog backuppiece  '/home/oracle/DG01_830395300_22_1';
RMAN> Recover database noredo;

SB> sqlplus / as sysdba
--------alter database flashback off;
--------alter database flashback on;
--------alter database recover managed standby database disconnect from session;

PB> and SB> archive log list
PB> and SB> select current_SCN from v$database;

check the CURRENT_SCN is very near comparision(not equal, it should be very near similar to PRIMARY)
check the ARCH sequence in both PRIMARY and STANDBY




 

STEPS to SYNC PRIMARY DB and STANDBY DB

STEPS to SYNC PRIMARY DB and STANDBY DB

sync primary database and standby database if archive gap is very less

1.check the alert log from PRIMARY side and STANDBY side for problem
sqlplus / as sysdba
archive log list
show parameter alert
! or host
cd /u01/app/oracle/diag/rdbms/test/TEST/trace/alertlog.log

2. check on what sequence of archive its got problem
3.copy the particular archive from PRIMARY to standby
cd /u04/oracle/test/oraarch/
scp oracle@test:/u04/oracle/test/oraarch/twest_01.arc oracle@testsb:/u04/oracle/test/oraarch/test_01.arc

4.start STANDBY database in mount mode
startup nomount
alter database mount;

5.stop the MRP process on STANDBY
6.recover the database using the archive log which is copied from PRIMARY
alter database recover managed standby database cancel;
recover database;
copy the path of archive log

7.Then bring up the MRP process
alter database recover managed standby database disconnect from session;

8.Check the archive logs are in sync in both PRIMARY and STANDBY
archive log list
select thread#,sequence#,applied,archived from v$archived_log;