Steps to create Amazon Web Service(AWS) cloud Free Tier account

Steps to Create Amazon Web Services (AWS) Free Tier Account

Amazon Web Services AWS is an Amazon Cloud Service which provideĀ  different infrastructure, database and application as a service.

AWS Free Tier is the Service which used to learn the Amazon Cloud. It help to practice understand and learn than amazon web services

Following are the Steps to create an AWS Free Tier Account

1. Open the Google web page and type AWS free tier on Search engine.

2. Go to the following link directly or by google in first step.
https://aws.amazon.com/free/

CreateFreeAWSaccount.PNG

3. Page also provide the free service information when you scroll down the web page.

DetailofFreeAWSaccount

4. For Creating free account, Click on Create a free account button.

Creatfreeaccount.PNG

5. Provide the Email address password and Name on following form.

ProvidedetailforAWSaccount

6. AWS Site ask for more contact information as shown below.

ContactInformation

7. After Personal information you will ask for the Credit card detail.
Note: Only 1 dollar or 2 Rupees is deducted from you account for verification purpose for genuine user and that money is credit back in 3 days.

CreditCarddetail

8. After submit you will come to the Dashboard Page as below.

Dashboard

9. You will received mail about your account status. That its activate or not.

10. If you tried to open the EC2 page following message display as shown below:

Complete7.png

11. Then you can see two link as follows:
Complete Your AWS registration
Check weather your AWS registration or Mobile No is registered or registered it. For Registration you will get call and pin number to verified it.
Credit Card Information
Check the Credit card detail it is correct otherwise check with your Credit card bank why transaction is deny or try with another card.

Advertisements

Types of Oracle Database Objects

Types of Oracle Database Objects

Oracle Database objects are with consider or present in schema and not present in schema as follows. divided into two catagory

Schema objects
Non-Schema objects

Schema Objects
A schema is a logical structures of data or schema objects. A schema has same name as user. Each user map with a single schema.

Schema has following types of objects:

Clusters
Constraints
Database links
Database triggers
Dimensions
External procedure libraries
Index-organized tables
Indexes
Indextypes
Java classes, Java resources, Java sources
Materialized views
Materialized view logs
Mining models
Object tables
Object types
Object views
Operators
Packages
Sequences
Stored functions, stored procedures
Synonyms
Tables
Views

Nonschema Objects
Objects present in database but are not present in a schema. Handled by SYS or SYSTEM schema only:

Following are the list of Nonschema objects

Contexts
Directories
Editions
Restore points
Roles
Rollback segments
Tablespaces
Users

Update Multiple PDBs in a Single DML Statement

Update Multiple PDBs in a Single DML Statement of Application container

With DML Query you can also update the multiple PDBs if having same tables and columns or same database.

Example:
Set the country_name column to the value USA in the sh.sales table.
This table exists in two separate PDBs, with container IDs of 7 and 8.
Both PDBs are in the application container named sales_ac. You can connect to the application root as an administrator, and make the update as follows:

CONNECT sales_admin@sales_ac
Password: *******
UPDATE CONTAINERS(sh.sales) sal
SET sal.country_name = 'USA'
WHERE sal.CON_ID IN (7,8);

Example:
Update the column value city_name to DELHI of a table customers present in sales schema of two PDB having con id 7 and 8 in One DML statements.

UPDATE CONTAINERS(sales.customers) ctab
SET ctab.city_name='DELHI'
WHERE ctab.CON_ID IN(7,8) AND
CUSTOMER_ID=3425;

Blocked Connection in listener

Blocked Connection in listener

Blocked connection occurred when you listener is not registered with database, or when a database is in restricted mode and shutdown of the database is in progress.
If a database is in restricted mode, then LREG Backgroud process block all connections to the instance.
Clients are getting one of the following errors:

ORA-12526: TNS:listener: all appropriate instances are in restricted mode
ORA-12527: TNS:listener: all appropriate instances are in restricted mode or blocking new connections
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The ORA-12528 error occurs when a database instance is not yet registered with the listener.

Solution 1
1. If the database service registration is not listed, then enter the following SQL command:

--1. Check the service is registered with listener if not follow the next step.
lsnrclt services

--2. Connect with the database
sqlplus sys as sysdba

--3. Fire following command to register the Listener with Database
SQL> ALTER SYSTEM REGISTER;

You can set the listener parameter for register the Server:

-- Mention listener name
ALTER SYSTEM SET LOCAL_LISTENER=listener_sales1;

--In RAC you can also set remote listener
ALTER SYSTEM SET REMOTE_LISTENER=listener_sales2;

cmctl: is used for register the listener on linux or unix system.

CMCTL> ADMINISTER [instance_name]

Solution 2
Check database is not present in mount, nomount & restricted state if not then start the database in open state:

Startup

Rebuild the global or local partition index in Oracle

Rebuild the global or local index in Oracle

Local and global index present in partition table of the oracle. Index is created on basis of partition is local and index created on whole table is global index.
You cannot rebuild the local index which having partition as normal. If you tried to do you are getting the following error:

SQL> alter index sales_IDX rebuild;
alter index sales_IDX rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

Rebuild partition index by specifying partition name.

ALTER INDEX sales_IDX REBUILD PARTITION sales_Q4 TABLESPACE users;

