Tuesday, November 25, 2014

How to configure Oracle RMAN backup for the first time


Oracle 11g Logo
RMAN is a oracle utility to backup, restore & recovery of database.
The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)
Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.
catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.
Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”
Note: Create catalog/repository database with the help of DBCA.
Lets consider following Step by Step syntax to do so:
Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 3 11:28:24 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
Database is in NOARCHIVELOG mode.
Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step # 4: Startup the database in mount state.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.
Step # 5: Configure database in ARCHIVELOG mode.
SQL> alter database archivelog;
Database altered.
Step # 6: Alter database to open state.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
Step # 7: Ensure ARCHIVELOG destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
In case you wish to change default archive log destination then issue the following command.
SQL> alter system set log_archive_dest_1='location=/home/oracle/arch' scope=both;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
Step # 8: Ensure the flash/fast recovery area location.
SQL> show parameter db_recovery_file_dest
Step # 9: Connect to RMAN prompt with target database.
[oracle@centos ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 3 11:46:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
RMAN>
Step # 10: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.
RMAN> configure controlfile autobackup on;
Step # 11: To enable backup optimization run the following command, by default backup optimization has been configured OFF.
RMAN> configure backup optimization on;
Step # 12: Configure retention policy for backup.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Step # 13: Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects.
[oracle@centos ~]$ sqlplus "/ as sysdba"
SQL> select global_name from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
CATALOGD
SQL> create tablespace catalogtbs datafile '/home/oracle/dbfile/catalogtbs1.dbf' size 100M autoextend on maxsize unlimited;
Tablespace created.
Step # 14: Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.
SQL> create user recoveryman identified by recoveryman;
User created.
SQL> alter user recoveryman default tablespace catalogtbs temporary tablespace temp;
User altered.
SQL> grant recovery_catalog_owner to recoveryman;
Grant succeeded.
SQL> grant connect,resource to recoveryman;
Grant succeeded.
Step # 15: Connect to RMAN on target and recovery catalog database.
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 4 14:30:28 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1363580714)
connected to recovery catalog database
Step # 16: create catalog by issuing the following command in RMAN prompt.
RMAN> create catalog;
recovery catalog created
Step # 17: After creating catalog, Ensure RMAN repository tables by logging into repository database as RMAN user. This is only for the first time.
[oracle@oracle ~]$ sqlplus "recoveryman/recoveryman@catalogdb"
SQL> show user;
USER is "RECOVERYMAN"
SQL> select table_name from user_tables;
Step # 18: Register database with recovery catalog.
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Step # 19: Check whether registration was successful.
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    670      SYSTEM               YES     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    490      SYSAUX               NO      /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    30       UNDOTBS1             YES     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    5        USERS                NO      /home/oracle/app/oracle/oradata/orcl/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace  Maxsize(MB) Tempfile Name
---- -------- ----------- ---------   -------------------------------
1    20       TEMP        32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
OR
RMAN> LIST INCARNATION OF DATABASE;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS Reset SCN    Reset Time
------- ------- -------- ---------------- ------------ ---    --------
89      102     ORCL     1363580714       PARENT       1      15-AUG-09
89      90      ORCL     1363580714       CURRENT      945184 02-JAN-14
Target database is registered with the RMAN.
Now you can backup your target(registered) database as per your convenience.
***********************************************************************
Posted by at 1 comment:

Monday, November 24, 2014

hotbackup script

#!/bin/bash

.  /u01/PROD/oraprod/db/tech_st/11.2.0/PROD_abr-ln-orc2.env
NOWDATE=`date +%d%b%y`
sqlplus '/ as sysdba' <
alter system switch logfile;

alter database begin backup;

exit;
eof

cd /u01/PROD/oraprod/db/

tar cvf - apps_st | gzip -c > /u01/PROD/oraprod/db/prod_data_top_hot_$NOWDATE.tgz

sqlplus '/ as sysdba' <
alter database end backup;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE as '/u01/PROD/oraprod/db/$NOWDATE.ctl';

exit;
eof
~

