Thursday, March 26, 2020

What flavor of RedHat am I using

cat /etc/redhat-release

Posted by at No comments:

How to Test RMAN Tape Backup

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 26 21:46:47 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN>
connected to target database: HYPP (DBID=2806684771)

RMAN>
connected to recovery catalog database

RMAN>
echo set on


RMAN> run {
2> allocate channel oem_sbt_backup type 'sbt_tape' format '%d_%U' parms "NBR_BACKUP_CHANNELS=4NB_ORA_CLASS=ora_octavo_hyppNB_ORA_CLIENT=octavo.ecp.privNB_ORA_SCHED=oracleNB_ORA_SERVER=flexo.ecp.priv";
3> backup as BACKUPSET current controlfile tag '03262020094644';
4> restore controlfile validate from tag '03262020094644';
5> release channel oem_sbt_backup;
6> }
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on oem_sbt_backup channel at 03/26/2020 21:47:19
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27209: syntax error in device PARMS - unknown keyword or missing =


RMAN> allocate channel for maintenance type 'sbt_tape'  parms "NBR_BACKUP_CHANNELS=4NB_ORA_CLASS=ora_octavo_hyppNB_ORA_CLIENT=octavo.ecp.privNB_ORA_SCHED=oracleNB_ORA_SERVER=flexo.ecp.priv";
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ORA_MAINT_SBT_TAPE_1 channel at 03/26/2020 21:47:19
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27209: syntax error in device PARMS - unknown keyword or missing =


RMAN> delete noprompt backuppiece tag '03262020094644';
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1764 device type=DISK
released channel: ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 03/26/2020 21:47:46
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27209: syntax error in device PARMS - unknown keyword or missing =


RMAN> exit;

Recovery Manager complete.


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

 run
{
allocate channel t1 device type sbt;
send 'NB_ORA_SERV=agathor,NB_ORA_CLIENT=hermes.ecp.priv';
set archivelog destination to '/dbapps/oracle';
restore archivelog from logseq=48626 until logseq=48627 thread 1;
}

Posted by at No comments:

Wednesday, March 25, 2020

RMAN Restore Database

Recovery Manager complete.
! cpwebpklorblok:/dbapps/oracle/oradata/CPWEBP> rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Mar 25 02:45:06 2020
Version 18.8.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CPWEBP (not mounted)

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 03/25/2020 02:45:19
ORA-01507: database not mounted

[I CHOOSE THE WRONG BACKUPSET!!!!]

RMAN> restore controlfile from '/dbapps/oracle/fast_recovery_area/CPWEBP/backups                                                                             et/2020_03_25/o1_mf_nnndf_FULL_BACKUP_h7p68km7_.bkp';

Starting restore at 25-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2020 02:47:41
ORA-19697: standby control file not found in backup set

[WISHFUL THINKING!!!]
RMAN> restore database;
Starting restore at 25-MAR-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/25/2020 02:49:41
ORA-01507: database not mounted


RMAN> show all
2> ;

RMAN configuration parameters for database with db_unique_name CPWEBP are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

[GOOD THING CONTROLFILE AUTOBACKUP WAS ON]

RMAN> restore controlfile from '/dbapps/oracle/fast_recovery_area/CPWEBP/autobac                                                                             kup/2020_03_25/o1_mf_s_1035942155_h7p6cwpq_.bkp'
2> ;

Starting restore at 25-MAR-20
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/dbapps/oracle/oradata/CPWEBP/control01.ctl
output file name=/dbaredo/fast_recovery_area/CPWEBP/control02.ctl
Finished restore at 25-MAR-20


RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database;

Starting restore at 25-MAR-20
Starting implicit crosscheck backup at 25-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 25-MAR-20

Starting implicit crosscheck copy at 25-MAR-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-MAR-20

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /dbapps/oracle/fast_recovery_area/CPWEBP/autobackup/2020_03_25/o1_mf_                                                                             s_1035942155_h7p6cwpq_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /dbapps/oracle/oradata/CPWEBP/sy                                                                             stem01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /dbapps/oracle/oradata/CPWEBP/ca                                                                             pex01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /dbapps/oracle/oradata/CPWEBP/sy                                                                             saux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /dbapps/oracle/oradata/CPWEBP/un                                                                             dotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /dbapps/oracle/oradata/CPWEBP/pr                                                                             ess01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /dbapps/oracle/oradata/CPWEBP/us                                                                             ers01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /dbapps/oracle/oradata/CPWEBP/cs                                                                             v2db01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /dbapps/oracle/oradata/CPWEBP/dm                                                                             s01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /dbapps/oracle/oradata/CPWEBP/sm                                                                             r01.dbf
channel ORA_DISK_1: reading from backup piece /dbapps/oracle/fast_recovery_area/                                                                             CPWEBP/backupset/2020_03_25/o1_mf_nnndf_FULL_BACKUP_h7p68km7_.bkp

