Querying Recovery Catalog:

Recovery Catalog Performance Views :–

 There are many views that provide backup-related information. The most commonly used ones are the following dynamic performance views in the target database to obtain information about your backups:

• V$BACKUP_SET: Backup sets created
• V$BACKUP_PIECE: Backup pieces that exist
• V$DATAFILE_COPY: Copies of data files on disk
• V$BACKUP_FILES: Information about all files created when creating backups

If you are using a recovery catalog, you can query corresponding views that contain the same information for each target database registered in the recovery catalog database. The corresponding views have the same name, except that the “V$” is replaced with “RC_”. Also, they are in the schema owned by the recovery catalog owner.

For example, the corresponding views in the recovery catalog, showing the information shown above are:

RC_BACKUP_SET,  RC_BACKUP_PIECE, RC_DATAFILE_COPY, and RC_BACKUP_FILES.

The following are brief descriptions of the most important recovery catalog views:

RC_STORED_SCRIPT: This view lists information about RMAN scripts stored in the recovery catalog.

RC_UNUSABLE_BACKUPFILE_DETAILS: This view shows the unusable backup files recorded in the recovery catalog.

RC_RMAN_STATUS: This view is similar to the V$RMAN_STATUS view and shows the status of all RMAN operations.  This view doesn’t contain information about any operations that are currently executing.

RC_RMAN_CONFIGURATION: This view provides information about persistent configuration settings.

RC_DATAFILE: This view shows all data files registered in the recovery catalog.

RC_DATABASE: This view shows the databases registered in the recovery catalog.

RC_ARCHIVED_LOG: This view provides historical information on both archived as well as unarchived redo logs.

In order to query the RC_BACKUP_FILES view, you must first execute the following in the recovery catalog database ( i.e. catdb):

[oracle@airyhost ~]$ . oraenv

ORACLE_SID = [ORCL] ? catdb

[oracle@airyhost ~]$ sqlplus / as sysdba

 SQL> CALL DBMS_RCVMAN.SETDATABASE(null,null,null,<dbid>);

SQL> CALL DBMS_RCVMAN.SETDATABASE(null,null,null, 1417825656);

 where <dbid> is the database ID of a target database (ORCL).

 SQL> connect cat/cat;

Connected.

SQL> desc RC_BACKUP_PIECE;

 Name                         Null?                     Type

 ----------------------------------------- -------- ----------------------------  

 DB_KEY                       NOT NULL                  NUMBER

 DB_ID                        NOT NULL                  NUMBER

 BP_KEY                       NOT NULL                  NUMBER

 RECID                        NOT NULL                  NUMBER

 STAMP                        NOT NULL                  NUMBER

 BS_KEY                       NOT NULL                  NUMBER

 SET_STAMP                    NOT NULL                  NUMBER

 SET_COUNT                    NOT NULL                  NUMBER

 BACKUP_TYPE                                            VARCHAR2(1)

 INCREMENTAL_LEVEL                                      NUMBER

 PIECE#                       NOT NULL                  NUMBER

 COPY#                        NOT NULL                  NUMBER

 DEVICE_TYPE                  NOT NULL                  VARCHAR2(255)

 HANDLE                       NOT NULL                  VARCHAR2(1024)

 COMMENTS                                               VARCHAR2(255)

 MEDIA                                                  VARCHAR2(80)

 MEDIA_POOL                                             NUMBER

 CONCUR                                                 VARCHAR2(3)

 TAG                                                    VARCHAR2(32)

 START_TIME                   NOT NULL                  DATE

 COMPLETION_TIME              NOT NULL                  DATE

 ELAPSED_SECONDS                                        NUMBER

 STATUS                       NOT NULL                 VARCHAR2(1)

 BYTES                                                  NUMBER

 IS_RECOVERY_DEST_FILE        NOT NULL                  VARCHAR2(3)

 RSR_KEY                                                NUMBER

 COMPRESSED                                             VARCHAR2(3)

 SITE_KEY                                               NUMBER

 ENCRYPTED                                              VARCHAR2(3)

 BACKED_BY_OSB                                          VARCHAR2(3)

Thank you for Reading…This is Airy… Enjoy 

Also Read Related Links:

1: What is RMAN Recovery Catalog

2: RMAN Base Recovery Catalog Creation 

#dbms_rcvman

Automatic Secure Configuration in Oracle 11g

 Automatic Secure Configuration:

When you create a new database, you can use Database Configuration Assistant (DBCA) to automatically create a more secure configuration than in previous releases of Oracle Database. You can enable the following secure configuration settings in one operation:

Password-specific settings in the default profile and Password complexity verification: 

Default profile feature enables you to enforce password expiration and other password policies.

Password complexity verification ensures that users set complex passwords when setting or resetting passwords. You can enforce password complexity by using the default settings provided by Oracle Database, or create custom requirements to further secure the password complexity requirements for your site.

Setting Profiles and Password Verify Function:

 Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn’t.

In Oracle Database 11g, the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g. At the end, the script has the following lines:

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 180

PASSWORD_GRACE_TIME 7

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 10

PASSWORD_LOCK_TIME 1

PASSWORD_VERIFY_FUNCTION verify_function_11G;

The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.

 

Thank you for reading… This is Airy…Enjoy

#password-management

Secure Password Support in Oracle 11g

Secure Password Support in Oracle 11g :

Oracle 11g secure passwords and implementing good password policies are by far the most important defense for protecting against password based security threats. Oracle recommends customers use passwords with at least 10 characters in length. In addition, the complexity of the password is critical. Passwords that are based on words are vulnerable to a “Dictionary attacks”. A complex password should contain:

	1:  At least 10 characters
	2:  A mixture of letters and numbers
	3:  Mixed case letters (Oracle Database 11g)
	4:  Include symbols (pre-Oracle Database 11g allows “_”, “$” and “#”)
	5:  Little or no relation to an actual word

Although there is no substitute for a strong, complex password, the following techniques could be used to generate longer passwords from a shorter, easier to remember password. Note that Oracle Database 11g supports mix-case passwords.

	1:  Create passwords from the 1st letters of the words of an easy-to-remember sentence: ‘I usually work until 6 almost every day of the week’ would become ‘Iuwu6aedotw’
	2:  Combine 2 weaker passwords like: “welcome1” and “tiger” into “WelTigerCome1”
	3: Repeat a character at the beginning or end of the password
	4:  Prepend or append a string of some sort
	5: Append PART of the same password
	6:  Double some or all of the letters: “welcome13” → “wwellCcooMmee13”

Using a random technique from this list can increase the work that an attacker must do before they can crack a password.

Oracle also recommends customers enforce password expiration and reuse policies using Oracle profiles and follow best practices defined by Oracle Applications. Oracle Database 11g made it easy to configure password policies during database install. For example, during install, customers can decide to limit the number of times a user can input incorrect passwords before getting his/her account temporarily deactivated. Oracle Database auditing can also be used to monitor account logins.

 Oracle Database now includes the following new password protections:

 1: Easy ability to find default passwords:  If you have upgraded from an earlier release of Oracle Database, you may have user accounts that still have default passwords. For greater security, you should find and change these passwords.

Finding User Accounts That Have Default Passwords:

When you create a database in Oracle Database 11g Release 1 (11.1), most of its default accounts are locked with the passwords expired. If you have upgraded from an earlier release of Oracle Database, you may have user accounts that have default passwords. These are default accounts that are created when you create a database, such as the HR, OE, and SCOTT accounts.

For greater security, change the passwords for these accounts. Using a default password that is commonly known can make your database vulnerable to attacks by intruders. To find both locked and unlocked accounts that use default passwords, log onto SQL*Plus using the SYSDBA privilege and then query the DBA_USERS_WITH_DEFPWD data dictionary view.

For example to find both the names of accounts that have default passwords and the status of the account:

SQL> CONNECT / AS SYSDBA

Enter password: password

SQL> select * from dba_users_with_defpwd;
And here is the output:

 

USERNAME

——————————

DIP

MDSYS

XS$NULL

SPATIAL_WFS_ADMIN_USR

CTXSYS

HR

OLAPSYS

OUTLN

OWBSYS

SPATIAL_CSW_ADMIN_USR

EXFSYS

ORACLE_OCM

SCOTT

DBSNMP

MDDATA

OE

ORDPLUGINS

ORDSYS

PM

SH

APPQOSSYS

BI

IX

ORDDATA

XDB

SI_INFORMTN_SCHEMA

WMSYS

27 rows selected.

You can see SCOTT listed above, because his password is TIGER, the default one. Change it with:

SQL> alter user scott identified by tiger1;

User altered.

Now if you check the view:

SQL> select * from dba_users_with_defpwd;

You won’t see SCOTT on the list anymore. It’s that simple!

 2: Enforced case sensitivity:

When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords, set the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the ALTER SYSTEM privilege can set the SEC_CASE_SENSITIVE_LOGON parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.

Procedure for Enabling Password Case Sensitivity:

To enable case sensitivity in passwords:

If you are using a password file, ensure that it was created with the IGNORECASE parameter set to N.

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=N

The IGNORECASE parameter overrides the SEC_CASE_SENSITIVE_LOGON parameter. By default, IGNORECASE is set to Y, which means that passwords are treated as case-insensitive. For more information about password files.

In Oracle Database prior to release 11g, user passwords were case insensitive. For example:

SQL> conn scott/tiger

Connected.

SQL> conn scott/TIGER

Connected.

This arrangement presents a problem for standards such as the Payment Card Industry (PCI) Data Security Standard, which require passwords to be case sensitive.

Problem solved; in Oracle Database 11g passwords can be case sensitive as well. While creating the database via DBCA, you will be prompted whether you want to upgrade to the “new security standards,” one of which is the case-sensitive password. If you accept, passwords will be recorded in the same case as they were created. Here is the resulting behavior, assuming you have accepted the new standard:

SQL> conn scott/tiger

Connected.

SQL> conn scott/TIGER

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

Note how “tiger” and “TIGER” are treated differently.

Now, some of your apps may not be passing the password in proper case right now. A typical example is a user input form: Many forms accept passwords with no case conversion being performed. However, with Oracle Database 11g, that login may fail unless the user enters the password in case-sensitive format or the developer changes the app to convert to upper or lower case (which may not be possible quickly).

If you wish, however, it is still possible to revert to case insensitivity by altering a system parameter,

SEC_CASE_SENSITIVE_LOGON, as shown in the example below.

SQL> conn / as sysdba

Connected.

Enter the following ALTER SYSTEM statement:

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL> conn scott/TIGER

Connected.

Finding the Password Versions of User Accounts:

 In previous releases of Oracle Database, passwords were not case sensitive. If you import user accounts from a previous release, for example, When you upgrade an existing Oracle 10g database to 11g, you can migrate your passwords to the new standard means Release 10g, into the current database release, the case-insensitive passwords in these accounts remain case insensitive until the user changes his or her password. If the account was granted SYSDBA or SYSOPER privilege, it is imported to the password file. When a password from a user account from the previous release is changed, it then becomes case sensitive.

You can find users who have case sensitive or case insensitive passwords by querying the DBA_USERS view. The PASSWORD_VERSIONS column in this view indicates the release in which the password was created. For example:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

SQL> select username, password, password_versions

from dba_users;

USERNAME        PASSWORD           PASSWORD

————– —————————

SYSTEM                       10G                            11G

SYS                                10G                            11G

MGMT                          10G                           11G

PAWAN                       10G                            11G

AMIT                            10G                            11G

RAVI                             10G                           11G

KAUSHIK                     10G

PAWANM                     11G

The passwords for accounts PAWAN, AMIT, and RAVI were originally created in Release 10g and then reset in Release 11g. Their passwords, assuming case sensitivity has been enabled, are now case sensitive, as is the password for KAUSHIK. However, the account for PAWANM is still using the Release 10g standard, so it is case insensitive. You should ask this user to reset his password so that it will be case sensitive, and therefore more secure.

Other thing you can notice that the password column is NULL, not populated with the hashed value as it is in Oracle Database 10g and prior versions. So what happened to the password? It’s still stored in the database (in the table USER$) but it is not visible in the DBA_USERS view. When the user is created as either global or externally authenticated, the status is indicated—GLOBAL or EXTERNAL—but the hash value of the password is not displayed.

Next, note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case sensitivity of the password. The value “10G 11G” signifies that the user was either created in 10g and migrated to 11g or created in 11g directly.

You can enforce, if you wish, the sensitivity of the SYSDBA password as well by entering a new parameter, ignorecase, while creating the password file as shown below:

$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
In the above example the SYSDBA password will be abc123, not ABC123 or any other variation in case.

The possibility of enforcing a case-sensitive password not only makes it more difficult to crack passwords by brute force, but also enables you to meet many more compliance requirements. Even more important, you can enforce the password requirement dynamically without needing a database shutdown, which comes in handy during upgrades and debugging login issues when upgrading legacy apps.

 

Thank you for reading… This is Airy…Enjoy

#password-management, #password-security