Oracle Database Administration

http://www.oracle-scripts.net/standard-datapump-use/ http://www.oracle-scripts.net/generate-user-ddl/

set lines 134 set pages 250 spool /home/oracle/scripts/logs/list_catalog_backup.log; select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP, NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP from (select a.name DB,dbid, decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db, decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch from rc_database a,bs b where a.db_key=b.db_key and b.bck_type is not null and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null) and b.bs_key not in(select bs_key from rc_backup_spfile) group by a.name,dbid,b.bck_type ) group by db,dbid order by least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI')); SELECT db_name, input_type, status, to_char((start_time),...

Database size  col "Database Size" format a20  col "Free space" format a20  col "Used space" format a20  select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"   , round(sum(used.bytes) / 1024 / 1024 / 1024 ) -   round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"   , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"   from (select bytes   from v$datafile   union all   select bytes   from v$tempfile   union all   select bytes   from v$log) used   , (select sum(bytes) as p   from dba_free_space) free   group by free.p   / This script will display a list of Oracle invalid objects: break on c1 skip 2 set pages 999 col c1 heading 'owner' format a15 col c2 heading 'name' format a40 col c3 heading 'type' format a10 ttitle 'Invalid|Objects' select     owner       c1,     object_type c3,...

Ref: http://oracleinaction.com/duplicate-db-no-db-conn/ duplicate a database from its backups without any connection to the source database. This method can be used if source database is not available . ********************************   source database  orcl   Duplicate database  orclt ******************************** Overview: on the source  host - BACKUP DATABASE PLUS ARCHIVELOG AND CONTROLFILE -  Copy these backup files to the server where you want to create the duplicate copy. - CREATE PFILE FROM SOURCE DATABASE on the target host - Add a line in the file /etc/oratab to reflect the database instance you are going to copy - create folders - Copy the backup files from the source database - Copy the initialization parameter file from the source database add edit it. - Copy the password file - Startup the target database in nomount mode using modified parameter file - Using RMAN  connect to the  duplicate database (orclt) a...

Ref:  http://select-star-from.blogspot.com.au/search/label/DATAGUARD Method :1 ========= PRIMARY ======== sqlplus "/ as sysdba" set pages 50000 lines 32767col OPEN_MODE for a10 col HOST_NAME for a10 col DATABASE_ROLE for a7 select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance; SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE; select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log; select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2); set pages 50000 lines 32767Set scan off Set feed off BREAK ON ROW SKIP 1 COL "THREAD" for a10 COL "PR-ARCHIVED" for a15 COL "STBY-ARCHIVED"...