channel ORA_DISK_1: piece handle=/dbapps/oracle/fast_recovery_area/CPWEBP/backup                                                                             set/2020_03_25/o1_mf_nnndf_FULL_BACKUP_h7p68km7_.bkp tag=FULL BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:26
Finished restore at 25-MAR-20

RMAN>
RMAN> recover database;

Starting recover at 25-MAR-20
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=408
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2020 02:57:52
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq                                                                             uence 408 and starting SCN of 11113178284895

RMAN> alter database open resetlogs;

Statement processed

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    10.23M     DISK        00:00:00     12-SEP-19
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20190912T120812
        Piece Name: /dbapps/oracle/fast_recovery_area/CPWEBP/autobackup/2019_09_                                                                             12/o1_mf_s_1018786092_gqo5wwm2_.bkp
  SPFILE Included: Modification time: 12-SEP-19
  SPFILE db_unique_name: CPWEBP
  Control File Included: Ckp SCN: 11109337668363   Ckp time: 12-SEP-19

Posted by at No comments:

Monday, March 23, 2020

Useful SQL SERVER Commands

--What database am I connected to

select db_name();

--List all tables

select * from information_schema.tables;
Posted by at No comments:

Thursday, March 19, 2020

What port is OEM running

select dbms_xdb.getHttpPort() from dual;
Posted by at No comments:

How to Get CPU Cores Oracle is using 

select
   (select max(value) from dba_hist_osstat 
    where stat_name = 'NUM_CPUS')        c1,
   (select max(value) from dba_hist_osstat 
    where stat_name = 'NUM_CPU_CORES')   c2,
   (select max(value) from dba_hist_osstat 
    where stat_name = 'NUM_CPU_SOCKETS') c3
from dual;
Posted by at No comments:

Tuesday, March 17, 2020

How to gather stats on Oracle

Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can be computed exactly, or estimated based on a specific number of rows, or a percentage of rows.

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS.

The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete} statistics options
ANALYZE index indexName {compute|estimate|delete} statistics options
ANALYZE cluster clusterName {compute|estimate|delete} statistics options

ANALYZE TABLE emp COMPUTE STATISTICS;
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;
ANALYZE TABLE emp PARTITION (p1) COMPUTE STATISTICS;
ANALYZE INDEX emp_pk COMPUTE STATISTICS;

ANALYZE TABLE emp ESTIMATE STATISTICS;
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 500 ROWS;
ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 15 PERCENT;
ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL COLUMNS;

ANALYZE TABLE emp DELETE STATISTICS;
ANALYZE INDEX emp_pk DELETE STATISTICS;

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
ANALYZE INDEX emp_pk VALIDATE STRUCTURE;
ANALYZE CLUSTER emp_custs VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE emp VALIDATE REF UPDATE;
ANALYZE TABLE emp LIST CHAINED ROWS INTO cr;

Note: Do not use the COMPUTE and ESTIMATE clauses of ANALYZE statement to collect optimizer statistics. These clauses are supported solely for backward compatibility and may be removed in a future release. The DBMS_STATS package collects a broader, more accurate set of statistics, and gathers statistics more efficiently.

We may continue to use ANALYZE statement to for other purposes not related to optimizer statistics collection:
  • To use the VALIDATE or LIST CHAINED ROWS clauses
  • To collect information on free list blocks
  • To sample a number (rather than a percentage) of rows
DBMS_UTILITY
The DBMS_UTILITY package can be used to gather statistics for a whole schema or database. With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema. Both methods follow the same format as the ANALYZE statement:

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',ESTIMATE_ROWS=>100);
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',ESTIMATE_PERCENT=>25);
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');EXEC DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
EXEC DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',ESTIMATE_ROWS=>100);
EXEC DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',ESTIMATE_PERCENT=>15);
Posted by at No comments:

How to get last analyzed ORACLE objects

PROMPT Regular Tables

select owner,table_name,last_analyzed, global_stats
from dba_tables
where owner not in (‘SYS’,’SYSTEM’)
order by owner,table_name
/

PROMPT Partitioned Tables

select table_owner, table_name, partition_name, last_analyzed, global_stats
from dba_tab_partitions
where table_owner not in (‘SYS’,’SYSTEM’)
order by table_owner,table_name, partition_name
/

PROMPT Regular Indexes

select owner, index_name, last_analyzed, global_stats
from dba_indexes
where owner not in (‘SYS’,’SYSTEM’)
order by owner, index_name
/

PROMPT Partitioned Indexes


select index_owner, index_name, partition_name, last_analyzed, global_stats
from dba_ind_partitions
where index_owner not in (‘SYS’,’SYSTEM’)
order by index_owner, index_name, partition_name
/

spool off
Posted by at No comments:

Wednesday, March 11, 2020

How to change Oracle Database Name

GO here

http://houseofbrick.com/changing-a-database-name-using-newid/

Posted by at No comments:
Subscribe to: Posts (Atom)