DDL: create statements

Since 9i, Oracle provides a package to get the DDL statements, such as for creating tablespace.

SQL> set long 50000
SQL> set heading off
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

In 11.2.0.3, following lines will get the statements for creating indexes and creating user:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off

SQL> select dbms_metadata.get_ddl('INDEX', index_name, 'OWNER_ID')
            from dba_indexes where owner = 'OWNER_ID' and table_name in (......) ;


SQL> select dbms_metadata.get_ddl('USER', 'OWNER_ID') from dual;


In 8i, you have to use Export to extract the same type of statement.

Grid Control: Status Pending

On one Host's homepage in Grid Control, it says "Status Pending" even Grid Control still monitors all databases on the host.

I ran agentca on the database host while the agent is up and running. After this action, the host's status is changed to "Up". If there are database and instance on the host but missing from Grid Control, it will also find and add them to Grid Control.

It seems it stopped the agent during running time, but I did not get notifications saying the agent is down. Here is the log:


/u01/oracle/product/agent10g/bin$./agentca -d

Stopping the agent using /u01/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc
Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u01/oracle/product/agent10g/cfgtoollogs/oui/configActions2009-02-03_09-14-00-AM.log

AUTHID current_user

An anonymous block works, but same code in a named block may not work, because roles used in named PL/SQL blocks and anonymous PL/SQL blocks act differently. The workaround is to define the procedure as "AUTHID current_user":

CREATE or REPLACE procedure test_proc
AUTHID CURRENT_USER as
......

Anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

If the user was granted all the necessary privileges directly (not through a role), it won't matter if roles are enabled or disabled, nor will you need "authid current_user".

You can check what system privileges are granted with roles currently enabled, then you can disable roles and see what privileges are different.
SQL> select * from session_privs;
SQL> set role none;
SQL> select * from session_privs;

The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.

-- check current enabled roles
SQL> SELECT * FROM session_roles;
-- turn off roles
SQL> set role none;
-- re-query:
SQL> SELECT * FROM session_roles;

Restore database from hot backups

There are different ways to specify the time point during incomplete recovery.

1. set until scn
set until scn 82189;
restore database;
recover database;

You can run a "preview" to find the highest SCN. Such as
RMAN> run {
2> set until time='sysdate-2';
3> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
4> restore database preview;
5> RELEASE CHANNEL ch00;
6> }

2. set until sequence
set until sequence = 58 thread 1;
restore database;
recover database;

You can use LIST to find checkpoint SCN of the backup you need:
RMAN> list backup of database completed between date_1 and date_2;
RMAN> list backup of database completed after date_2;

Then use LIST again to check the archivelogs needed for recovery have been backed up. It will report the sequence numbers:
RMAN> list backup of archivelog from scn=82189;

You can also run a query on the database to find the archivelog sequence number:
SQL> select * from V$LOG_HISTORY
where FIRST_CHANGE# < 82189 and NEXT_CHANGE# >=82189;

3. SET UNTIL TIME
SET UNTIL TIME "to_date('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')" ;
RESTORE DATABASE;
RECOVER DATABASE;

If there is only one backup available, I usually put the time to be the point right after RMAN finished backing up all data files and before RMAN was going to back up archive logs.


If you lost the control file, you can't run a SET UNTIL command if the controlfile isn't mounted. So, if you haven't made any structural changes to your database between the PITR and last autobackup, you can restore the control file fist (see Note 372996.1).

RMAN> set dbid=123456;
startup nomount;
set controlfile autobackup format for device type disk to 'diskPath/%F';
restore controlfile from autobackup until time "to_timestamp('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')";
startup mount;
run{ set until time "to_timestamp('30-JUL-2008:11:59:00', 'DD-MON-YYYY:HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}

Restore archivelogs to disk from ASM

During recovering a database, usually Oracle tells which archivelog is needed by specifying the sequence number, such as:
ORA-00279: change 39138275307 generated at 01/02/2009 00:12:07 needed for thread 1
ORA-00280: change 39138275307 for thread 1 is in sequence #78240

Here are the steps to get the necessary logs from the tape or from the ASM:

1. Make sure the status of archivelog is "AVAILABLE" on the tape, and the tape is also available on the tape drive.

RMAN> list backup of archivelog sequence 78240 thread 1;

2. Restore the archivelogs from the tape to the disk. But it may say the logs are already on the disk.

RMAN> run {
2> set archivelog destination to '/images';
3> ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
4> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
5> restore archivelog from logseq=78240 until logseq=78241;
6> RELEASE CHANNEL ch00;
7> RELEASE CHANNEL ch01;
8> }

executing command: SET ARCHIVELOG DESTINATION

allocated channel: ch00
channel ch00: sid=1017 instance=pdw1 devtype=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

allocated channel: ch01
channel ch01: sid=1003 instance=pdw1 devtype=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting restore at 12-JAN-09:06:50:05

archive log thread 1 sequence 78240 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215
archive log thread 1 sequence 78241 is already on disk as file +PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273
restore not done; all files readonly, offline, or already restored
Finished restore at 12-JAN-09:06:50:06

released channel: ch00
released channel: ch01

3. Use RMAN "COPY" command to copy the logs from ASM to disk. Make sure the disk folder is writable.

RMAN> connect target
connected to target database: PDB (DBID=1190101294)

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78240.11985.675049215' to '/images/archive_78240.arc';

Starting backup at 12-JAN-09:06:57:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=973 instance=pdb1 devtype=DISK
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78240 recid=3055 stamp=675049214
output filename=/images/archive_78240.arc recid=3088 stamp=675932248
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:57:29

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:57:29
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932297.12505.675932301 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:58:25

RMAN> copy archivelog '+PFRA/pdb/archivelog/2009_01_02/thread_1_seq_78241.14012.675049273' to '/images/archive_78241.arc';

Starting backup at 12-JAN-09:06:59:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=78241 recid=3056 stamp=675049272
output filename=/images/archive_78241.arc recid=3089 stamp=675932369
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02
Finished backup at 12-JAN-09:06:59:30

Starting Control File and SPFILE Autobackup at 12-JAN-09:06:59:30
piece handle=+PFRA/pdb/autobackup/2009_01_12/s_675932372.12504.675932373 comment=NONE
Finished Control File and SPFILE Autobackup at 12-JAN-09:06:59:34

RMAN> exit

That also shows a good way to copy files out from ASM.