Rebuild Global index as normal

ALTER INDEX sales_ids REBUILD;

Check the index is local or global:

select INDEX_NAME,TABLE_NAME,INDEX_type ,GLOBAL_STATS from dba_indexes where table_name = 'EMP';

Check the partition index

select index_name, partition_name,status from dba_ind_partitions;

Rebuild the partition local index with script

-- Offline rebuild
SET LINESIZE 5000;
SET PAGESIZE 5000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from dba_IND_PARTITIONS where INDEX_OWNER='HR';

-- Online rebuild
SET LINESIZE 5000;
SET PAGESIZE 5000;
SET LONG 50000;
SET TRIMSPOOL ON
SET WRAP ON
set termout off
set verify off
set longchunksize 200000
set feedback off
SET HEADING Off
set echo off
Select 'ALTER INDEX '|| index_name ||' rebuild partition ' || PARTITION_NAME ||' online ;' from dba_IND_PARTITIONS where INDEX_OWNER='HR';

Fine Grained Auditing in Oracle

Fine Grained Auditing in Oracle

Fine Grained auditing is used to trace the table object with insert, update & delete operation. Without enable the System parameter AUDIT_TRAIL.
Fine grained auditing records are stored in SYS.FGA_LOG$ table and are accessible through the DBA_FGA_AUDIT_TRAIL view.

Note: DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

Fine Grained policy defined on the data access conditions from the objects in database.
Example you want to audit record if some one fetch the records of employee having salary greater than 5 lacs. In this you can add policy on employee table with column salary and condition > 5 lacs.

Create or ADD the Fine Grained Policy for Audit
In example, we monitor salary column of the hr.emp table of all insert,update,delete or select statements for department sales.

DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name => 'emp',
policy_name => 'finegrained_hr_emp_check',
audit_condition => 'dept = ''SALES'' ',
audit_column => 'salary'
statement_types => 'insert,update,delete,select');

Note: If you execute the sql statement whichh uses salary column and in where condition dept = sales then it will generate audit.
Following are example of generate audit:

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;
SELECT salary FROM hr.emp WHERE dept = 'SALES';
DELETE from hr.emp where salary >1000000

Check the Audit record

select * from DBA_FGA_AUDIT_TRAIL;

select * from SYS.FGA_LOG$;

Example for DBMS_FGA.ADD_POLICY with all parameters

DBMS_FGA.ADD_POLICY(
object_schema => 'scott', --schema of the object which audit
object_name=>'emp', --object name which audit
policy_name => 'mypolicy1', --define policy name for audit
audit_condition => 'sal 'comm, credit_card, expirn_date', --define column which audit
handler_schema => NULL, --schema that contains the event handler.
handler_module => NULL, -- schema that contains the event handler. Include the package the event handler is in.
enable => TRUE, -- means policy need to be enabled
statement_types=> 'INSERT, UPDATE', --Statement which need to be audit Insert,update,delete,select
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, -- includes SQL Text and SQL Bind.
audit_column_opts => DBMS_FGA.ALL_COLUMNS --audit all such columns are referenced
);

Enable the Policy

DBMS_FGA.ENABLE_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
enable BOOLEAN);

Disable the Policy

DBMS_FGA.DISABLE_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );

Drop the policy

DBMS_FGA.DROP_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 );

Create lost control file in Oracle

Create lost control file in Oracle

It’s seems having error in starting the database. Some problem seems to be found in Control file during startup of database.
Database is started upto no mount stage but during mount it will generate errors: (Both errors are of different Server:)

ORA-00600: internal error code, arguments: [2131], [9], [8], [], [], [], [], [], [], [], [], []

ORA-00205: error in identifying control file, check alert log for more info

Solution
We decided to create an new control file for fixing the errors getting in startup the database.
Note: Before creating new control file take backup of oradata folder. It may caused problem in datafiles.

Following are the steps to Create New Control File

Sample of Trace of control file :
Control File

1. If you have trace file generated then its good otherwise edit the following create control file command:
Note: Edit the control file command with option database Name, redo files , datafiles location etc.

--If Database is shutdown normal and you have redo logs files then use noresetlogs option
CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

OR
--If you donot have redologs then use following command:
CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

2. Rename the already existing control file and Start the database in No mount state.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 759169968 bytes
Database Buffers 301989888 bytes
Redo Buffers 5517312 bytes

3. Use the above edit control file command for create new control file.

SQL> CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_DX8Z3980_.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'D:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_DX8Z3C51_.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
;

Control file created.

4. If you have trace file of control then you found following statement configure RMAN setting.
Note: Skip this step if donot have control file trace.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 2');

PL/SQL procedure successfully completed.

SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');

PL/SQL procedure successfully completed.

5. Recover database is required if database is not shut-down normal.

-- if the last shut-down was not normal or immediate.
RECOVER DATABASE
OR
-- if the last shut-down was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

6. Open the database normally or resetlog depend upon the Redo log file you lost or have it.

-- Database can now be opened normally. (If redo log file is good)
ALTER DATABASE OPEN;

-- Database can now be opened zeroing the online logs.( if redo log file is not good.)
ALTER DATABASE OPEN RESETLOGS;

7. Create temp file as needed.

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;