Posted by at 1 comment:

Friday, November 21, 2014

How to set DB Schema password to 'never expire'


As a Hyperion admin, I have time and again run into this issue with my hyperion schemas where the password sometimes expires and wreaks havoc on the application.

The key indicator for these error messages are the log files. They are quite descriptive .

Basic test to make sure that the schema is working correctly will be to login to the DB hosts and issue this command:

conn username/password;

To resolve this issue ,

connect to your DB as sysdba and run

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


Restart all Services and ensure all the log files under diagnostics\logs\services directory startup properly.

This should resolve the issue.
Posted by at 3 comments:

Thursday, November 20, 2014

Hyperion Financial Management Error "Bad Gateway. Request could not be processed. Invalid response received by proxy or gateway server" When Browsing Content Within an Application

Posted by at 1 comment:

Tuesday, November 11, 2014

SSL-Enable Oracle Database

 SSL-Enable Oracle Database

Take these steps to SSL-enable Oracle database:
  1. Create a root CA and a certificate for the DB. Here is an example:
    Note:
    Self-signed certificates are not recommended for production use. For information about obtain production wallets, see Section 8.4.8.3, "Changing a Self-Signed Wallet to a Third-Party Wallet.".
    mkdir root
mkdir server
 
# Create root wallet, add self-signed certificate and export
orapki wallet create -wallet ./root -pwd password
orapki wallet add -wallet ./root -dn CN=root_test,C=US -keysize 2048 -self_signed -validity 3650 -pwd password
orapki wallet display -wallet ./root -pwd password
orapki wallet export -wallet ./root -dn CN=root_test,C=US -cert ./root/b64certificate.txt -pwd password
 
#Create server wallet, add self-signed certificate and export
orapki wallet create -wallet ./server -pwd password
orapki wallet add -wallet ./server -dn CN=server_test,C=US -keysize 2048 -pwd password
orapki wallet display -wallet ./server -pwd password
orapki wallet export -wallet ./server -dn CN=server_test,C=US -request ./server/creq.txt -pwd password
 
# Import trusted certificates
orapki cert create -wallet ./root -request ./server/creq.txt -cert ./server/cert.txt -validity 3650 -pwd password
orapki cert display -cert ./server/cert.txt -complete
orapki wallet add -wallet ./server -trusted_cert -cert ./root/b64certificate.txt -pwd password
orapki wallet add -wallet ./server -user_cert -cert ./server/cert.txt -pwd password
orapki wallet create -wallet ./server -auto_login -pwd password}}
  2. Update listener.ora, sqlnet.ora, and tnsnames.ora for the database.
    1. This example shows the default listener.ora:
      SID_LIST_LISTENER =
(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /path_to_O_H)(PROGRAM = extproc)))
LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = mynode.mycorp.com)(PORT = 2490))
))
 
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/wallet_location)))
 
SSL_CLIENT_AUTHENTICATION=FALSE}}
      And here is an updated listener.ora file, illustrating a scenario with no client authentication:
      SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dbname)
      (ORACLE_HOME = /path_to_O_H)
      (SID_NAME = sid)
    )
  )
 
SSL_CLIENT_AUTHENTICATION = FALSE
 
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /wallet_path)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mycorp.com)(PORT = 2490))
    )
  )
      Note that the SSL port has been added.
    2. Likewise, a modified sqlnet.ora file may look like this:
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS,NTS)
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/directory)))
SSL_CLIENT_AUTHENTICATION=FALSE
    3. A modified tnsnames.ora file may look like this:
      OID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mynode.mycorp.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mynode.mycorp.com)
    )
  )
   
SSL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mynode.mycorp.com)(PORT = 2490))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mynode.mycorp.com)
    )
    (SECURITY=(SSL_SERVER_CERT_DN=\"CN=server_test,C=US\"))
  )
  3. Test the connection to the database using the new connect string. For example:
    $ tnsping ssl
$ sqlplus username/password@ssl
Posted by at 1 comment:
Subscribe to: Posts (Atom)