TABLE_EXISTS_ACTION in Datapump
TABLE_EXISTS_ACTION during IMPORT (IMPDP)
In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.
TABLE_EXISTS_ACTION can have following values.
1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.
3)TRUNCATE: This option deletes existing rows and then loads rows from the source.
4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.
- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.
- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.
If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.
- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.
- TRUNCATE cannot be used on clustered tables or over network links.
In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.
===========================================================================================================
How to Copy files from ASM to filesystem and filesystem to ASM
Copy files from ASM to filesystem
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
==================================================================
Schema Refresh using ASM Diskgroups
How to create Datapump Export Dumps within ASM diskgroups
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
EXPORT (EXPDP)
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
cluster=n ---- 11g
:wq
$nohup expdp parfile=schema_export_in_ASM.par &
$tail -f nohup.out or
$tail -f schema_export_in_ASM.log
==================================================================================================================
scp dumpfiles from Source Server ASM to Destination Server ASM
Take schema backup and Drop Schema Objects
MPORT (IMPDP)
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi import_schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n ---- 11g
If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2
:wq
$nohup impdp parfile=import_schema_export_in_ASM.par &
$tail -f nohup.out
$tail -f import_schema_export_in_ASM.log
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
=====================================================================================================================================================
Network Level Import of Schema
How to take Schema Import at Network Level
The NETWORK_LINK parameter initiates a network import. This means that the impdp client initiates the import request, typically to the local database. That server contacts the remote source database referenced by the database link in the NETWORK_LINK parameter, retrieves the data, and writes it directly back to the target database. There are no dump files involved.
1. Create a TNS entry on the local database tnsnames.ora file for the remote database
Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry. The user used in the database link must have read/write permission on the remote servers directory.
SOURCE_DATABASE_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = remote-db)
)
)
2. Create the Database Link on the Destination database:
create database link SOURCE_DATABASE_LINK connect to SYSTEM identified by password using 'SOURCE_DATABASE_LINK';
3. Script to get database link information:
$ sqlplus "/as sysdba"
DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20
COL DB_LINK FORMAT A30
select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;
OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ -------- ------- -------
4. PAR file.
$vi schema_network_level_import.par
user_id="/ as sysdba"
directory=DATA_PUMP_DIR
logfile=schema_network_level_import.log
schemas=schema1,schema2
NETWORK_LINK=source_database_link
FLASHBACK_TIME=SYSTIMESTAMP
exclude=statistics
parallel=6
cluster=n
:wq
$nohup impdp parfile=schema_network_level_import.par &
$tail -f nohup.out
$tail -f schema_network_level_import.log
Optional Parametrs:
------------------
ESTIMATE={BLOCKS | STATISTICS}
FLASHBACK_SCN=scn_number
FLASHBACK_TIME="TO_TIMESTAMP()"
The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified.
The FLASHBACK_SCN parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide an SCN-consistent data extraction.
This parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide a time-consistent import.
FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.
===================================================================================================================================================
BACKUP DATABASE LINK IN ORACLE
Database links backup
---------------------
All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;
For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;
======================================================================================================================================================
DDL
GENERAL USAGE
-------------
SET LONG 99999999
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;
Before droping user:(super Script---> collecting all grants of user)
--------------------------------------------------------------------
set long 100000
select dbms_metadata.get_ddl('USER','&username') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&username') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&username') FROM dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT','&username') from dual;
SET LONG 99999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
select dbms_metadata.get_ddl ('TABLE', '<TABLE_NAME>', '<SCHEMA_NAME>') from dual;
select dbms_metadata.get_ddl ('TABLE', '<SCHEMA_NAME>') from dual;
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;
select dbms_metadata.get_ddl('PROCEDURE',<PROCEDURE_NAME>','<OWNER_NAME>') from dual;
To get the create scripts of all tables of a particular schema
----------------------------------------------------------------
SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''<schema>'') from dual;' FROM DBA_TABLES
/
http://tech.padipa.net/generating-create-scripts-through-dbms_metadata-package
Posted by Dharmendra Chalasani at Tuesday, December 11, 2012
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: DDL
October 25, 2012
PACKAGE BACKUP in ORACLE
PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
spool off;
OR
set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_NAME' and type='PACKAGE' order by line;
PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;
OR
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_BODY_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;
DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='<object_name>';
drop package <owner>.<package_name>;
spool Off;
===================================================================================================================================================
Backup / Delete - Archivelogs and RMAN backups
sqlplus "/as sysdba"
archive log list;
!df -h or !df -kgt
show parameter db_recovery;
NAME TYPE VALUE
---- ---- -----
db_recovery_file_dest string +FLASHDG
db_recovery_file_dest_size big integer 150G
if +FRA,
select * from V$FLASH_RECOVERY_AREA_USAGE;
(see what kind of files are available in the Flash Recovery Area)
set pages 9999 lines 300
col name format a40
select name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---- ----------- ---------- ----------------- ---------------
if SPACE_USED is full, then increase the db_recovery_file_dest_size
rman target /
crosscheck archivelog all;
delete expired archivelog all;
crosscheck backup;
SQL>alter system set db_recovery_file_dest_size=200G scope=both;
System altered.
rman target /
show all;
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1'; ------List all archivelog backups for the past 24 hours
list backup of archivelog all completed before 'sysdate -1';
list backup of archivelog all;
list backup of archivelog all tag 'TAGXXX';
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
crosscheck archivelog all;
list expired archivelog ALL;
delete expired archivelog all;
delete obsolete device type disk;
crosscheck backup;
delete expired backup device type disk;
delete expired backupset device type disk;
ls -ltr archivebackup.cmd
nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
+FRA/+RECO01 filled with archivelogs
====================================
Backup archivelogs to disk
--------------------------
run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to disk (specified location,if there is no space to take bakup at archive default location)
--------------------------------------------------------------------------------------------------------------
RUN
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL format '/location/arch_%d_%p_%s.rman' FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to tape (PROD Environment)
--------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
+FRA filled with Bacupsets (PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;
}
if required,
BACKUP BACKUPSET ALL FILESPERSET 10 DELETE INPUT;
Delete BACKUPSET backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DEVICE TYPE DISK;
+FRA filled with Bacupsets (NON-PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
DELETE BACKUPSET COMPLETED BEFORE 'SYSDATE-4';
}
Delete archivelogs backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
Delete archivelogs backed up to tape/disk (NON-PROD Environment)
-----------------------------------------
DELETE archivelog all BACKED UP 1 TIMES TO DEVICE TYPE DISK;
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
delete archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
backup archivelog [all] [until time 'sysdate']| [sequence between 100 to 110 thread 1] as filesperset 5 delete
input;
backup archivelog from sequence 100 until sequence 110 thread 1 delete input;
backup archivelog from logseq 100 until logseq 110 thread 1 delete input;
report obsolete;
DELETE OBSOLETE REDUNDANCY = 3;
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;
http://anuj-singh.blogspot.in/2011_07_16_archive.html
oracledbazone.wordpress.com/2012/04/18/useful-rman-commands/
Source : Internet
=====================================================================================================================================================How to Drop SCHEMA Objects in Oracle
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_obj.sql
select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by object_type,object_name
/
spool off;
set head on
@ target_schemaname_drop_obj.sql
exit
DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_other_obj.sql
select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/
spool off;
set head on
@target_schemaname_drop_other_obj.sql
exit
ls -lrth target_schemaname_drop*.sql
vi target_schemaname_drop_obj.sql / vi target_schemaname_drop_other_obj.sql
:wq
sqlplus "/as sysdba"
@target_schemaname_drop_obj.sql
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
exit.
=====================================================================================================================================================OEM:Daily / Weekly Backups not happening
Issue :
Daily incremental/ weekly full backups scheduled through OEM is not happening as scheduled.
Troubleshooting:
1) First check whether emagent is running or not on that database node?
a) ps -efa|grep agent
- confirms the agent is running.
b) cd /home/oracle/OEM_GRID/agent10g/bin/
./emctl status agent
- The output says agent is up and running fine properly.
2) Checked whether agent upload is happening or not?
./emctl upload agent
- ERROR.
May be the same thing is not allowing the backups to happen as scheduled.
Fix :
cd /home/oracle/OEM_GRID/agent10g/bin/
./emctl stop agent
./emctl clearstate agent
./emctl start agent
./emctl upload agent
After this, scheduled OEM jobs will run as expected.
TABLE_EXISTS_ACTION during IMPORT (IMPDP)
In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.
TABLE_EXISTS_ACTION can have following values.
1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.
3)TRUNCATE: This option deletes existing rows and then loads rows from the source.
4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.
- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.
- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.
If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.
- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.
- TRUNCATE cannot be used on clustered tables or over network links.
In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.
===========================================================================================================
How to Copy files from ASM to filesystem and filesystem to ASM
Copy files from ASM to filesystem
---------------------------------
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/EXPDP
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
ASMCMD> cp dumpfile1.dmp dumpfile1.dmp dumpfile1.dmp /oracle/backup/testdb/expdp
copying +FRA/TESTDB/EXPDP/dumpfile1.dmp -> /oracle/backup/testdb/expdp/dumpfile1.dmp
copying +FRA/TESTDB/EXPDP/dumpfile2.dmp -> /oracle/backup/testdb/expdp/dumpfile2.dmp
copying +FRA/TESTDB/EXPDP/dumpfile3.dmp -> /oracle/backup/testdb/expdp/dumpfile3.dmp
ASMCMD> exit
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
Copy files from filesystem to ASM
---------------------------------
$cd /oracle/backup/testdb/expdp/
$ls -lrt dumpfile*.dmp
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> lsdg
ASMCMD> cd FRA/TESTDB/IMPDP
ASMCMD> ls
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile1.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile1.dmp -> +FRA/TESTDB/EXPDP/dumpfile1.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile2.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile2.dmp -> +FRA/TESTDB/EXPDP/dumpfile2.dmp
ASMCMD> cp /oracle/backup/testdb/expdp/dumpfile3.dmp '+FRA/TESTDB/EXPDP/'
copying /oracle/backup/testdb/expdp/dumpfile3.dmp -> +FRA/TESTDB/EXPDP/dumpfile3.dmp
ASMCMD> ls
dumpfile1.dmp
dumpfile2.dmp
dumpfile3.dmp
==================================================================
Schema Refresh using ASM Diskgroups
How to create Datapump Export Dumps within ASM diskgroups
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
EXPORT (EXPDP)
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir EXPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/EXPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/expdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir expdp
$ chmod -R 755 expdp
$ cd expdp
$pwd
/oracle/backup/testdb/expdp
$ df -h /oracle/backup/testdb/expdp -- or
$df -gt /oracle/backup/testdb/expdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/expdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/expdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:schema_export_in_ASM.log
schemas=schema1,schema2
exclude=statistics
parallel=6
compression=all
cluster=n ---- 11g
:wq
$nohup expdp parfile=schema_export_in_ASM.par &
$tail -f nohup.out or
$tail -f schema_export_in_ASM.log
==================================================================================================================
scp dumpfiles from Source Server ASM to Destination Server ASM
Take schema backup and Drop Schema Objects
MPORT (IMPDP)
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
$ps -ef| grep pmon
$. oraenv
ORACLE_SID = [dbinst1] ? +ASM1
The Oracle base for ORACLE_HOME=/opt/app/11.2.0/grid is /opt/app/oracle
$asmcmd
ASMCMD> cd DATA01/TESTDB
ASMCMD> mkdir IMPDP
1. Create a directory in ASM.
SQL> create or replace directory DATAPUMP_ASM_DIR as '+DATA01/TESTDB/IMPDP';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_ASM_DIR TO SYSTEM;
2. Create a logfile directory in filesystem, since logfiles can't be stored in ASM.
SQL> create or replace directory DATAPUMP_LOG_DIR as '/oracle/backup/testdb/impdp';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY DATAPUMP_LOG_DIR TO SYSTEM;
3. Create logfile directory in filesystem.
$ cd /oracle/backup/testdb/
$ mkdir impdp
$ chmod -R 755 impdp
$ cd impdp
$pwd
/oracle/backup/testdb/impdp
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col DIRECTORY_PATH for a80;
select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------- ---------------- ---------------------------------------
SYS DATAPUMP_ASM_DIR +DATA01/testdb/impdp
SYS DATAPUMP_LOG_DIR /oracle/backup/testdb/impdp
Where DATAPUMP_ASM_DIR is a ASM based directory
and DATAPUMP_LOG_DIR is on a filesystem.
Here, Export is written to ASM based dictionary,
but log is written to another directory which is on filesystem.
We can create export dumps to the ASM diskgroups. But we can’t write expdp log to ASM (as it is a text file).
$vi import_schema_export_in_ASM.par
user_id="/ as sysdba"
directory=DATAPUMP_ASM_DIR
dumpfile=source_schemaname_expdp_%U.dmp
logfile=DATAPUMP_LOG_DIR:import_schema_export_in_ASM.log
schemas=schema1,schema2
parallel=6
cluster=n ---- 11g
If required,
REMAP_SCHEMA=source_schema1:destination_schema1,source_schema2:destination_schema2
REMAP_TABLESPACE=source_tablespace1:destination_tablespace1,source_tablespace2:destination_tablespace2
:wq
$nohup impdp parfile=import_schema_export_in_ASM.par &
$tail -f nohup.out
$tail -f import_schema_export_in_ASM.log
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
=====================================================================================================================================================
Network Level Import of Schema
How to take Schema Import at Network Level
The NETWORK_LINK parameter initiates a network import. This means that the impdp client initiates the import request, typically to the local database. That server contacts the remote source database referenced by the database link in the NETWORK_LINK parameter, retrieves the data, and writes it directly back to the target database. There are no dump files involved.
1. Create a TNS entry on the local database tnsnames.ora file for the remote database
Create a TNS entry on the local database tnsnames.ora file for the remote database and create a database link using the TNS entry. The user used in the database link must have read/write permission on the remote servers directory.
SOURCE_DATABASE_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remote-loc)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = remote-db)
)
)
2. Create the Database Link on the Destination database:
create database link SOURCE_DATABASE_LINK connect to SYSTEM identified by password using 'SOURCE_DATABASE_LINK';
3. Script to get database link information:
$ sqlplus "/as sysdba"
DBLINKS DETAILS
---------------
set pages 50000 lines 32767
col owner for a15
col username for a10
col host for a20
col created for a20
COL DB_LINK FORMAT A30
select owner, db_link, username, host, to_char(created,'dd-mon-yyyy hh24:mi:ss') CREATED from DBA_DB_LINKS order by owner, db_link;
OWNER DB_LINK USERNAME HOST CREATED
---------- ------------------------------ -------- ------- -------
4. PAR file.
$vi schema_network_level_import.par
user_id="/ as sysdba"
directory=DATA_PUMP_DIR
logfile=schema_network_level_import.log
schemas=schema1,schema2
NETWORK_LINK=source_database_link
FLASHBACK_TIME=SYSTIMESTAMP
exclude=statistics
parallel=6
cluster=n
:wq
$nohup impdp parfile=schema_network_level_import.par &
$tail -f nohup.out
$tail -f schema_network_level_import.log
Optional Parametrs:
------------------
ESTIMATE={BLOCKS | STATISTICS}
FLASHBACK_SCN=scn_number
FLASHBACK_TIME="TO_TIMESTAMP()"
The Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified.
The FLASHBACK_SCN parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide an SCN-consistent data extraction.
This parameter is valid only when the NETWORK_LINK parameter is also specified. This is because the value is passed to the source system to provide a time-consistent import.
FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive.
===================================================================================================================================================
BACKUP DATABASE LINK IN ORACLE
Database links backup
---------------------
All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;
For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;
======================================================================================================================================================
DDL
GENERAL USAGE
-------------
SET LONG 99999999
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;
Before droping user:(super Script---> collecting all grants of user)
--------------------------------------------------------------------
set long 100000
select dbms_metadata.get_ddl('USER','&username') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&username') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&username') FROM dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT','&username') from dual;
SET LONG 99999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
select dbms_metadata.get_ddl ('TABLE', '<TABLE_NAME>', '<SCHEMA_NAME>') from dual;
select dbms_metadata.get_ddl ('TABLE', '<SCHEMA_NAME>') from dual;
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;
select dbms_metadata.get_ddl('PROCEDURE',<PROCEDURE_NAME>','<OWNER_NAME>') from dual;
To get the create scripts of all tables of a particular schema
----------------------------------------------------------------
SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''<schema>'') from dual;' FROM DBA_TABLES
/
http://tech.padipa.net/generating-create-scripts-through-dbms_metadata-package
Posted by Dharmendra Chalasani at Tuesday, December 11, 2012
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: DDL
October 25, 2012
PACKAGE BACKUP in ORACLE
PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
spool off;
OR
set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_NAME' and type='PACKAGE' order by line;
PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;
OR
set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_BODY_NAME>';
spool off
OR
select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;
DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='<object_name>';
drop package <owner>.<package_name>;
spool Off;
===================================================================================================================================================
Backup / Delete - Archivelogs and RMAN backups
sqlplus "/as sysdba"
archive log list;
!df -h or !df -kgt
show parameter db_recovery;
NAME TYPE VALUE
---- ---- -----
db_recovery_file_dest string +FLASHDG
db_recovery_file_dest_size big integer 150G
if +FRA,
select * from V$FLASH_RECOVERY_AREA_USAGE;
(see what kind of files are available in the Flash Recovery Area)
set pages 9999 lines 300
col name format a40
select name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---- ----------- ---------- ----------------- ---------------
if SPACE_USED is full, then increase the db_recovery_file_dest_size
rman target /
crosscheck archivelog all;
delete expired archivelog all;
crosscheck backup;
SQL>alter system set db_recovery_file_dest_size=200G scope=both;
System altered.
rman target /
show all;
LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1'; ------List all archivelog backups for the past 24 hours
list backup of archivelog all completed before 'sysdate -1';
list backup of archivelog all;
list backup of archivelog all tag 'TAGXXX';
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
crosscheck archivelog all;
list expired archivelog ALL;
delete expired archivelog all;
delete obsolete device type disk;
crosscheck backup;
delete expired backup device type disk;
delete expired backupset device type disk;
ls -ltr archivebackup.cmd
nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
+FRA/+RECO01 filled with archivelogs
====================================
Backup archivelogs to disk
--------------------------
run
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to disk (specified location,if there is no space to take bakup at archive default location)
--------------------------------------------------------------------------------------------------------------
RUN
{
allocate channel ch1 type DISK;
allocate channel ch2 type DISK;
allocate channel ch3 type DISK;
allocate channel ch4 type DISK;
BACKUP ARCHIVELOG ALL format '/location/arch_%d_%p_%s.rman' FILESPERSET 10 DELETE INPUT;
}
Backup archivelogs to tape (PROD Environment)
--------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
+FRA filled with Bacupsets (PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP BACKUPSET COMPLETED BEFORE 'SYSDATE-7' DELETE INPUT;
}
if required,
BACKUP BACKUPSET ALL FILESPERSET 10 DELETE INPUT;
Delete BACKUPSET backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE NOPROMPT BACKUPSET COMPLETED BEFORE 'SYSDATE-14' DEVICE TYPE DISK;
+FRA filled with Bacupsets (NON-PROD Environment)
==========================
Move older backupset to tape (to keep more recent backups on disk and older backups only on tape)
-------------------------------------------------------------------------------------------------
run
{
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
DELETE BACKUPSET COMPLETED BEFORE 'SYSDATE-4';
}
Delete archivelogs backed up to tape/disk (PROD Environment)
-----------------------------------------
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
DELETE archivelog UNTIL TIME 'SYSDATE-7' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
Delete archivelogs backed up to tape/disk (NON-PROD Environment)
-----------------------------------------
DELETE archivelog all BACKED UP 1 TIMES TO DEVICE TYPE DISK;
list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
delete archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-7';
backup archivelog [all] [until time 'sysdate']| [sequence between 100 to 110 thread 1] as filesperset 5 delete
input;
backup archivelog from sequence 100 until sequence 110 thread 1 delete input;
backup archivelog from logseq 100 until logseq 110 thread 1 delete input;
report obsolete;
DELETE OBSOLETE REDUNDANCY = 3;
DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS;
http://anuj-singh.blogspot.in/2011_07_16_archive.html
oracledbazone.wordpress.com/2012/04/18/useful-rman-commands/
Source : Internet
=====================================================================================================================================================How to Drop SCHEMA Objects in Oracle
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
DROP SCHEMA OBJECTS
-------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_obj.sql
select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('&owner') and object_type not in ('DATABASE LINK','JOB','LOB') order by object_type,object_name
/
spool off;
set head on
@ target_schemaname_drop_obj.sql
exit
DROP OTHER OBJECTS (If required)
------------------
set head off
set pagesize 0
set linesize 300
spool target_schemaname_drop_other_obj.sql
select 'DROP '||decode(object_type, 'PACKAGE BODY', 'PACKAGE',object_type)|| ' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';')
from dba_objects where owner=upper('&owner') and object_type not in ('TABLE')
/
spool off;
set head on
@target_schemaname_drop_other_obj.sql
exit
ls -lrth target_schemaname_drop*.sql
vi target_schemaname_drop_obj.sql / vi target_schemaname_drop_other_obj.sql
:wq
sqlplus "/as sysdba"
@target_schemaname_drop_obj.sql
Schema Status
-------------
select username,account_status,default_tablespace from dba_users where username=upper('&username');
select owner,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where owner=upper('&owner') group by owner;
select owner,object_type,count(*) from dba_objects where owner=upper('&owner') and object_name not like 'BIN$%' group by object_type,owner order by object_type;
exit.
=====================================================================================================================================================OEM:Daily / Weekly Backups not happening
Issue :
Daily incremental/ weekly full backups scheduled through OEM is not happening as scheduled.
Troubleshooting:
1) First check whether emagent is running or not on that database node?
a) ps -efa|grep agent
- confirms the agent is running.
b) cd /home/oracle/OEM_GRID/agent10g/bin/
./emctl status agent
- The output says agent is up and running fine properly.
2) Checked whether agent upload is happening or not?
./emctl upload agent
- ERROR.
May be the same thing is not allowing the backups to happen as scheduled.
Fix :
cd /home/oracle/OEM_GRID/agent10g/bin/
./emctl stop agent
./emctl clearstate agent
./emctl start agent
./emctl upload agent
After this, scheduled OEM jobs will run as expected.
No comments:
Post a Comment