All about ASM
Add,drop and rebalance ASM diskgroups
Adding Disks to a Disk Group
ALTER DISKGROUP DATA_DG ADD DISK '/devices/DiskA1’,’/device/DiskB1';
Dropping Disks and Disk Groups
ALTER DISKGROUP DATA_DG DROP DISK DiskA1;
DROP DISKGROUP DATA_DG INCLUDING CONTENTS;
Note:
· DROP DISKGROUP statements requires the instance to be in MOUNT state.
Rebalance Disk Group
· ASM rebalance a disk group automatically, whenever we add or remove disks form disk group.
· Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement.
· If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used.
Example :
SQL> ALTER DISKGROUP DATA_DG REBALANCE POWER 5; [By Default is 1]
SQL> select * from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
1 REBAL WAIT 5 0 0 0
Thursday, January 19, 2017
Permission access denied in APEX with companion
Log file detail :-
ORA-1017 ORA-01017: invalid username/password; logon denied\n
cd $ORACLE_HOME/Apache/modplsql/conf
$ vim dads.conf
Alias /i/ "/u02/app/oracle/product/10.2.0/ias/Apache/Apache/images/"
AddType text/xml xbl
AddType text/x-component htc
<Location /pls/apex>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDatabasePassword oracle
PlsqlDatabaseConnectString 192.168.1.20:1521:apex
PlsqlAuthenticationMode Basic
PlsqlDefaultPage apex
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
</Location>
Change the password in dads.conf file and in database level
Solution:-
SQL >ALTER USER APEX_PUBLIC_USER IDENTIFIED BY oracle;
ORA-1017 ORA-01017: invalid username/password; logon denied\n
$ vim dads.conf
Alias /i/ "/u02/app/oracle/product/10.2.0/ias/Apache/Apache/images/"
AddType text/xml xbl
AddType text/x-component htc
<Location /pls/apex>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDatabasePassword oracle
PlsqlDatabaseConnectString 192.168.1.20:1521:apex
PlsqlAuthenticationMode Basic
PlsqlDefaultPage apex
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
</Location>
Change the password in dads.conf file and in database level
Solution:-
SQL >ALTER USER APEX_PUBLIC_USER IDENTIFIED BY oracle;
Wednesday, January 18, 2017
Steps to Backup And Restore ASM Metadata
Steps to Backup And Restore ASM Metadata
--------------------------------------------------------------
Set the environment to ASM using .oraenv
Issue asmcmd to start the ASM client
The md_backup command makes a copy of the metadata for one or more disk groups. The summary usage is shown below.
md_backup [-b location_of_backup] [-g dgname [-g dgname …]]
An example of the command is shown below. The resulting file contains all the metadata needed to recreate the ASM setup.
ASMCMD [+] > md_backup -b /tmp/backup.txt -g data
Disk group to be backed up: DATA
ASMCMD [+] >
RESTORE METADATA
The md_restore command allows a restore of a disk group from the metadata created by the md_backup command. It also allows a certain amount of manipulation of the final disk groups during the restore. The summary usage is shown below.
md_restore -b <backup_file> [-li]
[-t (full)|nodg|newdg] [-f <sql_script_file>]
[-g '<diskgroup_name>,<diskgroup_name>,...']
[-o '<old_diskgroup_name>:<new_diskgroup_name>,...']
A straight restore of the backup taken previously is shown below.
ASMCMD [+] > md_restore -b /tmp/backup.txt -t full -g data
--------------------------------------------------------------
Set the environment to ASM using .oraenv
Issue asmcmd to start the ASM client
The md_backup command makes a copy of the metadata for one or more disk groups. The summary usage is shown below.
md_backup [-b location_of_backup] [-g dgname [-g dgname …]]
An example of the command is shown below. The resulting file contains all the metadata needed to recreate the ASM setup.
ASMCMD [+] > md_backup -b /tmp/backup.txt -g data
Disk group to be backed up: DATA
ASMCMD [+] >
RESTORE METADATA
The md_restore command allows a restore of a disk group from the metadata created by the md_backup command. It also allows a certain amount of manipulation of the final disk groups during the restore. The summary usage is shown below.
md_restore -b <backup_file> [-li]
[-t (full)|nodg|newdg] [-f <sql_script_file>]
[-g '<diskgroup_name>,<diskgroup_name>,...']
[-o '<old_diskgroup_name>:<new_diskgroup_name>,...']
A straight restore of the backup taken previously is shown below.
ASMCMD [+] > md_restore -b /tmp/backup.txt -t full -g data
ASM REBALANCE AND ASM_POWER_LIMIT
ASM ha the ability to add and remove disks/luns from a diskgroup with no down time and an automatic rebalancing of the data on the luns.
To remove a lun, use the following command:
alter diskgroup dgroup1 drop disk asmdisk01;
10G R2
The init.ora parameter ASM_POWER_LIMIT is used to influence the throughput and speed of the rebalance operation. The range of values for ASM_POWER_LIMIT is 0–11, where a value of 11 is full throttle and a value of 1 (the default) is low speed.A value of 0, which turns off automatic rebalance, should be used with caution.
If the POWER clause is not specified in an ALTER DISKGROUP command, or when rebalance is implicitly invoked by adding or dropping a disk, the rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter. You can adjust this parameter dynamically. The higher the limit, the faster a rebalance operation may complete. Lower values cause rebalancing to take longer, but consume fewer processing and I/O resources. This leaves these resources available for other applications, such as the database.
The V$ASM_OPERATION view provides information that can be used for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations. The V$ASM_OPERATION view also gives an estimate in the EST_MINUTES column of the amount of time remaining for the rebalance operation to complete. You can see the effect of changing the rebalance power by observing the change in the time estimate.
You can manually rebalance the files in a disk group using the REBALANCE clause of the ALTER DISKGROUP statement.This will alter the power limit for any new operations, but not existing ones. To change an existing operation, you must directly alter the power limit on the diskgroup involved in the operation:
The POWER clause of the ALTER DISKGROUP…REBALANCE statement specifies the degree of parallelization, and thus the speed of the rebalance operation. It can be set to a value from 0 to 11. A value of 0 halts a rebalancing operation until the statement is either implicitly or explicitly reinvoked.
If you want the ALTER DISKGROUP…REBALANCE command to wait until the rebalance operation is complete before returning, you can add the WAIT keyword to the REBALANCE clause. This is especially useful in scripts.
The command also accepts a NOWAIT keyword, which invokes the default behavior of conducting the rebalance operation asynchronously. You can interrupt a rebalance running in wait mode by typing CTRL-C on most platforms. This causes the command to return immediately with the message ORA-01013: user requested cancel of current operation, and to continue the rebalance operation asynchronously.
11G R2
The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing in a disk group. The range of values is 0 to 1024. The default value is 1. A value of 0 disables rebalancing. Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead and more rebalancing processes.
For disk groups that have the disk group ASM compatibility set to less than 11.2.0.2, the operational range of values is 0 to 11 inclusive. If the value for ASM_POWER_LIMIT is larger than 11, a value of 11 is used for these disk groups.
Oracle recommendation set asm_power_limit value 4 on Exadata Database Machine ,and the value can impact of the application due to the the ASM rebalance generated Io loss minimization on Exadata.
The big advantage to being able to set the power limit to 0 comes when it is necessary to add/drop several disks at the same time. If the power limit is already set, then each add/drop will be serialized and each operation will have to complete before the next can begin. If, however, the power limit is set to 0 first, then submit all the add/drop operations and finally set the power limit to a non-zero number for the disk group, the operations are paralellized and run at the same time.
To remove a lun, use the following command:
alter diskgroup dgroup1 drop disk asmdisk01;
10G R2
The init.ora parameter ASM_POWER_LIMIT is used to influence the throughput and speed of the rebalance operation. The range of values for ASM_POWER_LIMIT is 0–11, where a value of 11 is full throttle and a value of 1 (the default) is low speed.A value of 0, which turns off automatic rebalance, should be used with caution.
If the POWER clause is not specified in an ALTER DISKGROUP command, or when rebalance is implicitly invoked by adding or dropping a disk, the rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter. You can adjust this parameter dynamically. The higher the limit, the faster a rebalance operation may complete. Lower values cause rebalancing to take longer, but consume fewer processing and I/O resources. This leaves these resources available for other applications, such as the database.
The V$ASM_OPERATION view provides information that can be used for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations. The V$ASM_OPERATION view also gives an estimate in the EST_MINUTES column of the amount of time remaining for the rebalance operation to complete. You can see the effect of changing the rebalance power by observing the change in the time estimate.
You can manually rebalance the files in a disk group using the REBALANCE clause of the ALTER DISKGROUP statement.This will alter the power limit for any new operations, but not existing ones. To change an existing operation, you must directly alter the power limit on the diskgroup involved in the operation:
The POWER clause of the ALTER DISKGROUP…REBALANCE statement specifies the degree of parallelization, and thus the speed of the rebalance operation. It can be set to a value from 0 to 11. A value of 0 halts a rebalancing operation until the statement is either implicitly or explicitly reinvoked.
If you want the ALTER DISKGROUP…REBALANCE command to wait until the rebalance operation is complete before returning, you can add the WAIT keyword to the REBALANCE clause. This is especially useful in scripts.
The command also accepts a NOWAIT keyword, which invokes the default behavior of conducting the rebalance operation asynchronously. You can interrupt a rebalance running in wait mode by typing CTRL-C on most platforms. This causes the command to return immediately with the message ORA-01013: user requested cancel of current operation, and to continue the rebalance operation asynchronously.
11G R2
The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing in a disk group. The range of values is 0 to 1024. The default value is 1. A value of 0 disables rebalancing. Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead and more rebalancing processes.
For disk groups that have the disk group ASM compatibility set to less than 11.2.0.2, the operational range of values is 0 to 11 inclusive. If the value for ASM_POWER_LIMIT is larger than 11, a value of 11 is used for these disk groups.
Oracle recommendation set asm_power_limit value 4 on Exadata Database Machine ,and the value can impact of the application due to the the ASM rebalance generated Io loss minimization on Exadata.
The big advantage to being able to set the power limit to 0 comes when it is necessary to add/drop several disks at the same time. If the power limit is already set, then each add/drop will be serialized and each operation will have to complete before the next can begin. If, however, the power limit is set to 0 first, then submit all the add/drop operations and finally set the power limit to a non-zero number for the disk group, the operations are paralellized and run at the same time.
STEPS TO MULTIPLEX CONTROL FILE IN ASM
For single-instance
1, make sure asm diskgroup is avaliable
Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
2, check current controlfile and using spfile
show parameter control_files
show parameter pfile
3, add new controlfile to spfile
alter system set control_files='[original file]','[asm diskgroup name where new control file will stored]' scope=spfile ;
for example:
alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','+NEW_DATA' scope=spfile ;
4, restart oracle database instance, shutdown and startup in mount (not ASM instance)
shutdown immediate
stratup nomount
5, Dumplicate controlfile to new path in spfile using rman
rman target /
rman> restore controlfile from '[original file full name]' ; exmaple: '+SYSTEM/TESTDB/controlfile/current.258.798205861'
Note: that the command prints the name of the new created file in new ASM diskgroup(my case is +NEW_DATA)
6, Modify the control_file parameter with the complete path of the new file
alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','[ full name of the new controlfile generated in the previous step ]' scope=spfile sid='*';
new control file name like this “+NEW_DATA/TESTDB/controlfile/current.258.798205861”
7, restart instance and verify controlfile
shutdown immediate
startup
show parameter control
Another way on RAC
1). Identify the location of the current controlfile::
SQL> select name from v$controlfile;
2) For can duplicate our control file our database must be down. So I am going to close my database first:
srvctl stop database -d TESTDB
3) After my database down. I am loginning one of the my node. In my case I am loginning node 1:
On node1:
Set DB env. than:
sqlplus / as sysdba
startup nomount
4) On node1 set ASM env. than check your current controlfile:
# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
5) On node1 Set DB env. Use RMAN to duplicate the controlfile:
rman target /
RMAN> restore controlfile to '+ORADATA' from '+ORADATA/RACTEST/controlfile/current.240.737948655' ; # this will create 2th controlfile
note:
We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File). Use ASMCMD or sqlplus to identify the name assigned to the controlfile or rman restore log .
6) Checking those file create or not follow setp 4 and use ls command
# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
CONTROLFILE UNPROT FINE AUG 03 18:00:00 Y current.1026.790368247
note:
As you can see We have new 1 controlfile “current.1026.790368247”
7) On node1 Set DB env. Modify the control_file parameter in spifle .than:
sqlplus “/as sysdba”
SQL> alter system set control_files='+ORADATA/RACTEST/controlfile/current.240.737948655','+ORADATA/RACTEST/controlfile/current.1026.790368247' scope=spfile sid='*';
SQL> shutdown immediate
8) Start your db
srvctl start database -d RACTEST
1, make sure asm diskgroup is avaliable
Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
2, check current controlfile and using spfile
show parameter control_files
show parameter pfile
3, add new controlfile to spfile
alter system set control_files='[original file]','[asm diskgroup name where new control file will stored]' scope=spfile ;
for example:
alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','+NEW_DATA' scope=spfile ;
4, restart oracle database instance, shutdown and startup in mount (not ASM instance)
shutdown immediate
stratup nomount
5, Dumplicate controlfile to new path in spfile using rman
rman target /
rman> restore controlfile from '[original file full name]' ; exmaple: '+SYSTEM/TESTDB/controlfile/current.258.798205861'
Note: that the command prints the name of the new created file in new ASM diskgroup(my case is +NEW_DATA)
6, Modify the control_file parameter with the complete path of the new file
alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','[ full name of the new controlfile generated in the previous step ]' scope=spfile sid='*';
new control file name like this “+NEW_DATA/TESTDB/controlfile/current.258.798205861”
7, restart instance and verify controlfile
shutdown immediate
startup
show parameter control
Another way on RAC
1). Identify the location of the current controlfile::
SQL> select name from v$controlfile;
2) For can duplicate our control file our database must be down. So I am going to close my database first:
srvctl stop database -d TESTDB
3) After my database down. I am loginning one of the my node. In my case I am loginning node 1:
On node1:
Set DB env. than:
sqlplus / as sysdba
startup nomount
4) On node1 set ASM env. than check your current controlfile:
# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
5) On node1 Set DB env. Use RMAN to duplicate the controlfile:
rman target /
RMAN> restore controlfile to '+ORADATA' from '+ORADATA/RACTEST/controlfile/current.240.737948655' ; # this will create 2th controlfile
note:
We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File). Use ASMCMD or sqlplus to identify the name assigned to the controlfile or rman restore log .
6) Checking those file create or not follow setp 4 and use ls command
# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
CONTROLFILE UNPROT FINE AUG 03 18:00:00 Y current.1026.790368247
note:
As you can see We have new 1 controlfile “current.1026.790368247”
7) On node1 Set DB env. Modify the control_file parameter in spifle .than:
sqlplus “/as sysdba”
SQL> alter system set control_files='+ORADATA/RACTEST/controlfile/current.240.737948655','+ORADATA/RACTEST/controlfile/current.1026.790368247' scope=spfile sid='*';
SQL> shutdown immediate
8) Start your db
srvctl start database -d RACTEST
Comments
Post a Comment