Identify master node in RAC

1. Check the ocssd.log file.

db1p:$CRS_HOME/log/db1p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 1, master node number 2

or

db2p:$CRS_HOME/log/db2p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 2, master node number 2

2. "ocrconfig -showbackup" show you the last backup on master node.

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;