How to purge perfstat??

PERFSTAT

Below sql will trigger perfstat purging,Trigger this in off business hours as this will generate lot of Redo logs and try to purge every 15 days

@sppurge.sql

Use this SQL to get large objects of PERFSTAT.
select segment_name,bytes/1024/1024/1024,segment_type from dba_segments where owner='PERFSTAT' order by 2;


As delete operation is performned by sppurge.sql,Need to perform table movement to claim free space.

alter table PERFSTAT.STATS$SQL_SUMMARY move tablespace SYSAUX

alter table PERFSTAT.STATS$SQL_SUMMARY move tablespace PERFSTAT

select INDEX_NAME,status from dba_indexes where owner='PERFSTAT';

alter index PERFSTAT.STATS$SQL_SUMMARY_PK rebuild online;

NOTE:The above table name may vary but would be of similar kind.

No. Of Redo Log Generated in a Month Oracle

Query for No. of Redo Log Generated in a Month

select to_char(first_time,'MM-DD') day, to_char(sum(decode(to_char(first_time,'hh24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'hh24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'hh24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'hh24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'hh24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'hh24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'hh24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'hh24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'hh24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'hh24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'hh24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'hh24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'hh24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'hh24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'hh24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'hh24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'hh24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'hh24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'hh24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'hh24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'hh24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'hh24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'hh24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'hh24'),'23',1,0)),'99') "23"
from v$log_history group by to_char(first_time,'MM-DD') order by 1




select count(*) from v$log_history where to_char(first_time,'MM-DD')='07-19';

  COUNT(*)
----------
        43



select count(*) from v$log_history where to_char(first_time,'MM')='06';

  COUNT(*)
----------
      8242

Metadata(Table,Index,DBLink) sql in Oracle

METADATA(Table,Index and DBLink)

select dbms_metadata.get_ddl('TYPE','OBJECT','OWNER') from dual;

For EX.

select dbms_metadata.get_ddl('DB_LINK','TEST','PUBLIC') from dual;

select dbms_metadata.get_ddl('TABLE','TABLE_TEST','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX,'INDEX_NAME','SCOTT') from dual;

Logical Volume Manager In Linux

LVM

>>Shows all available disk.

>>If working on a new Machine Disk Need to be setup. (/dev/sdb device doesn’t contain a valid partition).
>>We Need to Create Partition or Else Skip this step.

>>n-Will create a new partition
>>p-specifies that partition is primary and then a number between 1-4 to specify partition number.
After p you need to specify the first and the last cylinder of the partition as required
>>w-To write this to disk.

>>List the disk

>>/dev/sdb1 has been added successfully (No invalid Partition).

>>Partprobe –s will Load the changes to operating system.

>>pvcreate to create a physical volume. When successfully created it can be seen through pvdisplay cmd.

>> pvcreate /dev/sdb1

>>vgcreate to create a volume group that will belong to a physical group.

>>Create a mount point directory.

>>lvcreate to create a logical volume, that belongs to a particular volume group.

>>lvdisplay to display the local volume group.

>>mkfs(make file system ) –t will specify the type of file system and then the device name.

>>create entries in /etc/mtab OR /etc/fstab throught this cmd.

>>cat /etc/mtab

>>df -h

LVEXTEND

VGEXTEND

>>Display physical volume which is free(/dev/sdb2) Not associated with any physical group.

LVExtend

Extned mount point to 140G

lvextend -L 140G /dev/mapper/vg_apydb-orabackup
---------------------------------------------------------------------------------
fsadm -e -y resize /dev/mapper/vg_apydb-orabackup 140G

Forcefully Mount
fsadm -e -y -f resize /dev/mapper/vg_apydb-orabackup 140G


To find the process using the Mount point
fuser -cu /orabackup

Database Drill OR DR Drill OR IBCE For RAC

Steps to perform role reversal.

@Production

Verify sync and perform 4-5 log switches to check connectivity and sync b/w PR and DR.

Take cold backup(If Possible) or else take RMAN backup of PR.
Please check RMAN Label for RMAN Backup script.

SQL > set time on timing on

SQL> select name,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;

SQL > Alter database commit to switchover to physical standby with session shutdown;

Wait For END-of-REDO message on alert logs of  PR and match ACTIVATION ID in alert of both PR and DR.

SQL > shutdown immediate

SQL > startup nomount

SQL > alter database mount standby database;

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

@DR

SQL > alter database recover managed standby database cancel;

SQL > alter database commit to switchover to primary;

SQL > shutdown immediate

SQL > Startup

SQL > select name,open mode,log mode from v$database;

SQL > select * from v$recover file;

SQL > select * from v$tempfile;

SQL > alter system set log archive dest state 3='ENABLE';

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

@Production

SQL > alter system set log archive dest state 2='DEFER';

Start MRP Process

SQL > alter database recover managed standby database parallel 64 disconnect from session ;

SQL > select dest_id,error,status from v$archive_dest_status;

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

@DR

SQL > alter system switch logfile;

Check log applying in PR.

select process,status,sequence#,block# from v$managed_standby;

Incase if MRP is not picking up the archive log,try doing enable and defer for the destination.

That will help,Or else check configuration.

NOTE : RAC and Single instance steps for DR Drill is same,The only thing to be done incase of RAC is to convert PR DR into single instance.

That can be done by shutting down the other instance apart from Master node.

For Example if a 2 Node RAC ,Convert it to single node for both PR and DR(shutting down instance 2),After role reversal just need to startup the other node in desired mode.

*****************DONE*******************************