Commands I Always Forget

This post is aimed at one location for maintaining commands I can't seem to keep straight and end up googling.  PSOUG and Morgan's library our great resources, but some times I just want a quick place to stop by and retrieve what I need.  As I run into these commands I'll update this page.

Dropping a database 11.2 Single Instance

 SQLPLUS:

 SQL> shutdown immediate

 SQL> startup restricted mount

 SQL> drop database;

RMAN:

> RMAN

RMAN>  CONNECT TARGET SYS@orcl

target database Password: password

connected to target database:  orcl (DBID=4920931111)

 RMAN > STARTUP FORCE MOUNT

 RMAN > SQL 'ALTER SYSTEM ENABLE RESTRICTED SESSION';

 RMAN> DROP DATABASE INCLUDING BACKUPS NOPROMPT;

Stop and Start Physical Standby 

SQLPLUS

Start 

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DISCONNECT FROM SESSION;

Stop

SQL> ALTER DATABASE RECOVER MANAGED STANDBY CANCEL; 

CRSCTL

crsctl start crs 

   start cluster 11.2 must be root or have sudo rights

crsctl stop crs

    stop cluster 11.2 must be root or have sudo rights

crsctl stat res -t

   displays the shorten form of the status (10.2 crs_stat)  

crsctl enable crs

     enable clusterware daemons to auto start

crsctl disable crs

      disables automatic start of clusterware daemons  

crsctl add css votedisk path

     add a vote disk

crsctl delete css votedisk path

     remove voting disk

crsctl check crs

    status of the crs  

 crsctl check cssd

crsctl check crsd

crsctl check evmd

crsctl check cluster

crsctl query crs softwareversion

crsctl query crs activeversion

Check Inventory

./opatch lsinventory

    ** view inventory using Opatch

cat /etc/oraInst.loc|grep inventory_loc 

     ** to find the location of the inventory

cat /opt/app/oraInventory/ContentsXML/inventory.xml

     ** View inventory without using Opatch

Skipping Users In Logical Standby

Logical standby's have their place for reporting requirements. They do require their own special attention. Logical standby's are generally used to fulfill a reporting requirement which would only cause performance issues in the production instance. However, not everything needs to be replicated to the logical standby. Individual users who have access to production environments and can create objects are one type of events that can be skipped. If they are creating objects and data with a requirement to replicate, that's a whole other issue. When we create a user in a production environment it will be replicated to the logical standby, however, we do not want their objects or the data to be replicated from production to the logical standby. So we issue the following with each user create:  
ALTER database stop logical standby apply; 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt =>'DML',schema_name => 'NewUser',object_name => '%'); 
EXECUTE DBMS_LOGSTDBY.SKIP(stmt =>'SCHEMA_DDL',schema_name => 'NewUser',object_name => '%');
 alter database start logical standby apply immediate;
 **Note Substitute the actual user for NewUser.

The key is to remember to do this when each user is created. Or have an automated process that will trigger these command on the logical standby.

RMAN register several archivelogs

From time to time physical standbys get out of sync. The apply process stops working, and the gap fetch doesn't seem to fetch the missing archive logs from the primary. So a manual move of the archives to the primary has to occur -- unless the gap is too large then it would be easier to just perform an incremental backup, but that's another post. Once the archives have been moved to the appropriate directory on the standby, they have to be registered.

 >ALTER DATABASE REGISTER LOGFILE '/home/oracle/archives/orcl/1_19526_676899244.arc';

This will be displayed in the alert log and Media Recovery will apply the log. The messages will look like:
 alter database register logfile '/home/oracle/archives/orcl/1_19526_676899244.arc' 

There are 1 logfiles specified. 

ALTER DATABASE REGISTER [PHYSICAL] LOGFILE Completed: 
alter database register logfile '/home/oracle/archives/orcl/1_19526_676899244.arc'

The process works great when dealing with only a handful of archive logs. There is a better way to register an entire directory of logs with RMAN.
1. rman target / nocatalog -- log in to rman which would be appropriate to your environment.
2. CATALOG START WITH '/home/oracle/archives/orcl -- provide the directory that contains the archive logs to register. ** Be Careful if you have an archive log from a different incarnation this could be problematic as it will be registered and applied.
3. RMAN will then review the directory and any files not registered will be listed to register and you will be prompted:  

Do you really want to catalog the above files (enter YES or NO)? 4. YES -- If the listing looks appropriate.

Oracle will then register the archive logs and report any that could not be registered. Any files that are not archive logs will not be registered and will be listed as corrupted. Once finished the Media Recovery will begin picking up the archive logs and applying.

Determine Queries Temporary Space Requirement

If you are ever in the situation where someone doesn't want to add disk space to the temporary tablespace without having the specifics as to exactly how much space is required. There are two events that can be set that will provide this information.

The key is that you will actually need to run the query in question in order to get all the sort information required.

alter session set events '10032 trace name context forever';
alter session set events '10033 trace name context forever';

10032 will provide all the sort information to include the total amount of records input and output as well as the total amount of space required.:


10033 will provide the details of the sort run to include the block address and the number of blocks. At the end it will total the blocks used for all the sorts. This total number will be the amount of disk space required for this query with this data set.

Both of these traces express the disk space required in blocks, so make sure you multiple that number by the block size of the database.