Misc/unfiled

Here are some useful sql and tips, gathered from various sources…

Cloning a Database

Please note:

You can only clone databases where the source and destination databases are at the same Oracle version, ie: you cannot clone an Oracle 8i database (8.1.7.3) to Oracle 9i (9.2.0.4).

This technique was taken from the UK Oracle User Group Journal

Some advantages of this technique over export/import are as follows : Import requires much more internal processing and I/O to create the new database. In one case it takes a half hour to clone the database with this technique and it takes close to 18 hours to import the database and then fix all missing items (with quite a bit of manual effort involved.) Import can lose grants on views that are created using packaged functions. Any object owned by SYS and every grant performed by SYS must be recreated during the new database creation otherwise the objects and/or grants will not be there after the import.

The old database is down for less than an hour (usually) in order to get a consistent copy of the database. These instructions can be used to copy an Oracle database named LIVE to a database named TEST. These instructions are specifically designed for UNIX, however the concepts involved translate to other platforms as well.

The basic procedure here is to take a trace dump of the control file from database LIVE. This dump is an SQL script that is ordinarily used to restart the database when the control file is corrupted. In this case we will use this script to start database TEST with copies of all the files from database LIVE. Starting this database will remove references to LIVE from within all the database files, log files and control files. After the database starts it will be exactly the as if it was created from scratch and loaded with the information from LIVE.

These are the steps to follow :

· Create the file hierarchy for the new database.

· Create the initTEST.ora file.

· All users exit the LIVE database and shutdown all processes using it.

· Create the backup trace file of LIVE and then shut it down :
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> alter database backup controlfile to trace ;
SQL> shutdown immediate;
SQL> exit;

· Verify that the LIVE database has come down cleanly.

· Copy the trace file from the LIVE user dump directory to and call it crTEST.sql

· Copy the database files and online redo-logs to the new location with cp . Don’t copy control files or archived log files. If the original database files are raw partitions then use dd.

· Edit crTEST.sql to set the database as TEST and to reset the log files when it opens :

Remove all of the comment lines throughout the file that start with a #

Change the line :
CREATE CONTROLFILE REUSE DATABASE “LIVE” NORESETLOGS NOARCHIVELOG
to :
CREATE CONTROLFILE SET DATABASE “TEST” RESETLOGS NOARCHIVELOG

· Change all datafile and logfile names from LIVE to TEST where the files were copied to.
Change the line :
ALTER DATABASE OPEN;
to :
ALTER DATABASE OPEN RESETLOGS ;

Delete the line :
RECOVER DATABASE

· Set the environment for the TEST database.

· Log into sqlplus to start the new TEST database as follows:
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> @crTEST.sql
SQL> alter database rename global_name to TEST ;
SQL> shutdown immediate;
SQL> startup;
SQL> exit ;

· Set the environment for the LIVE database.

· Restart the LIVE database.

· Verify that both databases start normally. Take full backups of both databases.

DBA_JOBS

This SQL will pull the DBA_JOBS table in an easy to read format.

SET LINESIZE 152
SET PAGESIZE 100COLUMN JOB         FORMAT 99,999 HEADING "Job #"
COLUMN schema_user FORMAT a12    HEADING "Job Owner"
COLUMN log_user    FORMAT a12    HEADING "Job Exec"
COLUMN broken      FORMAT a3     HEADING "BKN"
COLUMN last        FORMAT a17    HEADING "Last Run"
COLUMN this        FORMAT a17    HEADING "This Run"
COLUMN next        FORMAT a17    HEADING "Next Run"
COLUMN time        FORMAT 99,999 HEADING "Time Mins"
COLUMN what        FORMAT a40    HEADING "Job Action"

select job,       schema_user,
       log_user,
       decode(broken,'Y','Yes','N','No','---') "broken",
       to_char(LAST_DATE,'DD-MON-YYYY HH24:MI') "last",
       to_char(THIS_DATE,'DD-MON-YYYY HH24:MI') "this",
       to_char(NEXT_DATE,'DD-MON-YYYY HH24:MI') "next",
       ROUND(TOTAL_TIME/60,0) "time",
       substr(what,1,40) "what"
from dba_jobs
where substr(what,1,7) !='declare'
union all
select job,
       schema_user,
       log_user,
       decode(broken,'Y','Yes','N','No','---') "broken",
       to_char(LAST_DATE,'DD-MON-YYYY HH24:MI') "last",
       to_char(THIS_DATE,'DD-MON-YYYY HH24:MI') "this",
       to_char(NEXT_DATE,'DD-MON-YYYY HH24:MI') "next",
       ROUND(TOTAL_TIME/60,0) "time",
       substr(what,instr(what,'MSG'),20) "what"
from dba_jobs
where substr(what,1,7) ='declare'
order by 1
/

Profiles

column username format a8
column profile format a15
column resource_name format a21
column limit format a7
select u.username, p.*
from dba_profiles p, dba_users u
where p.profile = u.profile
and u.username = 'RB'
and p.RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS'
/

XML Invalid

select status, version, comp_name from dba_registry where comp_id='XDB';

VALID       9.2.0.4.0                      Oracle XML Database

sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/catnoqm
conn / as sysdba
drop user anonymous;
drop role XDBadmin;
exit

sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/catqm xdb tools temp
conn / as sysdba
@?/rdbms/admin/catxdbj
exit