How to Move schema data from USERS tablespace to their own tablespace

#TASK – – Move schema (ABC) data from USERS tablespace to their own tablespace It is very common for objects to be created in wrong tablespaces especially the USERS tablespace. Part of the DBA’s task is to move these objects to the right tablespace. First. you want to note all objects belonging to the schema. –Create new tablespaces to host schema data CREATE SMALLFILE TABLESPACE XXXX_ts DATAFILE ‘XXXX_01.dbf’ SIZE 32767M AUTOEXTEND ON NEXT 512M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; — Add enough datafiles on the tablespace (This depends on the size of the data to … Continue reading How to Move schema data from USERS tablespace to their own tablespace

CRONTAB FOR SCHEDULING JOBS

Before scheduling a job on crontab, the DBA must have created and tested the script for a successful execution. You can view the current cron setting using the below command; cat /etc/crontab * * * * * Command_to_execute | | | | | | | | | +?? Day of week (0?6) (Sunday=0) or Sun, Mon, Tue,… | | | +???- Month (1?12) or Jan, Feb,… | | +????-? Day of month (1?31) | +??????? Hour (0?23) +????????- Minute (0?59) Below are some examples of cron jobs; 0 6,18 * * /home/oracle/dba/scripts/rm_listener.sh >> /home/oracle/rmlistener.log 0 1 * * * /home/oracle/dba/scripts/expdp_orcl.sh … Continue reading CRONTAB FOR SCHEDULING JOBS

TYPES OF BACKUPS, HOW TO ENABLE ARCHIVELOG MODE, CONFIGURE FOR RECOVERABILITY

BACKUPS THERE ARE 2 MAJOR TYPES OF BACKUPS; – PHYSICAL bkp   — Recovery manager (RMAN) – LOGICAL  bkp   — Datapump  imp/exp — 10g impdp/expdp — 11g and higher BACKUPS ARE FURTTHER SEPARATED INTO 2 SUBTYPES; HOT bkp — inconsistent backup because the DB is open to 3tier and 2tier users while backup is in progress COLD bkp — consistent backup because the DB is inaccessible to any user while the backup is running. Usually, the DB is MOUNTED for cold bkp   FRA = /u06/oradata/FRA (This was my FRA) CONFIGURE FOR RECOVERABILITY (TELLING THE DB WHERE TO KEEP … Continue reading TYPES OF BACKUPS, HOW TO ENABLE ARCHIVELOG MODE, CONFIGURE FOR RECOVERABILITY

STEPS FOR REFRESH OR DATAPUMP

Database exports are common task been asked of every Oracle database admin. Close your eyes and imagine a table created in 2015. At the time of creation, asssume it was say 500 GB in size in development (DEV), it was pushed to TEST database and then PROD. 2 years later, the table is now 8TB large. This means we have 7.5 TB of data on the PROD table. The developers and QA testers need this data in DEV and TEST regions respectively. The only way to get this data across is via refresh/datapump as described below; NB: step 1 to … Continue reading STEPS FOR REFRESH OR DATAPUMP

How to create a Recovery Catalog

***The sole purpose of the recovery catalog is to hold or store database backups.  As long as the recovery catalog schema exists, we can recover datafiles, controlfiles, pfiles, spfiles  and redologfiles from the recovery catalog in case of a crash. Proactively use the recovery catalog as part of your backup strategy.*** RECOVERY CATALOG ================ 1. Create RCAT(recovery catalog database) tablespace SQL> create tablespace rcat_ts datafile ‘/u02/oradata/kud02/rcat_ts.dbf’ size 100M; Tablespace created. SQL> 2. create a RCAT user SQL> create user RCAT identified by RCAT default tablespace rcat_ts temporary tablespace temp quota unlimited on rcat_ts; User created. SQL> 3. Grant the recovery_catalog_owner … Continue reading How to create a Recovery Catalog

Agent Resync or Agent Unreachable Issues, Can’t Communicate with OMS– ORACLE 12C

  Proper Resync of an Agent Log onto the server that is reporting it’s blocked. FOR WINDOWS go to E:\app\oracle\agent12c\core\12.1.0.2.0\bin> If Linux/Unix, go to the $AGENT_HOME/bin directory 1. Stop the agent: ./emctl stop agent Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. The Oracleagent12c1Agent service is stopping…………. The Oracleagent12c1Agent service was stopped successfully. 2. Secure the agent: ./emctl secure agent Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Agent is already stopped… Done. Securing agent… Started. Enter Agent Registration Password : <– … Continue reading Agent Resync or Agent Unreachable Issues, Can’t Communicate with OMS– ORACLE 12C